PostgreSQL JSON vs JSONB: Types, Operators & Indexing

json vs jsonb

The json type stores an exact copy of the input text and reparses it on every access, preserving whitespace, key order, and duplicate keys. The jsonb type stores a decomposed binary form: slightly slower to insert because of the conversion, but far faster to query because no reparsing is needed, and it supports indexing. In practice jsonb queries can be many times faster than json on read-heavy workloads.

The rule is simple: use jsonb unless you specifically need to preserve the exact original text. For almost all applications, jsonb is the right default.

Accessing fields: -> and ->>

Two operators read values out of a JSON column. The -> operator returns a JSON value, while ->> returns text. So data->'user'->>'name' navigates into the user object and returns the name as text. For deep paths, the #> and #>> operators take a path array, letting you reach a nested value in one step. Choosing -> versus ->> matters because comparisons and casts usually need the text form from ->>.

SELECT data->'user'->>'name' AS name
FROM   events
WHERE  data->>'status' = 'active';

Containment and existence operators

SELECT * FROM events
WHERE  data @> '{"status":"active"}';

• @> tests containment: does the JSON contain this sub-object? Ideal for filtering rows whose JSON includes a given key-value pair.
• ? tests whether a top-level key exists.
• ?| and ?& test whether any or all of several keys exist.
• These operators work on jsonb (not json) and are what make jsonb genuinely queryable.

Indexing jsonb with GIN

Without an index, querying a JSON column scans every row. A GIN (Generalized Inverted Index) fixes this for containment and existence queries. The default jsonb_ops operator class indexes every key and value and supports @>, ?, ?|, and ?&. The more compact jsonb_path_ops class indexes only values and supports @> alone — choose it when your queries are dominated by containment, because it is smaller and faster for that pattern.

CREATE INDEX idx_events_data
  ON events USING GIN (data jsonb_path_ops);

B-tree expression indexes for specific fields

If you always filter or sort by one field inside the JSON — a tenant_id, a status, or a timestamp — a B-tree expression index on that extracted value is often the cleanest win. You index the expression (data->>'status'), and equality and range queries on it become fast. Use GIN for flexible containment queries and a B-tree expression index for hot, specific fields.

When not to use JSON columns

JSON columns are for genuinely semi-structured or sparse data. If a field is present on every row and you query or join on it constantly, a normal typed column with a normal index is simpler and faster. Reaching for jsonb to avoid defining a schema is a common anti-pattern; use it to complement your relational model, not to replace it. To inspect and clean JSON before loading it, a formatter and validator help you confirm the shape.