How to Search for Elements within JSON Arrays in MySQL?

Searching JSON Array Elements in MySQL

Your query attempts to use the JSON_EXTRACT function to compare the entire array with a scalar value, which will always return a non-null result. To search for specific elements within the array, you can utilize the JSON_CONTAINS function.

Searching an Array of Integers:

JSON_CONTAINS('[1,2,3,4,5]','7','$')

This will return 0 if the array does not contain the value 7 and 1 if it does.

Searching an Array of Strings:

JSON_CONTAINS('["a","2","c","4","x"]','"x"','$')

This will return 1 if the array contains the string "x" and 0 otherwise.

Query for Your Case:

To select all rows where the data column contains an array element greater than 2, use the following query:

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

This will return all rows where the data column's array contains an element greater than 2.

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