mysql - 用于解析 JSON 的 SQL 查询

Question

我有包含 user_id 和 user_details 的用户表。它包含字符串格式的 JSON 数据，如下所示:

1-

[{"name":"question-1","value":"sachin","label":"Enter your name?"},
    {"name":"question-2","value":"abc@example.com","label":"Enter your email?"},
    {"name":"question-3","value":"xyz","label":"Enter your city?"}]

2-

[{"name":"question-1","value":"sachin123","label":"Enter your name?"},
    {"name":"question-2","value":"abc@example.com","label":"Enter your email?"},
    {"name":"question-3","value":"xyz","label":"Enter your city?"},
    {"name":"question-4","value":"red","label":"Enter your favourite color?"}]

3-

[{"name":"question-1","value":"","label":"Enter your name?"},
    {"name":"question-3","value":"xyz","label":"Enter your city?"},
    {"name":"question-4","value":"red","label":"Enter your favourite color?"}]

4-

[{"name":"question-1","value":"","label":"Enter your name?"},
    {"name":"question-2","value":"pqr@example.com","label":"Enter your email?"},
    {"name":"question-3","value":"abc","label":"Enter your city?"},
    {"name":"question-4","value":"pink","label":"Enter your favourite color?"}]

预期输出是:

row  Enter your name? | Enter your email?   | Enter your city?  | Enter your favourite color?
1    sachin           | abc@example.com     | xyz               | -
2    sachin123        | xyz@example.com     | xyz               | red
3    -                | -                   | xyz               | blue
4    -                | pqr@example.com     | abc               | pink

我尝试过通过 PHP 来实现，可以通过将 JSON 转换为数组然后进行比较来实现。有没有更简单的方法直接使用MySQL查询获取？

Answer 1

我想出了这个解决方案:

SELECT user_id,
    COALESCE(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(user_details, CONCAT(q1, '.value'))), ''), '-') AS 'Enter your name?',
    COALESCE(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(user_details, CONCAT(q2, '.value'))), ''), '-') AS 'Enter your email?',
    COALESCE(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(user_details, CONCAT(q3, '.value'))), ''), '-') AS 'Enter your city??',
    COALESCE(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(user_details, CONCAT(q4, '.value'))), ''), '-') AS 'Enter your favorite color?'
FROM (
    SELECT user_id, user_details,
    SUBSTRING_INDEX(JSON_UNQUOTE(JSON_SEARCH(user_details, 'one', 'question-1')), '.', 1) AS q1,
        SUBSTRING_INDEX(JSON_UNQUOTE(JSON_SEARCH(user_details, 'one', 'question-2')), '.', 1) AS q2,
        SUBSTRING_INDEX(JSON_UNQUOTE(JSON_SEARCH(user_details, 'one', 'question-3')), '.', 1) AS q3,
        SUBSTRING_INDEX(JSON_UNQUOTE(JSON_SEARCH(user_details, 'one', 'question-4')), '.', 1) AS q4
    FROM users) t

输出:

+---------+------------------+-------------------+-------------------+----------------------------+
| user_id | Enter your name? | Enter your email? | Enter your city?? | Enter your favorite color? |
+---------+------------------+-------------------+-------------------+----------------------------+
|       1 | sachin           | abc@example.com   | xyz               | -                          |
|       2 | sachin123        | abc@example.com   | xyz               | red                        |
|       3 | -                | -                 | xyz               | red                        |
|       4 | -                | pqr@example.com   | abc               | pink                       |
+---------+------------------+-------------------+-------------------+----------------------------+

但这不会根据每个问题中找到的标签动态地标记列。这样做是有问题的，因为不能保证与问题 1 对应的标签在每一行上都相同，或者在任何给定行上都存在。因此，在准备()查询之前，您必须决定标签应该是什么，并对查询中的列别名进行硬编码。

最终，像您已经完成的那样，在 PHP 中执行此操作可能会更简单。