JSONB in Postgres: when to use it, how to index it

JSONB is one of the reasons teams pick Postgres over a document database: you get schemaless flexibility where you need it, and real columns, constraints and joins everywhere else. The trap is using it everywhere, and rediscovering why schemas exist.

json vs jsonb

json stores the document as text. It keeps whitespace, key order and duplicate keys, and reparses on every access. jsonb stores a decomposed binary form: marginally slower on insert, much faster to query, and — crucially — indexable. It also normalises the document, dropping whitespace and duplicate keys and not preserving key order.

Pick jsonb almost always. Pick json only when you must return the payload exactly as received, for example when a signature is computed over the raw bytes.

The operators you'll actually use

-- extract
payload ->  'status'          -- as jsonb  ("active", with quotes)
payload ->> 'status'          -- as text   (active)
payload #>> '{customer,email}' -- nested path, as text

-- test
payload @> '{"status":"active"}'::jsonb   -- containment
payload ?  'tracking_id'                   -- does this key exist?
payload ?| array['email','phone']          -- any of these keys
payload @? '$.items[*] ? (@.qty > 10)'     -- jsonpath predicate

The single most common bug: payload -> 'status' = 'active' never matches, because the left side is the JSON string "active", quotes included. Use ->> for text comparisons, and cast for numbers: (payload ->> 'amount')::numeric > 100.

Indexing: pick the one that matches your query

GIN, for containment and key existence

create index events_payload_gin on events using gin (payload);

-- uses it
select * from events where payload @> '{"type":"invoice.paid"}';
select * from events where payload ? 'tracking_id';

If @> is the only operator you use, a narrower operator class gives you a smaller and faster index:

create index events_payload_gin on events using gin (payload jsonb_path_ops);

jsonb_path_ops indexes only the hashed paths-plus-values needed for containment. It covers @> and the jsonpath operators @? and @@, but not the key-existence family ?, ?| and ?& — those need the default jsonb_ops.

B-tree expression index, for one hot key

A GIN index over the whole document is the wrong tool if 90% of your queries filter on one field. This is smaller, supports ranges and sorting, and is usually much faster:

create index events_status_idx on events ((payload ->> 'status'));
create index events_amount_idx on events (((payload ->> 'amount')::numeric));

The expression in the query must match the index exactly, casts included.

Promote hot keys with a generated column

Cleaner still: pull the field out into a real, always-consistent column that the database maintains for you.

alter table events
  add column status text generated always as (payload ->> 'status') stored;

create index events_status_idx on events (status);

Now it indexes, sorts and reports like an ordinary column, but there's still exactly one source of truth. This is often the right migration path when a JSONB field graduates into a first-class concept.

Querying arrays and nested documents

To treat a JSON array as rows, expand it:

select e.id, item ->> 'sku' as sku, (item ->> 'qty')::int as qty
from events e,
     lateral jsonb_array_elements(e.payload -> 'items') as item
where (item ->> 'qty')::int > 10;

Note this can't use an index on the expanded values — it expands every candidate row first. If the array filter is selective and hot, either pre-filter with a containment predicate that can use the GIN index, or normalise the items into their own table.

Updating JSONB without clobbering it

-- set or replace one key
update events set payload = jsonb_set(payload, '{status}', '"processed"') where id = $1;

-- shallow merge
update events set payload = payload || '{"retried": true}'::jsonb where id = $1;

-- delete a key
update events set payload = payload - 'temp_token' where id = $1;

Every one of these rewrites the whole document, because Postgres updates whole rows. For a large payload that's a real cost — and for big documents the value lives in TOAST storage, so each update rewrites that too. Frequently-changing fields are a strong signal for a real column.

Constraints still apply

Schemaless doesn't have to mean unvalidated:

alter table events add constraint events_payload_is_object
  check (jsonb_typeof(payload) = 'object');

alter table events add constraint events_type_present
  check (payload ? 'type');

Four gotchas

• SQL NULL vs JSON null. payload ->> 'x' returns SQL NULL both when the key is missing and when its value is JSON null. Use payload ? 'x' to tell them apart.
• Planner estimates are weaker. Postgres has far less statistical information about the inside of a document than about a column, so plans over heavy JSONB predicates are more likely to go wrong. Check them with EXPLAIN ANALYZE.
• Numbers are normalised. jsonb stores numbers as numeric, so 1.10 comes back as 1.10 but 1e2 comes back as 100. Don't rely on the original formatting.
• Big documents are slow to write. If a row's JSONB is hundreds of kilobytes and changes constantly, you've built a document store inside a relational one, with the costs of both.

Over the REST API

Because WattleDB's REST layer is PostgREST over plain Postgres, JSONB columns come back as real JSON in the response, and you can filter on them from the client using PostgREST's arrow syntax — no server code in between. The instant REST API guide covers the query syntax; row-level security applies to JSONB columns exactly as it does to any other.