SQL
Querying Nested JSON Data in PostgreSQL
Efficiently query and filter records based on nested JSON column values in PostgreSQL, extracting specific attributes from stored JSON documents.
SELECT
id,
data->>'name' AS item_name,
data->'details'->>'category' AS item_category,
data->'tags' AS item_tags
FROM
products
WHERE
data->'details'->>'status' = 'available'
AND (data->'tags')::jsonb ? 'electronics';
How it works: This snippet demonstrates how to query and filter data stored in a JSONB column in PostgreSQL. It uses the `->` operator to extract a JSON object or array and the `->>` operator to extract a JSON object field as text. The `?` operator efficiently checks for the existence of a key or element within a JSONB array or object, allowing complex filtering on structured JSON data.