提问人: 提问时间:3/28/2016 最后编辑:informatik01 更新时间:1/30/2023 访问量:196482
如何在MySQL中搜索JSON数组?
How to search JSON array in MySQL?
问:
假设我在某个MySQL表中有一个名为data的JSON列,该列是一个数组。因此,例如,数据可能包含:
[1,2,3,4,5]
现在我想选择具有数据列的所有行,其中其中一个数组元素大于 2。这可能吗?
我尝试了以下方法,但似乎无论数组中的值如何,它总是正确的:
SELECT * from my_table
WHERE JSON_EXTRACT(data, '$[*]') > 2;
答:
一种可能的方法是将问题作为字符串匹配来处理。将 JSON 转换为字符串并匹配。
或者您可以使用JSON_CONTAINS。
评论
我不知道我们是否找到了解决方案。
我用MariaDB找到了一种方法,可以在数组中搜索路径。例如,在数组中,我想要查找 id 等于 2 的路径。[{"id":1}, {"id":2}]
SELECT JSON_SEARCH('name_field', 'one', 2, null, '$[*].id')
FROM name_table
结果是:
"$[1].id"
星号表示搜索整个阵列
评论
SELECT JSON_SEARCH('["1","2","3","4","5"]', 'one', "2") is not null
是真的
SELECT JSON_SEARCH('["1","2","3","4","5"]', 'one', "6") is not null
是假的
评论
SELECT JSON_SEARCH('[1,2,3,4,5]', 'one', 1) is not null
从 MySQL 8 开始,有一个称为 JSON_TABLE 的新函数。
CREATE TABLE my_table (id INT, data JSON);
INSERT INTO my_table VALUES
(1, "[1,2,3,4,5]"),
(2, "[0,1,2]"),
(3, "[3,4,-10]"),
(4, "[-1,-2,0]");
SELECT DISTINCT my_table.*
FROM my_table, JSON_TABLE(data, "$[*]" COLUMNS(nr INT PATH '$')) as ids
WHERE ids.nr > 2;
+------+-----------------+
| id | data |
+------+-----------------+
| 1 | [1, 2, 3, 4, 5] |
| 3 | [3, 4, -10] |
+------+-----------------+
2 rows in set (0.00 sec)
评论
您可以使用 JSON 提取来搜索和选择数据
SELECT data, data->"$.id" as selectdata
FROM table
WHERE JSON_EXTRACT(data, "$.id") = '123'
#ORDER BY c->"$.name";
limit 10 ;
SET @doc = '[{"SongLabels": [{"SongLabelId": "111", "SongLabelName": "Funk"}, {"SongLabelId": "222", "SongLabelName": "RnB"}], "SongLabelCategoryId": "test11", "SongLabelCategoryName": "曲风"}]';
SELECT *, JSON_SEARCH(@doc, 'one', '%un%', null, '$[*].SongLabels[*].SongLabelName')FROM t_music_song_label_relation;
结果:“$[0]。歌曲标签[0]。SongLabelName”
SELECT song_label_content->'$[*].SongLabels[*].SongLabelName' FROM t_music_song_label_relation;
结果: [“Funk”, “RnB”]
我有类似的问题,通过函数搜索
create function searchGT(threshold int, d JSON)
returns int
begin
set @i = 0;
while @i < json_length(d) do
if json_extract(d, CONCAT('$[', @i, ']')) > threshold then
return json_extract(d, CONCAT('$[', @i, ']'));
end if;
set @i = @i + 1;
end while;
return null;
end;
select searchGT(3, CAST('[1,10,20]' AS JSON));
您可以按如下方式搜索整数数组:
JSON_CONTAINS('[1,2,3,4,5]','7','$') Returns: 0
JSON_CONTAINS('[1,2,3,4,5]','1','$') Returns: 1
您可以按如下方式搜索字符串数组:
JSON_CONTAINS('["a","2","c","4","x"]','"x"','$') Returns: 1
JSON_CONTAINS('["1","2","3","4","5"]','"7"','$') Returns: 0
注意:JSON_CONTAINS 返回 1 或 0
在您的情况下,您可以使用如下所示的查询进行搜索:
SELECT * from my_table
WHERE JSON_CONTAINS(data, '2', '$');
评论
'$'
我使用 JSON_EXTRACT 和 JSON_CONTAINS (MariaDB) 的组合:
SELECT * FROM table WHERE JSON_CONTAINS(JSON_EXTRACT(json_field, '$[*].id'), 11, '$');
这个例子对我适用于上面的mysql5.7
SET @j = '{"a": [ "8428341ffffffff", "8428343ffffffff", "8428345ffffffff", "8428347ffffffff","8428349ffffffff", "842834bffffffff", "842834dffffffff"], "b": 2, "c": {"d": 4}}';
select JSON_CONTAINS(JSON_EXTRACT(@j , '$.a'),'"8428341ffffffff"','$') => returns 1
关于周围搜索关键字的通知,"
'"8428341ffffffff"'
这似乎是可能的。它在 mysql 版本 8.0 或 mariadb 版本 10.6 中可用。JSON_TABLE
使用此测试设置
CREATE TEMPORARY TABLE mytable
WITH data(a,json) AS (VALUES ('a','[1]'),
('b','[1,2]'),
('c','[1,2,3]'),
('d','[1,2,3,4]'))
SELECT * from data;
我们得到下表
+---+-----------+
| a | json |
+---+-----------+
| a | [1] |
| b | [1,2] |
| c | [1,2,3] |
| d | [1,2,3,4] |
+---+-----------+
使用此查询,可以从 mytable 中选择值大于 2 的每一行。
SELECT * FROM mytable
WHERE TRUE IN (SELECT val > 2
FROM JSON_TABLE(json,'$[*]'
columns (val INT(1) path '$')
) as json
)
返回:
+---+-----------+
| a | json |
+---+-----------+
| c | [1,2,3] |
| d | [1,2,3,4] |
+---+-----------+
评论