如何在 MySQL 中搜索 JSON 数组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36249828/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to search JSON array in MySQL?
提问by
Let's say I have a JSON column named datain some MySQL table, and this column is a single array. So, for example, data may contain:
假设我在某个 MySQL 表中有一个名为data的 JSON 列，而该列是单个数组。因此，例如，数据可能包含：
[1,2,3,4,5]
[1,2,3,4,5]
Now I want to select all rows which have a data column where one of its array elements is greater than 2. Is this possible?
现在我想选择所有具有数据列的行，其中一个数组元素大于 2。这可能吗？
I tried the following, but seems it is always trueregardless of the values in the array:
我尝试了以下操作，但无论数组中的值如何，它似乎总是正确的：
SELECT * from my_table
WHERE JSON_EXTRACT(data, '$[*]') > 2;
回答by JONG MIN IM
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
是假的
回答by MNU-548
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
注意：JSON_CONTAINS 返回 1 或 0
In your case you may search using a query like so:
在您的情况下，您可以使用如下查询进行搜索：
SELECT * from my_table
WHERE JSON_CONTAINS(data, '2', '$');
回答by Mohamed Gad-Elrab
A possible way is to deal with the problem as string matching. Convert the JSON to string and match.
一种可能的方法是将问题作为字符串匹配来处理。将 JSON 转换为字符串并匹配。
Or you can use JSON_CONTAINS.
或者您可以使用JSON_CONTAINS。
回答by Carlos 2V
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.
我不知道我们是否找到了解决方案。我用 MariaDB 找到了一种在数组中搜索路径的方法。例如，在数组 [{"id":1}, {"id":2}] 中，我想找到 id 等于 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
星号表示搜索整个数组
回答by user2458995
Since MySQL 8there is a new function called JSON_TABLE.
从 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)
回答by Justin J
You can use JSON extract to search and select data
您可以使用 JSON 提取来搜索和选择数据
SELECT data, data->"$.id" as selectdata
FROM table
WHERE JSON_EXTRACT(data, "$.id") = '123'
#ORDER BY c->"$.name";
limit 10 ;
回答by Tom Hu
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"
结果：“$[0].SongLabels[0].SongLabelName”
SELECT song_label_content->'$[*].SongLabels[*].SongLabelName' FROM t_music_song_label_relation;
result: ["Funk", "RnB"]
结果：[“放克”，“RnB”]
回答by zool
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));
