Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 90 ยท Data Types

JSONB in Practice

Use PostgreSQL JSONB well: choose between documents and columns, validate with CHECK, expand arrays into rows, and avoid the update and TOAST pitfalls.

Once you know the operators from JSON and JSONB, the harder question appears: should this data be JSON at all? PostgreSQL makes it easy to drop a jsonb column into any table, and teams often do so on day one because it feels flexible. Six months later they have a column that nobody dares to touch, queries that cannot use ordinary indexes, and a quiet performance problem.

This lesson is about judgement. You will see when a document column is the right tool, how to build a hybrid schema, how to validate documents so they stay clean, how to turn arrays of objects back into rows, and which costs hide behind UPDATE. The live examples run on SQLite's JSON functions, which behave like PostgreSQL's in spirit but use different names. Everything that is PostgreSQL-only is shown as a read-only block, and each block says so.

What runs live here. The <TryIt> editors use sql.js (SQLite). json_extract, json_each, json_patch and friends are SQLite functions; PostgreSQL spells the same ideas ->>, jsonb_array_elements, || and jsonb_set. Blocks labelled "PostgreSQL" are not executed on this page.

Documents or columns?

A relational column gives you a type, a constraint, a statistics entry and an index. A key inside a document gives you none of those for free. So the default rule is simple: if every row has the value and you filter, join or sort on it, make it a column. Reach for jsonb when the shape really varies from row to row, when you store a payload you mostly read back whole, or when the data comes from outside your control.

Good fits for JSONB

Varying attributes Shoes have sizes, hats do not.
Raw payloads Webhooks and API responses you keep as received.
User settings Sparse preferences that rarely get queried.
1 / 2

The pattern that works for most products is a hybrid schema: real columns for identity and everything common, plus one jsonb column for the long tail.

SQL
-- PostgreSQL (read-only)
CREATE TABLE products (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name   text    NOT NULL,
  price  numeric(10,2) NOT NULL CHECK (price >= 0),
  attrs  jsonb   NOT NULL DEFAULT '{}'::jsonb,
  CHECK (jsonb_typeof(attrs) = 'object')
);

Validating documents with CHECK

A jsonb column accepts any valid JSON: an array, a number, null. If your code expects an object with certain keys, say so in the schema. A CHECK constraint runs on every insert and update, so a bad document is rejected at the door instead of surfacing as a crash months later.

In PostgreSQL, jsonb_typeof(attrs) = 'object' checks the top-level type, attrs ? 'sku' checks that a key exists, and jsonb_typeof(attrs -> 'sizes') = 'array' checks a nested type. The text must already be valid JSON for jsonb to store it, so you never need a "is valid" check on the column itself. Recent versions (16 and later) also offer the SQL-standard IS JSON predicate for text columns.

SQLite stores JSON as plain text, so its version of the constraint also checks validity. The next example rejects text that is not JSON and JSON that is not an object. It uses INSERT OR IGNORE, which skips rows that violate a constraint instead of stopping the script, so you can see the survivors:

SQL A CHECK that guards a JSON column

Press Run (or Ctrl + Enter) to see the result here.

Only three rows survive. Notice what the last query shows: the hat has no waterproof or sizes key, so those cells are NULL. A missing key and a key set to JSON null look alike after extraction, which matters when you write conditions. In PostgreSQL, attrs ? 'waterproof' tests for the key itself.

Pulling data back into columns

Documents are convenient to store and awkward to analyse. Two techniques bring structure back.

Expand arrays into rows. When a document holds a list of objects, PostgreSQL's jsonb_to_recordset turns it into a table with typed columns. You supply the column list, and PostgreSQL casts each key for you:

SQL
-- PostgreSQL (read-only)
SELECT e.id, e.payload ->> 'user' AS usr, i.sku, i.qty, i.price
FROM events AS e
CROSS JOIN LATERAL jsonb_to_recordset(e.payload -> 'items')
     AS i(sku text, qty int, price numeric);

jsonb_array_elements does the same but hands you one jsonb value per element, which you then take apart with ->>. Version 17 added the SQL-standard JSON_TABLE, which does both steps in one clause. SQLite's equivalent is json_each, which returns one row per array element:

