Open navigation menu

JSON Data Query and Manipulation

Queries and manipulates JSON/JSONB data using operators for extraction, filtering, and modification of nested JSON structures in PostgreSQL.

SqlData Fetching

Code

snippet
SELECT
    id,
    metadata->>'name' AS name,
    metadata->'address'->>'city' AS city,
    metadata->'tags' AS tags_array,
    metadata #>> '{address,zip}' AS zip_code,
    jsonb_array_length(metadata->'tags') AS tag_count
FROM documents
WHERE metadata @> '{"status": "published"}'
  AND metadata->'address'->>'city' = 'New York'
  AND metadata->'tags' ? 'featured'
  AND metadata->>'priority' IS NOT NULL
ORDER BY (metadata->>'priority')::int DESC;

-- Update JSON data
UPDATE documents
SET metadata = jsonb_set(
        metadata,
        '{last_updated}',
        to_jsonb(CURRENT_TIMESTAMP)
    ) || jsonb_build_object(
        'view_count',
        COALESCE((metadata->>'view_count')::int, 0) + 1
    )
WHERE id = 42;

-- Extract and expand JSON array
SELECT
    d.id,
    tag.value AS tag
FROM documents d,
     jsonb_array_elements(d.metadata->'tags') AS tag
WHERE d.metadata->'tags' IS NOT NULL;

Related Examples