How Can I Query JSON Arrays in MySQL to Find Specific Array Elements?

Querying JSON Arrays with MySQL's JSON Functions

In MySQL, JSON data can be stored in columns as a single array. To query these arrays, you can utilize the JSON functions provided by MySQL.

Consider a table with a JSON column named data containing an array of integers, such as [1,2,3,4,5]. To select rows where one of the array elements is greater than 2, you cannot directly use the JSON_EXTRACT function as demonstrated in your query.

Instead, you can employ the JSON_CONTAINS function as follows:

1. Integers: JSON_CONTAINS(data, '7', '$') returns 0 if the array does not contain 7, and 1 if it does. For example, JSON_CONTAINS('[1,2,3,4,5]', '7', '$') evaluates to 0.
2. Strings: JSON_CONTAINS(data, '"x"', '$') returns 1 if the array contains "x", and 0 otherwise. Similarly, JSON_CONTAINS('["a","2","c","4","x"]', '"x"', '$') yields 1.

In your specific scenario, you can use the following query:

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

This query will return all rows where the data column contains an array with the value 2 or greater.

The above is the detailed content of How Can I Query JSON Arrays in MySQL to Find Specific Array Elements?. For more information, please follow other related articles on the PHP Chinese website!