PostgreSQL JSONB: Query Nested Data and Choose the Right GIN Index

JSONB is useful for attributes that genuinely vary between rows: webhook payloads, product metadata, integration settings, or event properties. It is not an excuse to hide a relational model in one column. The difference matters most when the table grows and every JSON predicate becomes a sequential scan.

Sample Data

CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT now(),
  payload jsonb NOT NULL
);

INSERT INTO events (payload) VALUES
('{"type":"payment","amount":89.90,"customer":{"country":"BR"},"tags":["vip","mobile"]}'),
('{"type":"signup","customer":{"country":"US"},"tags":["web"]}');

Read Scalars and Nested Values

-- JSON value
SELECT payload -> 'customer' AS customer
FROM events;

-- Text value, suitable for comparison
SELECT id, payload ->> 'type' AS event_type
FROM events
WHERE payload ->> 'type' = 'payment';

-- Nested text value
SELECT id
FROM events
WHERE payload #>> '{customer,country}' = 'BR';

Use -> when the next operation needs JSONB and ->> when you need text. Mixing them is a frequent source of confusing operator errors.

Containment: The Best Fit for a General GIN Index

The containment operator @> asks whether a document contains a JSON fragment. It maps naturally to a general-purpose GIN index.

CREATE INDEX events_payload_gin_idx
ON events USING gin (payload);

SELECT id, created_at
FROM events
WHERE payload @> '{"type":"payment","customer":{"country":"BR"}}';

Use EXPLAIN (ANALYZE, BUFFERS) after creating the index. PostgreSQL may correctly choose a sequential scan on a tiny table or a low-selectivity predicate.

Expression Indexes for One Hot Key

If one JSON key is filtered constantly, a small B-tree expression index is often better than a large general GIN index.

CREATE INDEX events_type_idx
ON events ((payload ->> 'type'));

SELECT COUNT(*)
FROM events
WHERE payload ->> 'type' = 'payment';

For a nested key, index the exact expression used by the query:

CREATE INDEX events_country_idx
ON events ((payload #>> '{customer,country}'));

SELECT id
FROM events
WHERE payload #>> '{customer,country}' = 'BR';

Arrays and Existence

-- Does the document have a top-level key?
SELECT id FROM events WHERE payload ? 'amount';

-- Does tags contain "vip"?
SELECT id FROM events WHERE payload @> '{"tags":["vip"]}';

A default GIN index supports these operators. Do not assume it accelerates every JSONPath expression; verify the plan for the exact operator you use.

When JSONB Is the Wrong Model

• A value is required for every row and participates in joins.
• You need foreign keys, uniqueness, or range constraints on it.
• You sort or aggregate it on most requests.
• The key is central to your product domain, not incidental metadata.

In those cases, model the value as a typed column. Keep flexible, sparse attributes in JSONB and move stable, high-traffic fields into columns as the application matures.

PG Monitoring helps here by showing which JSONB predicates consume time, whether an index changed the plan, and whether index maintenance is becoming part of the write bottleneck.