How to search JSON array in MySQL?

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

Related