How Can I Efficiently Search and Filter JSON Data within MySQL Queries?

JSON Data Search in MySQL Queries

Queries in MySQL involving JSON data are becoming increasingly important. JSON, or JavaScript Object Notation, is a text-based format commonly used for data exchange. To efficiently retrieve specific data from JSON stored in MySQL, certain query modifications are necessary.

One such modification involves the extraction of data based on key-value pairs. The provided query,

SELECT  `id` ,  `attribs_json` 
FROM  `products` 
WHERE  `attribs_json` REGEXP  '"1":{"value":[^"3"$]'

intends to extract keys equal to "1" and values not equal to "3."

If you are using MySQL version 5.7 or later, you can employ a simpler approach using the JSON_EXTRACT() function, as follows:

SELECT JSON_EXTRACT(name, "$.id") AS name
FROM table
WHERE JSON_EXTRACT(name, "$.id") > 3

This query extracts the "id" value from the JSON object in the "name" column and filters results where the "id" is greater than 3.

For comprehensive details on JSON search functions in MySQL, refer to the official MySQL reference manual: https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html.

The above is the detailed content of How Can I Efficiently Search and Filter JSON Data within MySQL Queries?. For more information, please follow other related articles on the PHP Chinese website!