SQL
Extracting and Querying Data from JSON Columns
Learn how to query and extract specific values from JSON data stored in database columns using functions like `->>`, `->`, `JSON_EXTRACT`, or `JSON_VALUE`.
-- PostgreSQL JSONB functions:
SELECT
id,
data->>'name' as user_name, -- Extracts 'name' as text
data->'address'->>'city' as user_city, -- Extracts nested 'city' as text
data->'preferences'->'newsletter' as newsletter_pref -- Extracts boolean/JSON value
FROM users
WHERE
data->>'status' = 'active' -- Query where 'status' is 'active'
AND (data->'age')::int > 30; -- Query where 'age' (cast to int) is > 30
-- MySQL 8+ JSON functions:
-- SELECT
-- id,
-- JSON_EXTRACT(data, '$.name') AS user_name,
-- JSON_UNQUOTE(JSON_EXTRACT(data, '$.address.city')) AS user_city,
-- JSON_EXTRACT(data, '$.preferences.newsletter') AS newsletter_pref
-- FROM users
-- WHERE
-- JSON_UNQUOTE(JSON_EXTRACT(data, '$.status')) = 'active'
-- AND JSON_EXTRACT(data, '$.age') > 30;
How it works: This snippet demonstrates how to interact with JSON data stored in a database column, focusing on PostgreSQL's `JSONB` type. It shows how to extract specific fields (`name`, `city`, `newsletter`) using the `->` (extract JSON object field) and `->>` (extract JSON object field as text) operators. The `WHERE` clause illustrates filtering based on JSON field values, including type casting for numerical comparisons. A commented-out section provides equivalent examples for MySQL 8+ using `JSON_EXTRACT` and `JSON_UNQUOTE` functions.