如何在 MySQL 中搜索 JSON 数组？

假设在某个 MySQL 表中有一个名为 资料的 JSON 列，而这个列是一个单独的 数组。例如，数据可能包含:

[1,2,3,4,5]

现在我想选择所有行，其中有一个数组元素大于2的数据列。这可能吗？

我尝试了以下方法，但似乎总是 没错，而不管数组中的值是什么:

SELECT * from my_table
WHERE JSON_EXTRACT(data, '$[*]') > 2;
158976 次浏览
小开

A possible way is to deal with the problem as string matching. Convert the JSON to string and match.

Or you can use JSON_CONTAINS.

小开

I don't know if we found the solution. I found with MariaDB a way, to search path in a array. For example, in array [{"id":1}, {"id":2}], I want find path with id equal to 2.

SELECT JSON_SEARCH('name_field', 'one', 2, null, '$[*].id')
FROM name_table

The result is:

"$[1].id"

The asterisk indicate searching the entire array

小开 
SELECT JSON_SEARCH('["1","2","3","4","5"]', 'one', "2") is not null

is true

SELECT JSON_SEARCH('["1","2","3","4","5"]', 'one', "6") is not null

is false

小开

Since MySQL 8 there is a new function called 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)
小开

You can use JSON extract to search and select data

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;

result: "$[0].SongLabels[0].SongLabelName"

SELECT song_label_content->'$[*].SongLabels[*].SongLabelName' FROM t_music_song_label_relation;

result: ["Funk", "RnB"]

小开

I have similar problem, search via function

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));
小开

You may search an array of integers as follows:

  JSON_CONTAINS('[1,2,3,4,5]','7','$') Returns: 0
JSON_CONTAINS('[1,2,3,4,5]','1','$') Returns: 1

You may search an array of strings as follows:

  JSON_CONTAINS('["a","2","c","4","x"]','"x"','$') Returns: 1
JSON_CONTAINS('["1","2","3","4","5"]','"7"','$') Returns: 0

Note: JSON_CONTAINS returns either 1 or 0

In your case you may search using a query like so:

SELECT * from my_table
WHERE JSON_CONTAINS(data, '2', '$');
小开

I use a combination of JSON_EXTRACT and JSON_CONTAINS (MariaDB):

SELECT * FROM table WHERE JSON_CONTAINS(JSON_EXTRACT(json_field, '$[*].id'), 11, '$');
小开

This example works for me with mysql 5.7 above

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

notice about " around search keyword, '"8428341ffffffff"'


提交答案