How to search JSON array in MySQL?

Question

Let's say I have a JSON column named data in some MySQL table, and this column is a single array. So, for example, data may contain:

[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?

I tried the following, but seems it is always true regardless of the values in the array:

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

You can search json arrays in postgresql, normally as other fields, in mysql you have to search using mysql functions that you can find here: https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html — Thresh, Mar 27 '16 at 17:21
Thanks, but I couldn't find a way to search json array using these functions — , Mar 28 '16 at 07:52
Please did you find a solution to this as i'm stucked as well.. im trying to search [{"id":1}, {"id":2}]. To search where e.g id = 1 or so — Paul Okeke, Sep 04 '17 at 17:51

score 107 · Answer 1 · answered Dec 23 '19 at 19:27

107

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

answered Dec 23 '19 at 19:27

MNU-548

1,222
1
8
5

9

Omitting the `'$'` is also okay. – Fandi Susanto Jan 08 '20 at 15:06
1

what my SQL version supports that? – Yaroslav Dukal Feb 08 '21 at 03:53
1

Starting with MySQL 5.7 – HasanAlyazidi Feb 24 '21 at 08:53
8

That does not answer the question at all, we need all values which is greater than 2 not which equals 2 – Madian Malfi Mar 22 '21 at 01:43
¿How to resolve this? – Jorhel Reyes Jul 20 '22 at 01:38
depending on your MySQL server you might have to cast the item you search for to a json. for example: `JSON_CONTAINS('[1,2,3,4,5]',CAST(7 as json))`. – Kenten Fina Dec 01 '22 at 12:31

score 31 · Answer 2 · answered Apr 06 '18 at 07:01

31

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

answered Apr 06 '18 at 07:01

JONG MIN IM

319
3
5

4

why i cannot do like this? `SELECT JSON_SEARCH('[1,2,3,4,5]', 'one', 1) is not null` – Bobby Jun 15 '19 at 04:52
1

@Bobby https://stackoverflow.com/a/46719070/12538152 – Maddy89 May 28 '21 at 06:50

score 16 · Answer 3 · answered Apr 24 '18 at 15:28

16

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)

answered Apr 24 '18 at 15:28

user2458995

161
2
3

yes, this query work for me. @Loai you can use this. – Bobby Jun 15 '19 at 05:09
Even tho I copied the create table, insert table and then tried to query the select, it failed at the final select – ii iml0sto1 Oct 20 '19 at 18:45
Works in MariaDB since v10.6. Although DISTINCT is ambiguous. – unlimittt Sep 23 '21 at 03:37

score 11 · Answer 4 · answered May 25 '21 at 12:17

11

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

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

answered May 25 '21 at 12:17

Rodolfo Jorge Nemer Nogueira

4,915
2
41
30

score 9 · Answer 5 · edited Sep 28 '20 at 09:07

9

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

edited Sep 28 '20 at 09:07

Yves M.

29,855
23
108
144

answered Feb 04 '18 at 23:20

Carlos 2V

129
1
7

This worked in MySQL as well. Thank you for sharing. – Fawkes Aug 10 '21 at 05:57
1

An explanation of what 'one', 2, null do would be useful – James Dec 10 '21 at 13:59
1

@james Or a link to the doc: https://mariadb.com/kb/en/json_search/ – Kaymaz Mar 31 '23 at 07:52

score 3 · Answer 6 · answered May 28 '21 at 02:29

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"'

score 2 · Answer 7 · answered Mar 27 '16 at 17:04

2

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.

answered Mar 27 '16 at 17:04

Mohamed Gad-Elrab

636
1
6
20

1

I'm not sure that string matching will work as I want to compare for greater than not only equality, same goes for json_contains, it search for exact contain, not compare functions. – Mar 28 '16 at 07:53

score 2 · Answer 8 · answered Jul 10 '19 at 02:08

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"]

score 1 · Answer 9 · answered Nov 14 '18 at 05:08

1

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 ;

answered Nov 14 '18 at 05:08

Justin J

808
8
14

score 1 · Answer 10 · answered Dec 17 '19 at 07:20

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

score -1 · Answer 11 · answered Jan 29 '23 at 17:23

This seems to be possible with to JSON_TABLE function. It's available in mysql version 8.0 or mariadb version 10.6.

With this test setup

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;

we get the following table

+---+-----------+
| a | json      |
+---+-----------+
| a | [1]       |
| b | [1,2]     |
| c | [1,2,3]   |
| d | [1,2,3,4] |
+---+-----------+

It's possible to select every row from mytable wich has a value greater than 2 in the json array with this query.

SELECT * FROM mytable
WHERE TRUE IN (SELECT val > 2
               FROM JSON_TABLE(json,'$[*]'
                            columns (val INT(1) path '$')
                    ) as json
              )

Returns:

+---+-----------+
| a | json      |
+---+-----------+
| c | [1,2,3]   |
| d | [1,2,3,4] |
+---+-----------+

How to search JSON array in MySQL?

11 Answers11

Linked

Related