mysql 动态sql 解析json,如何从MySQL中的动态键值对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":"abc@example.com","label":"Enter your email?"},

{"name":"question-3","value":"xyz","label":"Enter your city?"}]

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?"}

then it return the result as,

Name | Label

question-1 | Enter your name?

Expected Result :

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

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":"abc@example.com","label":"Enter your email?"},

{"name":"question-3","value":"xyz","label":"Enter your city?"}]

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?

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":"abc@example.com","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";

it will return

name | label

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

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.