How Can I Efficiently Search JSON Data in MySQL?

Queries for Searching JSON Data in MySQL

You're trying to search within JSON data stored in your MySQL database. While you might be using a REGEXP-based query, it's not yielding the desired results. Let's explore a more effective approach.

Solution for MySQL versions >= 5.7

MySQL 5.7 and later versions provide the JSON_EXTRACT function, which allows you to directly search for specific elements within JSON data. To find keys equal to "1" with values other than "3," you can use the following query:

SELECT JSON_EXTRACT(attribs_json, '$.feature."1".value') AS attribs_json_value
FROM products
WHERE JSON_EXTRACT(attribs_json, '$.feature."1".value') != '["3"]'

This will extract the value of the "1" key in the "feature" object of the attribs_json.

Additional Reference

For further clarification, refer to the MySQL reference manual for JSON search functions: 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 JSON Data in MySQL?. For more information, please follow other related articles on the PHP Chinese website!