SQL
Extracting and Filtering JSON Data in MySQL
Learn to query and filter records based on specific values within JSON columns in MySQL using `JSON_EXTRACT` and `JSON_CONTAINS` functions.
-- Assuming a table 'products' with a JSON column 'details'
-- Example JSON for 'details': {"color": "red", "size": "M", "features": ["waterproof", "durable"], "metadata": {"weight_g": 500}}
-- Select products where the color is 'red'
SELECT
id,
name,
JSON_UNQUOTE(JSON_EXTRACT(details, '$.color')) AS product_color
FROM products
WHERE JSON_EXTRACT(details, '$.color') = '"red"'; -- JSON_EXTRACT returns JSON string, so compare with quoted string
-- Select products that have 'durable' in their 'features' array
SELECT
id,
name,
details
FROM products
WHERE JSON_CONTAINS(details, '"durable"', '$.features'); -- Check if scalar 'durable' exists in the 'features' array
How it works: This snippet demonstrates querying JSON data stored in a MySQL column. `JSON_EXTRACT(column, path)` is used to retrieve a specific value from the JSON document. For string comparisons, the extracted value must be compared against a JSON string literal (e.g., '"red"'). `JSON_UNQUOTE()` can strip the surrounding quotes for display. `JSON_CONTAINS(column, value, path)` is used to check if a JSON document at a specified path contains a particular value, useful for filtering based on array elements or nested objects.