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

问题描述

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

I have the user table with user_id and user_details. it contains the JSON data in string format as shown below:

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

我已经尝试过json_extract，但是如果json具有如下所示的数据，它将返回结果:

I have tried with the json_extract but it return the result if json has data as shown below:

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

然后返回结果，

    Name    |     Label
question-1  |   Enter your name?

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

Expected Result :I want to extract all name and label from json in sql query.

示例1:考虑在user_details列中有以下数据，

Example-1:Consider that we have the following data in user_details column,

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

然后sql查询应以以下格式返回结果，

then the sql query should return the result in following format ,

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

如何在MySQL中使用JSON_EXTRACT获得此信息?

How to get this using JSON_EXTRACT in MySQL?

推荐答案

我假设您没有使用表格.

I assume that you are not using a table.

SET @data = '[{"name":"question-1","value":"sachin","label":"Enter your name?"},
    {"name":"question-2","value":"[email protected]","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应该如下所示:

SQL should be like below according to your table and column name:

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

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

you can match them by using some loops for arrays. I do not know if this is the best way but it satisfy my needs.

这篇关于如何从MySQL中的动态键值对JSON提取?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！