SQL
Extracting Data from JSON Columns in SQL
Learn how to query and extract specific values from JSON data stored directly within a column in your SQL database, useful for flexible schema.
-- PostgreSQL/MySQL 8+ example
SELECT
id,
data->>'$.name' AS customer_name,
data->>'$.address.city' AS customer_city
FROM
orders
WHERE
data->>'$.status' = 'pending';
-- SQL Server example
SELECT
id,
JSON_VALUE(data, '$.name') AS customer_name,
JSON_VALUE(data, '$.address.city') AS customer_city
FROM
orders
WHERE
JSON_VALUE(data, '$.status') = 'pending';
How it works: This snippet demonstrates how to query and extract specific elements from a JSON column. For PostgreSQL/MySQL 8+, the `->>` operator is used to extract a JSON field as text. For SQL Server, `JSON_VALUE` achieves a similar result. This is highly useful for managing semi-structured data within traditional relational databases.