SQL
Querying and Extracting JSON Data from SQL Columns
Learn to effectively query and extract specific values from JSON or JSONB data stored directly within your SQL database columns using native functions.
-- PostgreSQL example for JSONB data type:
SELECT
id,
data->>'customer_name' AS customer_name, -- Text extraction
data->'shipping_address'->>'city' AS shipping_city, -- Nested text extraction
(data->>'order_total')::numeric AS order_total -- Type casting
FROM
orders
WHERE
data->>'status' = 'pending' AND (data->>'order_total')::numeric > 100;
-- MySQL 8.0+ example for JSON data type:
SELECT
id,
JSON_UNQUOTE(JSON_EXTRACT(data, '$.customer_name')) AS customer_name,
JSON_UNQUOTE(JSON_EXTRACT(data, '$.shipping_address.city')) AS shipping_city,
JSON_EXTRACT(data, '$.order_total') AS order_total
FROM
orders
WHERE
JSON_EXTRACT(data, '$.status') = 'pending' AND JSON_EXTRACT(data, '$.order_total') > 100;
-- Note: MySQL's JSON_EXTRACT returns JSON strings, use JSON_UNQUOTE for plain text.
How it works: Many modern SQL databases support storing and querying JSON data directly within a column. PostgreSQL offers operators like `->` (returns JSON object/array) and `->>` (returns text) for `JSONB` data. MySQL provides functions like `JSON_EXTRACT(column, '$.path.to.value')` to navigate and extract values. These functions allow developers to treat JSON fields almost like regular columns in WHERE clauses, SELECT lists, and even with indexing, providing flexibility for semi-structured data.