JSON

Postgres jsonb operators

Q: jsonb or json — which should I use?

jsonb for almost everything. json stores raw text (preserves whitespace, duplicate keys); jsonb stores a parsed binary form (faster reads, supports the rich operator set, can be indexed). Only use json when you genuinely need the original text preserved.

Q: What's the difference between -> and ->>?

-> returns a jsonb value (could be an object, array, or scalar). ->> returns the same value as text. Use -> when you want to chain (payload -> 'user' -> 'profile' ->> 'country'); use ->> for the final extraction to text.

Q: How do I check if a key exists?

payload ? 'field' — top-level key existence test. For nested keys, combine with the path operator: payload #> '{user, profile, country}' IS NOT NULL. The ? operator checks "any of these keys exists"; ?& checks "all of these keys exist."

Postgres ships an entire algebra of operators for the jsonb type — extraction, containment, key existence, path traversal, and merge. Knowing these is the difference between writing a 3-line filter and a 30-line subquery chain. DuckDB mirrors the Postgres extraction operators (->, ->>), but the containment, existence, and mutation operators (@>, ?, #>, -, #-) are Postgres-specific.

The core operators #

Operator	Returns	Example	Result
`->`	jsonb	`'{"a":1,"b":2}'::jsonb -> 'a'`	`1` (as jsonb)
`->>`	text	`'{"a":1}'::jsonb ->> 'a'`	`'1'` (text)
`#>`	jsonb	`'{"a":{"b":1}}'::jsonb #> '{a,b}'`	`1` (as jsonb)
`#>>`	text	`'{"a":{"b":1}}'::jsonb #>> '{a,b}'`	`'1'` (text)
`@>`	bool	`'{"a":1,"b":2}'::jsonb @> '{"a":1}'`	`TRUE` — left contains right
`<@`	bool	`'{"a":1}'::jsonb <@ '{"a":1,"b":2}'`	`TRUE` — left is contained in right
`?`	bool	`'{"a":1}'::jsonb ? 'a'`	`TRUE` — top-level key exists
`?	`	bool	`'{"a":1}'::jsonb ?	array['a','c']`	`TRUE` — any of the keys
`?&`	bool	`'{"a":1,"b":2}'::jsonb ?& array['a','b']`	`TRUE` — all of the keys
`		`	jsonb	`'{"a":1}'::jsonb		'{"b":2}'`	`{"a":1,"b":2}` — shallow merge
`-`	jsonb	`'{"a":1,"b":2}'::jsonb - 'a'`	`{"b":2}` — remove key
`#-`	jsonb	`'{"a":{"b":1}}'::jsonb #- '{a,b}'`	`{"a":{}}` — remove nested

Example #

Loading SQL editor...

Containment is the killer feature #

@> ("contains") is the operator that makes Postgres jsonb shine. It asks "does the left value include the right value as a sub-document?" — exact match by structure, not by string. With a GIN index, it's fast.

sql

-- Find purchase events.
SELECT * FROM events
WHERE payload @> '{"event":"purchase"}'::JSON;

-- Find events with a specific tag (works inside nested arrays).
SELECT * FROM events
WHERE payload @> '{"tags":["vip"]}'::JSON;

-- The index that makes containment fast.
CREATE INDEX events_payload_idx ON events USING GIN (payload jsonb_path_ops);

Performance and indexing #

Use jsonb, not json. json stores the original text (preserves whitespace, key order, duplicates); jsonb stores a parsed binary form (faster on read, supports indexing). jsonb is the default choice unless you need to preserve the original text exactly.
GIN indexes make containment (@>) and key-existence (?) operators usable on large tables. USING GIN (col jsonb_path_ops) is smaller and faster for containment-only workloads; USING GIN (col) (the default) supports more operators but costs more.
Expression indexes on hot fields beat GIN for known-field queries: CREATE INDEX events_user_idx ON events ((payload ->> 'user_id')); makes WHERE payload ->> 'user_id' = '42' an index lookup.
Don't ->>-then-cast in a WHERE clause without an index — that's a sequential scan extracting and parsing JSON for every row. Either index the expression or, if the field is hot, promote it to a real column.

FAQ #

What does the @> operator do in jsonb?

"Contains" — returns TRUE if the left value includes the right value as a sub-document. '{"a":1,"b":2}' @> '{"a":1}' is TRUE. The most powerful filter operator and the one that benefits most from a GIN index. Useful for searching event payloads, user preferences, configurations.

jsonb or json — which should I use?

jsonb for almost everything. json stores raw text (preserves whitespace, duplicate keys); jsonb stores a parsed binary form (faster reads, supports the rich operator set, can be indexed). Only use json when you genuinely need the original text preserved.

How do I update a single field inside a jsonb column?

UPDATE events SET payload = jsonb_set(payload, '{user, plan}', '"pro"'::jsonb) WHERE id = 42. The path is an array of keys. Pass TRUE as the 4th argument to create the path if missing (default), FALSE to update only when the path exists.

What's the difference between -> and ->>?

-> returns a jsonb value (could be an object, array, or scalar). ->> returns the same value as text. Use -> when you want to chain (payload -> 'user' -> 'profile' ->> 'country'); use ->> for the final extraction to text.

How do I index a jsonb column?

CREATE INDEX events_payload_idx ON events USING GIN (payload jsonb_path_ops) — the path_ops opclass is faster and smaller for containment-only queries (@>). For broader operator support, drop jsonb_path_ops. For known-field queries, an expression index is sharper: CREATE INDEX ON events ((payload->>'user_id')).

How do I check if a key exists?

payload ? 'field' — top-level key existence test. For nested keys, combine with the path operator: payload #> '{user, profile, country}' IS NOT NULL. The ?| operator checks "any of these keys exists"; ?& checks "all of these keys exist."

Tip

The migration arc for any heavily-queried JSON field. Stage 1: payload ->> 'user_id' everywhere — fine while ad-hoc. Stage 2: expression index on the field — fixes the read perf. Stage 3: promote the field to its own column with a NOT NULL constraint and backfill — fixes the modeling problem and makes the field discoverable.