JSON and JSONB in PostgreSQL

PostgreSQL can store semi-structured data directly in a column using two related types: JSON and JSONB. This lets you combine the strong guarantees of a relational schema with the flexibility of a document store for fields that genuinely vary in shape — settings blobs, event payloads, third-party API responses — without resorting to a separate NoSQL database.

The two types look similar on the surface but store data very differently, and that difference has real consequences for performance and what operations are efficient.

Why it matters

Some data does not fit fixed columns: product attributes that differ by category, settings, payloads from other systems. JSONB stores such documents inside an ordinary table and lets you query and index them, giving much of the flexibility of a document database without leaving PostgreSQL.

JSON vs. JSONB

JSON stores an exact textual copy of the input, including whitespace and key order, and re-parses it every time it is queried. JSONB ("JSON binary") parses the input once at write time into a decomposed binary format, discarding insignificant whitespace and duplicate keys. JSONB is slightly slower to insert but substantially faster to query, and — critically — it is the only one of the two that can be indexed efficiently. For nearly every real application, JSONB is the correct choice; plain JSON is mainly useful when you must preserve the exact original text.

Querying with -> and ->>

The -> operator extracts a field as JSON/JSONB (preserving type), while ->> extracts it as text. For nested access you chain them, using ->> only on the final step if you want a text result: data->'address'->>'city'. The #> and #>> operators do the same thing with a path array, which is handy for deeply nested or dynamic paths: data #>> '{address,city}'.

Containment and Indexing

JSONB supports the @> ("contains") and ? ("has key") operators, which let you ask questions like "does this document contain this sub-object" or "does this object have this key" directly. These operators can use a GIN index built on the JSONB column, making containment queries fast even over millions of rows — something plain JSON cannot do.

Examples

CREATE TABLE events (
    id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload JSONB NOT NULL
);

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

SELECT
    payload->>'type'          AS event_type,
    payload->'user'->>'plan'  AS plan
FROM events;

 event_type | plan
------------+------
 signup     | pro

CREATE INDEX idx_events_payload ON events USING GIN (payload);

SELECT id FROM events
WHERE payload @> '{"user": {"plan": "pro"}}';

CREATE INDEX
 id
----
  1

Exercise

This exercise uses PostgreSQL's JSONB type and operators, so it needs a PostgreSQL server. Create a products table with a JSONB column named attributes. Insert a pen whose attributes are colour blue and pack size 10, and a bag whose attributes are colour red. Write one query that returns the name and the colour, as text, of every product, and a second that finds the products whose attributes contain colour blue, using the containment operator.

Expected result: the first query returns Pen with blue and Bag with red; the second returns Pen.

-- TODO: products (id, name, attributes jsonb)

-- TODO: insert the pen and the bag

-- TODO: name and colour (as text) of every product

-- TODO: products whose attributes contain {"color": "blue"}

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  attributes JSONB NOT NULL DEFAULT '{}'
);

INSERT INTO products (id, name, attributes) VALUES
  (1, 'Pen', '{"color": "blue", "pack_size": 10}'),
  (2, 'Bag', '{"color": "red"}');

SELECT name, attributes ->> 'color' AS color
FROM products;

SELECT name
FROM products
WHERE attributes @> '{"color": "blue"}';

Quick quiz

Interview questions