PostgreSQL Basics #7 JSONB: Schema Flexibility Inside a Relational Database

Infrastructure PostgreSQL Database
4 min read

“This part’s schema keeps changing — do we need a separate NoSQL store just for it?” PostgreSQL’s answer to that worry is JSONB. You put a JSON document into a column of a relational table, and you can even index and search inside it. It’s the flagship case of chapter 1’s “things that used to require a separate system are built in,” and it settles a good share of the choice discussed in DynamoDB vs RDS with “just PostgreSQL.”

It’s jsonb, not json #

There are two types. json stores the input text as-is; jsonb stores a parsed binary form. The working default is jsonb. Read operations are much faster and it can be indexed. The only reason to pick json is the unusual requirement of “must preserve the original input verbatim (key order, duplicate keys, whitespace).”

Create the events table 
CREATE TABLE events (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    bigint NOT NULL REFERENCES users (id),
    event_type text NOT NULL,
    payload    jsonb NOT NULL DEFAULT '{}',
    created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO events (user_id, event_type, payload) VALUES
    (1, 'purchase', '{"item": "keyboard", "amount_usd": 89, "coupon": {"code": "AUG10", "rate": 0.1}}');

Event logs, stored responses from external APIs, product attributes that differ per product — “data whose shape differs per row” is JSONB’s home turf.

Operators: extracting and asking #

JSONB operators 
-- Extract: -> returns jsonb, ->> returns text
SELECT payload -> 'coupon' -> 'code'   FROM events;  -- "AUG10" (jsonb)
SELECT payload ->> 'item'              FROM events;  -- keyboard (text)
SELECT payload #>> '{coupon,code}'     FROM events;  -- AUG10 (text, via a path)

-- Ask: ? tests key existence, @> tests containment
SELECT * FROM events WHERE payload ? 'coupon';                      -- rows that have a coupon key
SELECT * FROM events WHERE payload @> '{"item": "keyboard"}';       -- rows containing this structure

Distinguishing -> from ->> is the first gate. -> yields jsonb, so you can keep chaining; ->> extracts text, for the final step of comparison or output. Comparing payload ->> 'amount_usd' against a number requires a cast: (payload ->> 'amount_usd')::numeric. Types inside JSON are loose, so the type discipline built in chapter 2 reappears at the JSONB boundary in the form of casts.

The GIN index: JSONB search’s partner #

B-tree indexes sort “the whole column value,” so they can’t see inside a document. JSONB search pairs with the GIN index, which inverts and catalogs the keys and values inside documents, backing @> (containment) and ? (existence) searches.

Create a GIN index 
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- this search now uses the index
SELECT * FROM events WHERE payload @> '{"coupon": {"code": "AUG10"}}';

Verify the effect with chapter 5’s EXPLAIN ANALYZE (a Bitmap Heap Scan is the normal shape). But GIN indexes the entire document, so write cost and size are substantial. If you only ever search one specific key, an expression index putting a B-tree on just that expression (ON events ((payload ->> 'item'))) is far lighter. Deeper GIN material (options, comparisons with other index types) comes in the practice series.

Updates are document-sized #

Update with jsonb_set 
UPDATE events
SET payload = jsonb_set(payload, '{coupon,rate}', '0.15')
WHERE id = 1;

There is syntax (jsonb_set) for changing a single path, but at the storage layer, chapter 6’s MVCC still applies: a whole new version of the row is written. “It’s just one field, so it must be cheap” is false. If your workload frequently patches small parts of large documents, the right design is pulling the frequently changing values out of the document into ordinary columns.

The design boundary: when you want to shove everything into JSONB #

JSONB’s temptation is “let’s just put everything in payload.” The boundary is clear: join keys, values you frequently search or sort by, and values you want protected by NOT NULL, CHECK, or FK stay as ordinary columns. Those constraints don’t reach inside JSONB (chapter 2’s “integrity lives in the database layer” principle is powerless inside a document), so promote the stable core fields to columns and leave only the shape-shifting periphery in JSONB. The events table above is the model: user_id and event_type are columns; the rest is payload.

Summary #

  • The type default is jsonb. Plain json is only for the special requirement of preserving input verbatim.
  • Extraction hinges on -> (jsonb) vs -» (text), with casts following for comparisons. Search is ? (existence) and @> (containment).
  • JSONB search pairs with a GIN index. If you only search one key, an expression index is lighter.
  • Even a partial update writes a whole new row version under MVCC. Pull frequently changing values out into columns.
  • Join keys, search targets, and constraint-protected values are columns; only the shape-shifting periphery goes in JSONB. Next chapter raises query expressiveness: views, CTEs, window functions.
X