Searching for Specific Values in JSON Documents

Search for a Specific Value in JSON Using JSON_CONTAINS

Write a MySQL query to find products whose details JSON column contains the key-value pair "color": "red" using the JSON_CONTAINS function.

Solution:

-- Retrieve products that have the key "color" with the value "red" in the JSON Details.

-- Select the ProductName column for products that meet the condition.
SELECT 
    -- Retrieve the ProductName column.
    ProductName

-- Specify the table from which to retrieve the data.
FROM Products

-- Use JSON_CONTAINS to filter products where the Details JSON contains the key-value pair {"color": "red"}.
-- JSON_CONTAINS checks if the specified JSON document or key-value pair exists within the JSON column.
WHERE JSON_CONTAINS(Details, '{"color": "red"}'); 

Explanation:

• Purpose of the Query:
• To filter records based on the presence of a specific key-value pair in a JSON document.
• Demonstrates the use of JSON_CONTAINS for searching within JSON data.
• Key Components:
• JSON_CONTAINS(Details, '{"color": "red"}') : Checks if the JSON document contains the specified pair.
• WHERE clause: Applies the filter to the query.
• Real-World Application:
• Useful in scenarios where products need to be filtered by attributes stored as JSON.

Notes:

• The JSON text provided must be valid and match the structure of the JSON in the Details column.
• JSON_CONTAINS returns 1 if the condition is met and 0 otherwise.

For more Practice: Solve these Related Problems:

• Write a MySQL query to find rows where a nested JSON key holds a specific value.
• Write a MySQL query to search for records containing a key-value pair in a JSON column regardless of its position.
• Write a MySQL query to filter records where a JSON array contains a particular element.
• Write a MySQL query to locate rows with a JSON column that includes a key "status" with the value "active".

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Remove a Key from a JSON Document using JSON_REMOVE.
Next MySQL Exercise: Remove a Key from a JSON Document using JSON_REMOVE.