如何从 MySQL 中的动态键值对中提取 JSON？

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

[{"name":"question-1","value":"sachin","label":"Enter your name?"},
    {"name":"question-2","value":"[email protected] /cdn-cgi/l/email-protection","label":"Enter your email?"},
    {"name":"question-3","value":"xyz","label":"Enter your city?"}]

我尝试过 json_extract 但如果 json 有数据，它会返回结果，如下所示：

{"name":"question-1","value":"sachin","label":"Enter your name?"}

然后它返回结果为，

    Name    |     Label
question-1  |   Enter your name? 

预期结果 ： 我想从 sql 查询中的 json 中提取所有名称和标签。

示例1： 考虑到我们在 user_details 列中有以下数据，

[{"name":"question-1","value":"sachin","label":"Enter your name?"},
    {"name":"question-2","value":"[email protected] /cdn-cgi/l/email-protection","label":"Enter your email?"},
    {"name":"question-3","value":"xyz","label":"Enter your city?"}]

那么sql查询应该返回以下格式的结果，

    Name      |     Label
question-1    |  Enter your name?
question-2    |  Enter your email?
question-3    |  Enter your city?

如何在 MySQL 中使用 JSON_EXTRACT 来获取它？

我假设您没有使用表格。

SET @data = '[{"name":"question-1","value":"sachin","label":"Enter your name?"},
    {"name":"question-2","value":"[email protected] /cdn-cgi/l/email-protection","label":"Enter your email?"},
    {"name":"question-3","value":"xyz","label":"Enter your city?"}]';

SELECT JSON_EXTRACT(@data,'$[*].name') AS "name", JSON_EXTRACT(@data,'$[*].label') AS "label";

它会返回

name                                           |  label
["question-1", "question-2", "question-3"]     |  ["Enter your name?", "Enter your email?", "Enter your city?"]

根据您的表和列名称，SQL 应如下所示：

SELECT JSON_EXTRACT(user_details,'$[*].name') AS "name", JSON_EXTRACT(user_details,'$[*].label') AS "label" FROM user;

您可以通过使用一些数组循环来匹配它们。我不知道这是否是最好的方法，但它满足了我的需求。