Guides••11 min read

JSON in PostgreSQL: jsonb, Indexes and Practical Patterns

The complete guide to JSON in Postgres — json vs jsonb, query operators, GIN indexes, in-place mutation, and when to use schemaless columns.

By JSONEditor.net Team

json vs jsonb — pick jsonb

Postgres has two JSON types and the choice between them is almost always jsonb. The differences:

json stores the document as raw text. Every read reparses. Whitespace and key order are preserved exactly. Duplicate keys are kept (the last value wins on query but earlier ones are still in the bytes). Use it when you need to preserve the original byte stream — for example, signing payloads where the signature was computed over a specific JSON serialization.

jsonb stores a binary tree representation. Reads skip parsing (already a tree). Whitespace is dropped, keys are deduplicated, the internal order may differ from the input. Supports indexing, fast operators, and in-place mutation. Use this unless you have a specific reason not to.

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  created_at TIMESTAMPTZ DEFAULT now(),
  payload JSONB NOT NULL
);

Querying JSON

Postgres has two notations: chained accessors and JSONPath. Both work; chained accessors are more idiomatic for simple paths, JSONPath for complex queries.

-- Chained accessors
SELECT payload->>'user' AS user_id      -- text value
FROM events
WHERE payload->>'type' = 'login';

SELECT payload->'address'->>'city'      -- nested
FROM users
WHERE payload->'tags' ? 'premium';      -- ? checks key existence

-- JSONPath (Postgres 12+)
SELECT jsonb_path_query(payload, '$.items[*] ? (@.price > 100)')
FROM orders;

SELECT * FROM orders
WHERE payload @? '$.items[*] ? (@.discount > 0)';

The -> operator returns jsonb, the ->> operator returns text. Use ->> when you want to compare to a string or cast to another type; use -> when chaining more accessors. The most common mistake is payload->'count' > 10 which silently returns nothing — the comparison is between a jsonb and an integer. Cast: (payload->>'count')::int > 10.

Indexing for fast lookups

A jsonb column with millions of rows and no index is a sequential scan on every query. Fix it with a GIN index:

-- Default GIN index: supports ?, ?&, ?|, @>
CREATE INDEX events_payload_idx ON events USING GIN (payload);

-- Better operator support for path queries
CREATE INDEX events_payload_path_idx
  ON events USING GIN (payload jsonb_path_ops);

-- Targeted index on a specific frequently-queried key
CREATE INDEX events_type_idx ON events ((payload->>'type'));
CREATE INDEX events_user_idx ON events ((payload->>'user'));

The targeted expression index ((payload->>'key')) is faster and smaller than the full GIN index when you know which keys you query. The GIN index is the catch-all for ad-hoc queries against unknown shapes. Most production systems end up with a small GIN index for exploration plus a handful of targeted indexes for the hot paths.

Always EXPLAIN ANALYZE your queries after creating the index — Postgres' planner sometimes prefers a sequential scan for small tables or when statistics are stale.

Mutating JSON in place

jsonb_set and the || operator let you change a JSON document without reading and rewriting it from the application side:

-- Set a nested value
UPDATE users
SET payload = jsonb_set(payload, '{address,city}', '"Berlin"', true)
WHERE id = 42;

-- Add or overwrite top-level keys
UPDATE users
SET payload = payload || '{"verified": true, "last_login": "2026-05-26"}'::jsonb
WHERE id = 42;

-- Remove a key
UPDATE users
SET payload = payload - 'temporary_field'
WHERE id = 42;

-- Remove a deeply nested key (path notation)
UPDATE users
SET payload = payload #- '{settings,deprecated_option}'
WHERE id = 42;

These run server-side, which avoids the round-trip and the race window of "read, modify in app, write back." For concurrent writes against the same row, combine with SELECT ... FOR UPDATE in a transaction.

When to use JSON columns

Three patterns that genuinely benefit from jsonb:

Audit logs and events. Each event has a different shape (user.login carries different fields than order.placed). A jsonb column captures any payload without schema migrations, while still allowing queries like "all events for user X in the last hour."

Settings and preferences. User-tunable knobs that grow over time. Each new feature can add a key without an ALTER TABLE.

Sparse data. A table where most rows would have NULL in most columns. A jsonb column stores only the keys actually present, saving the per-row NULL overhead.

When NOT to use JSON columns

Foreign keys. You cannot put a foreign key constraint on a jsonb value. If payload->>'user_id' is meant to reference users.id, that referential integrity is now your application's problem.

Fields you always need. A jsonb column adds parse cost on every read (even with the binary representation, you walk a tree). If every query needs the email and name, those should be regular columns.

Joins. Joining on a jsonb value is slower than joining on an indexed integer column. Hot-path joins should be on extracted columns.

The compromise that works: extract the always-needed fields into regular columns, keep the rest in a jsonb "extra" column. Best of both worlds, used by Stripe's metadata, Sentry's event tags, and most modern SaaS schemas.

Gotchas

Number precision. Postgres stores numeric values in jsonb as arbitrary-precision numeric, which is more precise than JSON's defacto double. A large integer round-tripped through your JavaScript app may lose precision client-side even though Postgres preserves it.

Key order is not preserved in jsonb. If you sign or hash a JSON document, do the signing/hashing on the canonical form (the JSON sorter produces deterministic output) — never on the bytes Postgres returns from a jsonb column.

Index bloat. A GIN index on a large jsonb column grows fast. Monitor with pg_size_pretty(pg_indexes_size('events')) and consider a targeted expression index instead.

The @> containment operator is one of the most useful: payload @> '{"type": "login"}'::jsonb matches any row whose payload contains those keys with those values. Faster than chained accessors with the GIN index, and natural to write.

For a complete tour of JSON Schema validation outside the database (which you should still do at the API boundary), see the schema tutorial and the browser validator.

JSONCSVConvertersData