Searching JSON Data in MySQL
You have inserted records with JSON-encoded data into a MySQL database and need to search within that data. However, the provided SQL query is not returning the desired results. Let's explore a solution that utilizes JSON extraction functions introduced in MySQL version 5.7.
JSON Extraction in MySQL 5.7
In MySQL versions 5.7 and above, JSON extraction functions provide a powerful way to navigate and retrieve data from JSON values. One such function, JSON_EXTRACT, allows you to target specific keys and extract their corresponding values.
Applying JSON_EXTRACT to Your Query
To search within the attribs_json field for specific key-value pairs, you can use JSON_EXTRACT as follows:
SELECT id, JSON_EXTRACT(attribs_json, "$.feature.1.value") AS matching_value FROM products WHERE JSON_EXTRACT(attribs_json, "$.feature.1.value") REGEXP '\[[^"]*3[^"]*\]'
Explanation:
This query will return all rows where the "1" key in the "feature" object has a value that does not include "3". For the provided data, it will match rows with "1" values of ["2"] and ["2", "1"].
Conclusion
By leveraging MySQL's JSON extraction functions, you can effectively search and access data stored in JSON-encoded fields. The JSON_EXTRACT function provides a flexible and efficient way to extract specific values and perform complex filtering operations on JSON data.
The above is the detailed content of How Can I Effectively Search JSON Data Stored in MySQL Using JSON_EXTRACT?. For more information, please follow other related articles on the PHP Chinese website!