How to Efficiently Retrieve Specific JSON Data from MySQL?

Retrieving JSON Data from MySQL

MySQL provides a convenient way to search and extract specific information from JSON data stored within its database. Here's how you can perform such a search:

MySQL Version 5.7 and Above

If you're using MySQL version 5.7 or later, you can utilize the JSON_EXTRACT function. This function enables you to extract a specific value from a JSON string. To search for a particular value within the attribs_json field, you can use a query like this:

SELECT JSON_EXTRACT(attribs_json, '$.feature."1".value') AS feature_value
FROM products
WHERE JSON_EXTRACT(attribs_json, '$.feature."1".value') REGEXP '^[^""3"$]'

This query will select the value of the "value" key within the "1" key of the "feature" object in the attribs_json field for all products that do not have "3" as a value.

Earlier MySQL Versions

For earlier versions of MySQL, you can use regular expressions with the REGEXP operator to search for a pattern within the JSON string. However, this approach is more complex and requires more intensive string parsing.

Example Query

Consider the following JSON structure:

{
  "feature": {
    "1": { "value": "[\"2\",\"3\"]" },
    "2": { "value": "[\"1\"]" },
    "3": { "value": "[\"1\"]" }
  }
}

To search for the key "1" with a value that is not "3", you can use the following query:

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

Important Note

When using regular expressions, remember to escape special characters in your search pattern to avoid unintended matches. Also, the syntax for JSON_EXTRACT and REGEXP may vary slightly depending on the specific MySQL version you're using.

The above is the detailed content of How to Efficiently Retrieve Specific JSON Data from MySQL?. For more information, please follow other related articles on the PHP Chinese website!