SQL
Querying JSON Data Stored in a Database Column
Learn how to effectively query and extract specific values from JSON data stored directly within a column in your SQL database using native JSON functions.
-- For PostgreSQL (JSONB type)
SELECT
id,
data->>'name' AS user_name,
(data->'address'->>'city')::text AS user_city
FROM
users
WHERE
data->'preferences'->>'newsletter' = 'true';
-- For MySQL (JSON type)
SELECT
id,
JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')) AS user_name,
JSON_UNQUOTE(JSON_EXTRACT(data, '$.address.city')) AS user_city
FROM
users
WHERE
JSON_EXTRACT(data, '$.preferences.newsletter') = 'true';
-- For SQL Server (NVARCHAR with JSON functions)
SELECT
id,
JSON_VALUE(data, '$.name') AS user_name,
JSON_VALUE(data, '$.address.city') AS user_city
FROM
users
WHERE
JSON_VALUE(data, '$.preferences.newsletter') = 'true';
How it works: This snippet demonstrates how to query and extract specific pieces of information from JSON data stored within a database column. It provides examples for PostgreSQL, MySQL, and SQL Server, showcasing their respective native JSON functions like '->>' for text extraction in PostgreSQL, JSON_EXTRACT for MySQL, and JSON_VALUE for SQL Server. This is crucial for applications that store flexible, schema-less data, allowing dynamic access to nested properties without rigid schema changes.