How to search JSON array in MySQL?

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