如何在MySQL中搜索JSON数组?

How to search JSON array in MySQL?

提问人: 提问时间:3/28/2016 最后编辑:informatik01 更新时间:1/30/2023 访问量:196482

问:

假设我在某个MySQL表中有一个名为data的JSON列,该列是一个数组。因此,例如,数据可能包含:

[1,2,3,4,5]

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

我尝试了以下方法,但似乎无论数组中的值如何,它总是正确的

SELECT * from my_table
WHERE JSON_EXTRACT(data, '$[*]') > 2;
mysql-json

评论

0赞 Thresh 3/28/2016
您可以在 postgresql 中搜索 json 数组,通常与其他字段一样,在 mysql 中,您必须使用 mysql 函数进行搜索,您可以在此处找到: dev.mysql.com/doc/refman/5.7/en/json-search-functions.html
0赞 3/28/2016
谢谢,但我找不到使用这些函数搜索 json 数组的方法
0赞 Stefan Gabos 5/5/2017
你设法找到一个解决方案吗?
1赞 Paul Okeke 9/5/2017
请问你有没有找到解决这个问题的方法,因为我也被困住了。我正在尝试搜索 [{“id”:1}, {“id”:2}]。搜索例如 id = 1 左右的位置
0赞 10/16/2017
不,我没有找到解决方案

答:

2赞 Mohamed Gad-Elrab 3/28/2016 #1

一种可能的方法是将问题作为字符串匹配来处理。将 JSON 转换为字符串并匹配。

或者您可以使用JSON_CONTAINS

评论

1赞 3/28/2016
我不确定字符串匹配是否有效,因为我想不仅要比较相等,json_contains也是如此,它搜索的是精确包含,而不是比较函数。
9赞 Carlos 2V 2/5/2018 #2

我不知道我们是否找到了解决方案。 我用MariaDB找到了一种方法,可以在数组中搜索路径。例如,在数组中,我想要查找 id 等于 2 的路径。[{"id":1}, {"id":2}]

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

结果是:

"$[1].id"

星号表示搜索整个阵列

评论

0赞 Fawkes 8/10/2021
这在MySQL中也有效。谢谢你的分享。
1赞 James 12/10/2021
解释 'one', 2, null 做什么会很有用
1赞 Kaymaz 3/31/2023
@james 或文档链接:mariadb.com/kb/en/json_search
32赞 JONG MIN IM 4/6/2018 #3
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

是假的

评论

4赞 Bobby 6/15/2019
为什么我不能这样做?SELECT JSON_SEARCH('[1,2,3,4,5]', 'one', 1) is not null
1赞 Maddy89 5/28/2021
@Bobby stackoverflow.com/a/46719070/12538152
17赞 user2458995 4/24/2018 #4

从 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)

评论

0赞 Bobby 6/15/2019
是的,这个查询对我有用。@Loai您可以使用它。
0赞 ii iml0sto1 10/21/2019
即使我复制了创建表,插入表,然后尝试查询选择,它在最终选择时失败了
0赞 unlimittt 9/23/2021
从 v10.6 开始在 MariaDB 中工作。虽然 DISTINCT 是模棱两可的。
1赞 Justin J 11/14/2018 #5

您可以使用 JSON 提取来搜索和选择数据

SELECT data, data->"$.id" as selectdata
FROM table
WHERE JSON_EXTRACT(data, "$.id") = '123'
#ORDER BY c->"$.name";
limit 10 ;
2赞 Tom Hu 7/10/2019 #6
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”]

1赞 zool 12/17/2019 #7

我有类似的问题,通过函数搜索

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));
111赞 MNU-548 12/24/2019 #8

您可以按如下方式搜索整数数组:

  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', '$');

评论

9赞 Fandi Susanto 1/8/2020
省略 也是可以的。'$'
1赞 Yaroslav Dukal 2/8/2021
我的 SQL 版本支持什么?
1赞 HasanAlyazidi 2/24/2021
从 MySQL 5.7 开始
8赞 Madian Malfi 3/22/2021
这根本没有回答这个问题,我们需要所有大于 2 的值,而不是等于 2 的值
0赞 Jorhel Reyes 7/20/2022
¿如何解决这个问题?
12赞 Rodolfo Jorge Nemer Nogueira 5/25/2021 #9

我使用 JSON_EXTRACT 和 JSON_CONTAINS (MariaDB) 的组合:

SELECT * FROM table WHERE JSON_CONTAINS(JSON_EXTRACT(json_field, '$[*].id'), 11, '$');
3赞 Majid Hojati 5/28/2021 #10

这个例子对我适用于上面的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"'

-1赞 jkoch 1/30/2023 #11

这似乎是可能的。它在 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] |
+---+-----------+