How Can I Effectively Search JSON Data Stored in MySQL Using JSON_EXTRACT?

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:

• The $.feature.1.value expression targets the value of the key "1" within the "feature" object.
• The REGEXP predicate restricts the results to those where the extracted value does not contain the string "3".

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!