我的用户表带有user_id和user_details。它以字符串格式包含JSON数据,如下所示:
[{"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?"}]
我已经尝试了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":"abc@example.com","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":"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";
它将返回
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;
您可以通过使用一些循环进行数组匹配它们。我不知道这是否是最好的方法,但它可以满足我的需求。
如何使用MySQL UDF JSON_EXTRACT 0.4.0从JSON数组中提取行给出另一个答案?是用 common_schema 来解析自己的JSON。如果您不习惯复杂SQL。
很棘手。您可以创建一个主题列表中提出的汇总表,如果您知道该字段将给出多少个元素,但我想这不是您的情况。
,我想这不是您的情况。但是,正如两个答案中提到的那样,似乎更好,而不是将这样的JSON列表存储在SQL数据库中。也许您可以制作一个包含每个字典一行的相关表,然后用外键链接到主表。
i在报告中工作,其中一列中有一个大的JSON数组列表。我修改了数据模型以存储关系1至 *,而不是将所有内容存储在一个列中。为了完成此过程,我不得不在存储过程中使用一段时间,因为我不知道最大尺寸:
DROP PROCEDURE IF EXISTS `test`;
DELIMITER #
CREATE PROCEDURE `test`()
PROC_MAIN:BEGIN
DECLARE numNotes int;
DECLARE c int;
DECLARE pos varchar(10);
SET c = 0;
SET numNotes = (SELECT
ROUND (
(
LENGTH(debtor_master_notes)
- LENGTH( REPLACE ( debtor_master_notes, "Id", "") )
) / LENGTH("Id")
) AS countt FROM debtor_master
order by countt desc Limit 1);
DROP TEMPORARY TABLE IF EXISTS debtorTable;
CREATE TEMPORARY TABLE debtorTable(debtor_master_id int(11), json longtext, note int);
WHILE(c <numNotes) DO
SET pos = CONCAT('$[', c, ']');
INSERT INTO debtorTable(debtor_master_id, json, note)
SELECT debtor_master_id, JSON_EXTRACT(debtor_master_notes, pos), c+1
FROM debtor_master
WHERE debtor_master_notes IS NOT NULL AND debtor_master_notes like '%[%' AND JSON_EXTRACT(debtor_master_notes, pos) IS NOT NULL AND JSON_EXTRACT(debtor_master_notes, pos) IS NOT NULL;
SET c = c + 1;
END WHILE;
SELECT * FROM debtorTable;
END proc_main #
DELIMITER ;
您不使用json_extract()。您使用json_table():
mysql> create table mytable ( id serial primary key, data json);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into mytable 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?"}]';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT j.* FROM mytable,
JSON_TABLE(data, '$[*]' COLUMNS (
name VARCHAR(20) PATH '$.name',
label VARCHAR(50) PATH '$.label'
)) AS j;
+------------+-------------------+
| name | label |
+------------+-------------------+
| question-1 | Enter your name? |
| question-2 | Enter your email? |
| question-3 | Enter your city? |
+------------+-------------------+
json_table()需要mySQL 8.0.4或更高版本。如果您至少不运行该版本,则必须升级。
老实说,如果您需要访问各个字段,则将数据存储在普通列中并避免使用JSON是较少的工作。