SQL Array of objects to rows, then aggregate

Press Run (or Ctrl + Enter) to see the result here.

Three item rows come out of two documents, and the second query reports 28.5 for A1 and 20 for B7. That is the whole trick: expand once, then use GROUP BY, joins and window functions as usual.

Promote hot keys to columns. If a key shows up in every query, copy it into a real column. In PostgreSQL the cleanest way is a generated column, which stays in sync automatically, plus an ordinary index on it:

SQL
-- PostgreSQL (read-only)
ALTER TABLE products
  ADD COLUMN color text GENERATED ALWAYS AS (attrs ->> 'color') STORED;
CREATE INDEX products_color_idx ON products (color);

You can also index the expression directly, CREATE INDEX ON products ((attrs ->> 'color')), without adding a column. For "does this document contain these keys and values" queries, a GIN index on the whole column is the right tool, covered in GIN and GiST Indexes.

Changing documents

Updating a key is easy to write and surprisingly expensive to run. PostgreSQL's jsonb_set(attrs, '{theme}', '"dark"') and the merge operator attrs || '{"theme":"dark"}' both return a new document. Because of MVCC, every UPDATE writes a complete new row version, and for a large document that means the whole value is copied even though one key changed.

SQLite has json_set, json_remove, and json_patch, which applies an RFC 7396 merge patch (a null value deletes the key). Run this to see all three on one document, and note that promoting a key to a column is just an UPDATE:

SQL Patch, set and remove keys

Press Run (or Ctrl + Enter) to see the result here.

Pitfalls to remember

Large documents live in TOAST. PostgreSQL stores rows in 8 kB pages. When a value grows past roughly 2 kB, it is compressed and may be moved to a side table (TOAST). Reading one key from a big document means fetching and decompressing the whole thing. A table of tiny documents behaves like any other table; a table of 200 kB documents does not.

Updates rewrite the value. Counters, flags and timestamps that change often belong in columns, not buried in a document. A "last seen" key updated on every request is a classic cause of table bloat, because each change leaves a dead row version behind for vacuum to clean up.

No statistics inside the document. The planner keeps per-column statistics, but it knows almost nothing about the keys within a jsonb value, so its row estimates for attrs ->> 'color' = 'red' are guesses. An expression index or a generated column gives the planner real numbers.

jsonb is normalized. It drops whitespace, collapses duplicate keys (the last wins) and does not preserve key order. If you must keep the exact original text, such as a payload whose signature you verify, use json or text for that copy.

Numbers are numeric. A jsonb number keeps its exact decimal value, which is good for money, but converting it to float in application code can lose precision.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which data is the best candidate for a plain column instead of a JSONB key?

    Values that every row has and that you filter or join on need a type, a foreign key and an index. A key inside a document cannot be a foreign key and gets no statistics.
  2. 2. What does a CHECK constraint such as CHECK (jsonb_typeof(attrs) = 'object') protect you from?

    A jsonb column already rejects invalid JSON on its own. The CHECK adds your own rule about the shape, so an array or a plain number is refused.
  3. 3. Why is updating one small key in a 100 kB jsonb document expensive?

    With MVCC every UPDATE creates a new row version, and the document is copied whole. Frequently changing values are better as columns.
  4. 4. In PostgreSQL, which function turns a jsonb array of objects into rows with typed columns?

    jsonb_to_recordset takes a jsonb array and a column definition list and returns one typed row per element. SQLite's json_each plays the same role in the live examples.

Summary

  • Use columns for common, queried values; use jsonb for varying shapes, raw payloads and sparse settings.
  • A hybrid schema (real columns plus one jsonb column) fits most products.
  • Add CHECK constraints so documents have the shape your code expects.
  • Expand arrays of objects with jsonb_to_recordset (SQLite: json_each), then use ordinary SQL.
  • Promote hot keys to generated columns or expression indexes.
  • Updates rewrite the whole value, large documents are TOASTed, and the planner has no per-key statistics.

Next: Range and Multirange Types, where one column holds an interval of values.