JSONB: Semi-Structured Data, First-Class

~14 min · extensions, jsonb

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

The relational + document hybrid

JSONB is PostgreSQL's binary-encoded JSON type. Unlike JSON (which stores text verbatim), JSONB parses on insert into a fast binary form, supports indexing (GIN), and offers a rich set of operators. It is the answer to most "do I need MongoDB?" questions.

The operators worth memorising

-> field access (returns JSONB): data->'user'.
->> field access as TEXT: data->>'name'.
@> contains: data @> '{"type":"click"}'.
? key exists: data ? 'email'.
jsonb_path_exists(data, '$.orders[*] ? (@.total > 100)') SQL/JSON path queries.

When to use JSONB and when not to

Reach for JSONB when the sub-object's shape genuinely varies (event payloads, third-party API responses, settings blobs). Avoid JSONB as a way to dodge schema design — if every row has the same five fields, those should be real columns with proper types and constraints.

Code

Create + query JSONB·sql

CREATE TABLE events (
    id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    data JSONB NOT NULL
);

CREATE INDEX events_data_gin ON events USING gin (data);

INSERT INTO events (data) VALUES
('{"type":"click","page":"/home","user_id":42}'),
('{"type":"signup","plan":"pro","user_id":43}'),
('{"type":"click","page":"/blog","user_id":42}');

-- Containment query (uses GIN)
SELECT data->>'page' AS page, COUNT(*)
FROM   events
WHERE  data @> '{"type":"click"}'
GROUP  BY 1;

-- Field access in SELECT
SELECT id, data->>'type' AS event_type, (data->>'user_id')::int AS user_id
FROM   events;

Update JSONB fields·sql

-- Set or replace a field
UPDATE events SET data = jsonb_set(data, '{plan}', '"enterprise"') WHERE id = 2;

-- Remove a field
UPDATE events SET data = data - 'page' WHERE id = 1;

-- Merge two JSONB documents (right wins on conflicts)
UPDATE events SET data = data || '{"reviewed": true}'::jsonb WHERE id = 1;

External links

Exercise

Add a JSONB metadata column to a table. Insert rows with varied keys. Query for rows containing a specific key/value with @>. Add a GIN index; compare EXPLAIN before and after.

Progress

Progress is local-only — sign in to sync across devices.

← 🔒 Transactions and ConcurrencyQuiz · 5 questions Next →When JSONB Helps and When It's Laziness

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.