JSONB introduction

Last reviewed May 28, 2026 Content v20260528

Track mode

sql_sandbox

Means

SQL sandbox

Reading

~2 min

Level

intermediate

This lesson

An orientation to the PostgreSQL track—core vocabulary, dialect notes, and what you will practice next.

You need a clear map of the PostgreSQL track so JSONB, extensions, and Postgres idioms do not feel like magic.

You will apply JSONB introduction in contexts like: Semi-structured product config, feature flags, and API payloads without a separate document DB.

Copy Postgres SQL into psql, local PostgreSQL, or DB Fiddle (PostgreSQL dialect)—use \d and EXPLAIN ANALYZE where lessons show them. The in-browser lab ships later; psql is the practice path now. Also read the interview prep blocks; use -> and ->> operators; index with GIN when filtering JSONB often.

After the core SQL track—when your stack standardizes on a specific database engine.

JSONB stores JSON in a decomposed binary format—efficient storage and indexing. Ideal for flexible attributes, feature flags, and event payloads alongside relational columns.

JSON vs JSONB

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  kind TEXT NOT NULL,
  payload JSONB NOT NULL
);

INSERT INTO events (kind, payload)
VALUES ('signup', '{"plan": "pro", "referrer": "newsletter"}');

Use JSONB almost always; plain JSON preserves whitespace and key order but is slower to query.

Practice: Run in psql or DB Fiddle (PostgreSQL). Use your practice database from the workflow lesson.

Operators and paths

SELECT payload->>'plan' AS plan
FROM events
WHERE payload @> '{"plan": "pro"}';

SELECT payload #>> '{referrer}' AS referrer
FROM events;

GIN indexes (preview)

CREATE INDEX idx_events_payload ON events USING GIN (payload);

GIN indexes accelerate containment (@>) and path queries—add when JSONB filters appear in EXPLAIN plans.

Important interview questions and answers

Q: -> vs ->>?
A: -> returns JSON; ->> returns text—use ->> for string comparisons.
Q: JSONB mutability?
A: Use jsonb_set for partial updates without rewriting the whole document in application code.

Self-check

Which type should you default to for queryable JSON?
What operator checks JSON containment?

Tip: Add a GIN index when JSONB filters show up in hot EXPLAIN plans.

Interview prep

->> operator?: Extracts JSON field as text.
@> operator?: Tests JSON containment.

Discussion

Past discussion is visible to everyone. Only logged-in users can post comments and replies.

Starter discussion topics

-> vs ->>?
GIN index when?

No discussion yet. Be the first to ask a question.