How Can I Efficiently Search Encoded JSON Data in MySQL to Exclude Specific Values?

Searching Encoded JSON Data in MySQL

You have encountered difficulties in querying JSON-encoded data in MySQL. Notably, your current MySQL query returns inconsistent results when attempting to retrieve keys equal to "1" with values that differ from "3."

Solution for MySQL Versions >= 5.7

If you're using MySQL version 5.7 or later, you can leverage the JSON_EXTRACT function to effectively search within JSON data. The syntax for this function is:

JSON_EXTRACT(JSON_DOCUMENT, JSON_PATH)

In your specific case, to query for key "1" with a value that excludes "3," you can use the following query:

SELECT JSON_EXTRACT(`attribs_json`, "$.feature.1.value") AS `value`
FROM `products`
WHERE JSON_EXTRACT(`attribs_json`, "$.feature.1.value") NOT LIKE "%3%"

This query will return a record if the value associated with key "1" does not contain the string "3."

Additional Resources

For a more comprehensive overview of 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 Encoded JSON Data in MySQL to Exclude Specific Values?. For more information, please follow other related articles on the PHP Chinese website!