How to Query JSON in PostgreSQL
Last updated July 25, 2026 · By the SaturnSQL team
Use -> to get a JSON value, ->> to get it as text, and @> for containment checks, all on jsonb columns. jsonb_array_elements() expands a JSON array into rows you can join and aggregate.
SELECT
payload -> 'user' AS user_object,
payload ->> 'event_type' AS event_type,
payload -> 'user' ->> 'id' AS user_id
FROM events
WHERE payload ->> 'event_type' = 'purchase';Containment and array expansion
SELECT id
FROM events
WHERE payload @> '{"source": "mobile"}';
SELECT e.id, item ->> 'sku' AS sku
FROM events e,
jsonb_array_elements(e.payload -> 'items') AS item;Prefer jsonb over json: it is indexable and faster to query. A GIN index (CREATE INDEX ON events USING gin (payload)) makes @> lookups fast.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free