SQL
Querying and Extracting Data from JSONB Fields in PostgreSQL
Learn to effectively query and extract specific values from JSONB columns in PostgreSQL using operators like '->' and '->>', and functions like jsonb_array_elements_text().
SELECT
id,
data->>'productName' as product_name,
data->'details'->>'color' as product_color,
jsonb_array_elements_text(data->'tags') as tag
FROM
products
WHERE
data->>'status' = 'available' AND
data->'price' @> '{"currency": "USD"}'; -- Example: Check nested object content
How it works: This SQL snippet demonstrates how to interact with JSONB data in PostgreSQL, a powerful feature for semi-structured data. The `->>` operator extracts a JSON object field as text (e.g., `data->>'productName'`), while `->` extracts it as JSONB (e.g., `data->'details'`). For arrays, `jsonb_array_elements_text()` can be used to unnest each element into separate rows. The `WHERE` clause also shows how to filter based on JSON values and even check for containment using the `@>` operator, making it flexible for dynamic data schemas.