How to search JSON array in MySQL?

Question

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

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)

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

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

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