SQL
Extract Data from JSON Columns in SQL
Learn how to query and extract specific values from JSON data stored in a database column using SQL's native JSON functions, crucial for modern applications (PostgreSQL syntax).
SELECT
id,
product_info->>'name' AS product_name,
(product_info->>'price')::numeric AS product_price,
product_info->'dimensions'->>'width' AS product_width
FROM
products
WHERE
(product_info->>'in_stock')::boolean = TRUE;
How it works: This query demonstrates how to extract values from a `JSONB` column named `product_info` in PostgreSQL. The `->>` operator retrieves a specific field from the JSON object as text. For numeric or boolean values, explicit casting (e.g., `::numeric`, `::boolean`) is used. It also shows extracting a nested value (`product_info->'dimensions'->>'width'`) and filtering rows based on a boolean value within the JSON data.