How to Efficiently Search for Elements within JSON Arrays in MySQL?

Searching JSON Arrays in MySQL

Suppose you have a MySQL table with a JSON column named "data" containing an array. You wish to retrieve all rows where one of the array elements is greater than 2.

The query

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

returns an incorrect result because it always evaluates to true, regardless of the array's values.

To search for an integer in an array:

JSON_CONTAINS('[1,2,3,4,5]','7','$')  # Returns: 0
JSON_CONTAINS('[1,2,3,4,5]','1','$')  # Returns: 1

To search for a string in an array:

JSON_CONTAINS('["a","2","c","4","x"]','"x"','$')  # Returns: 1
JSON_CONTAINS('["1","2","3","4","5"]','"7"','$')  # Returns: 0

For your scenario, use the query:

SELECT * from my_table
WHERE JSON_CONTAINS(data, '2', '$');

This query selects all rows where the "data" column contains an array with an element greater than 2.

The above is the detailed content of How to Efficiently Search for Elements within JSON Arrays in MySQL?. For more information, please follow other related articles on the PHP Chinese website!