Searching for Specific Values in JSON Documents

Last update on October 17 2025 12:44:35 (UTC/GMT +8 hours)

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".

Go to:

PREV : Retrieve JSON Object Keys Using JSON_KEYS.
NEXT : Remove a Key from a JSON Document using JSON_REMOVE.

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