PostgreSQL JSONB: Store & Query JSON in Postgres

Relational meets document

PostgreSQL can store JSON natively and query inside it — giving you the flexibility of a document database without leaving your relational one. The workhorse type is JSONB, a binary, indexed representation of JSON that powers everything from flexible product attributes to event payloads.

CREATE TABLE events (
  id   bigserial PRIMARY KEY,
  data jsonb NOT NULL
);

INSERT INTO events (data) VALUES
  ('{"type":"signup","user":{"id":91,"plan":"pro"}}');

JSON vs JSONB

Postgres has two types, and the difference matters:

Use JSONB unless you specifically need to preserve the exact input text. It is faster to query and the only one you can index meaningfully.

Querying inside JSONB

Postgres provides operators to reach into JSON:

-- ->  returns JSON, ->> returns text
SELECT data->'user'->>'plan' AS plan FROM events;

-- #>> extracts a nested value by path
SELECT data#>>'{user,id}' AS user_id FROM events;

-- @> checks containment (does data contain this JSON?)
SELECT * FROM events WHERE data @> '{"type":"signup"}';

-- ? checks for a top-level key
SELECT * FROM events WHERE data ? 'type';

The @> containment operator is the one to know — it answers "find rows whose JSON includes this shape" and it can use an index.

Indexing with GIN

Without an index, JSONB queries scan every row. A GIN index makes containment and key lookups fast:

CREATE INDEX idx_events_data ON events USING gin (data);

-- now this uses the index:
SELECT * FROM events WHERE data @> '{"type":"signup"}';

For a specific hot path, an expression index on one extracted field is even leaner:

CREATE INDEX idx_events_plan ON events ((data->'user'->>'plan'));

Updating JSONB

Modify JSON in place with jsonb_set:

UPDATE events
SET data = jsonb_set(data, '{user,plan}', '"enterprise"')
WHERE id = 1;

You can add keys, replace values, and remove keys (data - 'key') without rewriting the whole document in your application.

When to use JSONB — and when not to

Reach for JSONB when: - The shape is genuinely variable (user-defined fields, third-party payloads). - You want to store a whole document alongside relational columns. - You are prototyping and the schema is still moving.

Prefer normal columns when: - The fields are stable and queried often — typed columns are faster, smaller, and enforce constraints. - You need foreign keys and strong typing on the data.

A common, healthy pattern is hybrid: stable fields as real columns, the flexible extras in a JSONB column.