PostgreSQL JSONB: Querying, Indexing and When to Use It

PostgreSQL’s jsonb type stores JSON in a decomposed binary format that’s fast to query and can be indexed. It lets you keep flexible, document-style data next to your relational tables, often removing the need for a separate document database.

json vs jsonb

	json	jsonb
Storage	Exact text copy	Binary, parsed
Whitespace, key order, duplicate keys	Preserved	Normalized (last duplicate key wins)
Query speed	Re-parsed every time	Fast
Indexing (GIN)	No	Yes

Use jsonb unless you must preserve the exact original text.

Create and insert

CREATE TABLE events (
  id bigserial PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT now(),
  data jsonb NOT NULL
);

INSERT INTO events (data) VALUES
  ('{"type": "signup", "user": {"id": 42, "country": "JP"}, "tags": ["trial", "ads"]}'),
  ('{"type": "payment", "user": {"id": 42}, "amount": 19.5}');

Read values

SELECT data->'user'           AS user_json,     -- jsonb
       data->'user'->>'id'    AS user_id,       -- text
       data#>>'{user,country}' AS country,      -- path, as text
       data['type']           AS type           -- subscript syntax (PG 14+)
FROM events;

Operator	Returns
`->` key / index	jsonb
`->>` key / index	text
`#>` / `#>>` path array	jsonb / text
`@>`	Left contains right
`?` / `?	`/`?&`
`@?` / `@@`	jsonpath exists / matches

Filter rows

SELECT * FROM events WHERE data->>'type' = 'signup';
SELECT * FROM events WHERE data @> '{"type": "signup", "user": {"country": "JP"}}';   -- containment
SELECT * FROM events WHERE data->'tags' ? 'trial';                                   -- array element
SELECT * FROM events WHERE data @? '$.amount ? (@ > 10)';                             -- jsonpath
SELECT * FROM events WHERE (data->>'amount')::numeric > 10;                           -- cast for comparison

Update

UPDATE events SET data = jsonb_set(data, '{user,country}', '"HK"') WHERE id = 1;
UPDATE events SET data = data || '{"processed": true}' WHERE id = 1;          -- merge keys
UPDATE events SET data = data - 'processed' WHERE id = 1;                       -- remove key
UPDATE events SET data = data #- '{user,country}' WHERE id = 1;                 -- remove path
UPDATE events SET data['status'] = '"done"' WHERE id = 2;                       -- PG 14+

Every update writes a new version of the whole row, so very large documents that change often are expensive.

Index jsonb

GIN index on the whole column, for containment and key-existence queries:

CREATE INDEX idx_events_data ON events USING gin (data);                 -- supports @>, ?, ?|, ?&, @?, @@
CREATE INDEX idx_events_data_path ON events USING gin (data jsonb_path_ops);  -- smaller, faster; @>, @?, @@ only

Expression (B-tree) index for one frequently filtered or sorted key:

CREATE INDEX idx_events_type ON events ((data->>'type'));
SELECT * FROM events WHERE data->>'type' = 'payment';     -- uses idx_events_type

Write queries to match the index. A GIN index helps data @> '{"type":"payment"}', but not data->>'type' = 'payment', which needs the expression index. Verify with EXPLAIN ANALYZE.

Turn JSON into rows, and rows into JSON

-- array elements as rows
SELECT id, jsonb_array_elements_text(data->'tags') AS tag FROM events;

-- object keys and values
SELECT key, value FROM events, jsonb_each(data) WHERE id = 1;

-- build JSON for an API response
SELECT jsonb_agg(jsonb_build_object('id', id, 'type', data->>'type')) FROM events;

Schema design advice

Promote stable, frequently queried fields to real columns (with types, constraints, foreign keys). Keep jsonb for variable attributes, metadata, and raw payloads.
Validate shape with CHECK constraints for required keys (CHECK (data ? 'type')), or in the application.
Avoid huge documents. Rows over about 2 KB are compressed and moved to TOAST storage, which makes access slower.
Use generated columns to expose important keys as typed, indexable columns:

ALTER TABLE events ADD COLUMN event_type text GENERATED ALWAYS AS (data->>'type') STORED;

jsonb vs MongoDB

With jsonb you get documents plus transactions, joins, SQL, and one database to back up and operate. MongoDB still has advantages for very large document workloads and its native sharding. See PostgreSQL vs MongoDB and MySQL JSON columns.

Quick reference

Task	SQL
Get text value	`data->>'key'`
Nested path	`data#>>'{a,b}'`
Contains	`data @> '{"k":"v"}'`
Key exists	`data ? 'k'`
Set value	`jsonb_set(data, '{a,b}', '"v"')`
Index all	`USING gin (data jsonb_path_ops)`
Index one key	`CREATE INDEX ON t ((data->>'k'))`

PostgreSQL
JSON
Database