skip to content

JSONB Documents

How PostgreSQL stores and queries JSON: jsonb's parsed binary form, containment and path operators, jsonpath and 17's SQL/JSON functions. Interviewers probe when JSONB should be columns.

part ofPostgreSQLread it →
on this pageshow

explore

questions

6

In PostgreSQL, what is the difference between the json and jsonb column types, and which one should a new column normally use?

level: juniorimportance: must knowfreq 78%

answer

  1. exact text versus parsed binary
  2. reparse on every access
  3. whitespace, key order, duplicate keys
  4. operators and equality on one only
  5. last duplicate key wins

basics

~20 s

json stores the exact input text and reparses it on every access; jsonb stores a parsed binary form that drops whitespace, key order and duplicate keys but is faster to query, comparable and indexable. New columns should normally use jsonb.

solid answer

~40 s

Both types reject malformed JSON on input. `json` keeps an exact copy of the text, so whitespace, key order and duplicate keys survive, and every function call parses it again. `jsonb` parses once into a binary structure: whitespace is dropped, keys are stored in jsonb's own order (shorter keys first), and for a duplicated key only the last value is kept. Numbers become `numeric`, so out-of-range numbers are rejected and `1.230e-5` prints as `0.00001230`. Only `jsonb` has the containment and existence operators (`@>`, `?`), the jsonpath match operators, an equality operator and index support; `json` has no `=` at all, so `SELECT DISTINCT` or `GROUP BY` on it fails. Use `jsonb` unless the original text must be kept byte for byte, such as a raw webhook body kept for audit.

code

sql · 9 lines
sql
CREATE TABLE webhook_log (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  received_at timestamptz NOT NULL DEFAULT now(),
  raw_body json NOT NULL,      -- exact text, kept for signature checks
  body jsonb NOT NULL          -- parsed copy for queries
);

SELECT DISTINCT body FROM webhook_log;      -- works
SELECT DISTINCT raw_body FROM webhook_log;  -- ERROR: could not identify an equality operator for type json

go deeper

for a junior

Recall that json keeps the text exactly and jsonb stores a parsed form, and that jsonb is the default choice. Name one detail jsonb drops, such as key order.

for a middle

Explain the concrete consequences: the last duplicate key wins, numbers are stored as numeric, nothing is reparsed, and json lacks an equality operator, so DISTINCT and GROUP BY fail on it.

for a senior

Show when the raw text is the record, such as signed webhook bodies, how you keep a verbatim copy beside a jsonb working copy, and what converting a live column costs.

for a principal

Frame the choice as where work is paid: jsonb normalises at write time so every reader gets cheap, comparable values, while json defers parsing to every read.

## Two JSON types, one validation step PostgreSQL has two data types for JSON documents. Both check on input that the value is valid JSON, so a malformed document is rejected by either one. The difference is **what is stored**: - **`json`** stores an exact copy of the input text. Every function or operator that reads it must parse that text again. - **`jsonb`** parses the text once, on input, and stores a decomposed **binary** structure. Input is slightly slower because of the conversion; every later read is faster because nothing has to be reparsed. The manual's advice is direct: most applications should store JSON as `jsonb` unless they have specialised needs, such as legacy assumptions about the order of object keys. ## What jsonb does not keep Because `jsonb` stores a parsed structure rather than text, it discards details that JSON treats as insignificant: | Input detail | `json` | `jsonb` | |---|---|---| | Whitespace between tokens | kept | dropped | | Order of keys in an object | kept | not kept; keys are stored in jsonb's own order, shorter keys first | | Duplicate keys | all kept; processing functions use the last | only the last value is kept | | Number spelling | kept as typed | stored as `numeric`; `1.230e-5` prints as `0.00001230`, trailing zeros kept | | Numbers outside `numeric`'s range | accepted | rejected | | Unicode escape for the NUL character | accepted (syntax check only) | rejected | For a survey-builder that stores each submission's answers, none of these losses matters: the application reads answers by key name, never by position, and a respondent's whitespace carries no meaning. ## What jsonb gains - **Containment and existence operators.** `@>`, `<@`, `?`, `?|` and `?&` exist only for `jsonb`; the manual notes there is no parallel set of facilities for `json`. - **jsonpath match operators.** `@?` and `@@` take a `jsonb` left operand. - **Modification functions and operators.** `jsonb_set`, `jsonb_insert`, `||`, `-` and `#-` work on `jsonb`. - **Equality and ordering.** `jsonb` has `=`, `<>` and a B-tree ordering, so `SELECT DISTINCT`, `GROUP BY`, `UNION` and unique indexes work. `json` has no equality operator: `SELECT DISTINCT payload` on a `json` column fails with `could not identify an equality operator for type json`. - **Index support.** `jsonb` columns can carry GIN indexes as well as B-tree and hash indexes; choosing a GIN operator class is an indexing topic of its own. - **Faster processing.** Extracting a key from `jsonb` walks a binary structure; from `json` it reparses the whole text. Both types share the extraction operators `->`, `->>`, `#>` and `#>>`, and each casts to the other. ## Choosing for a new column 1. Default to `jsonb` for anything you will query, filter, index, compare or update. 2. Choose `json` only when the stored text itself is the record: a raw webhook body kept byte for byte for audit or signature verification, where key order and duplicate keys must survive. 3. When you need both, keep the raw text in a `json` (or `text`) column and a parsed `jsonb` copy beside it, or parse at read time if the raw copy is rarely queried. Converting an existing column with `ALTER TABLE ... ALTER COLUMN payload TYPE jsonb USING payload::jsonb` parses every row and rewrites the table; a single row with an out-of-range number fails the whole statement, and duplicate keys and key order are gone afterwards. ## Traps candidates fall into - Believing `jsonb` keeps the first of two duplicate keys: it keeps the **last**, and the `json` processing functions also treat the last as operative. - Believing `json` skips validation: both types reject malformed input. - Comparing documents as text: two `json` values that differ only in whitespace are different text, while their `jsonb` forms compare equal. - Expecting `jsonb` to preserve the key order a client sent: if order matters, model it as an array. ```sql SELECT '{"b": 1, "a": 2, "a": 3}'::json AS as_json, '{"b": 1, "a": 2, "a": 3}'::jsonb AS as_jsonb; -- as_json : {"b": 1, "a": 2, "a": 3} -- as_jsonb: {"a": 3, "b": 1} ```

  • Why does '{"a": 1}'::jsonb = '{ "a" : 1 }'::jsonb return true, while the same comparison on json fails?
    `jsonb` stores the parsed structure, so whitespace is gone and both sides are the same value; `jsonb` has an `=` operator that compares structures. `json` has no equality operator at all, so `json = json` fails with `operator does not exist`. Cast to `jsonb` first if you must compare `json` values.
  • What can go wrong when you convert a large json column to jsonb in place?
    `ALTER COLUMN ... TYPE jsonb USING payload::jsonb` parses every row and rewrites the table and its indexes, holding a lock that blocks other access for the duration. One row with a number outside `numeric`'s range, or a NUL-character escape, aborts the whole statement. Afterwards duplicate keys and the original key order are gone, so keep the raw text elsewhere if anything depends on it.

json is a scanned photocopy of a paper survey: perfectly faithful, but anyone who needs one answer must read the whole page again. jsonb is the clerk's transcription into labelled fields: the handwriting and the order the boxes were filled in are gone, but any answer can be looked up at once.

  • json skips validation, so it accepts malformed documents
  • jsonb keeps the first value when a key is duplicated
  • jsonb preserves the key order the client sent
  • The @> containment operator works on json columns too
  • json is faster to query because it is stored as plain text
open as a page

In PostgreSQL, what do the jsonb operators ->, ->> and @> return, and when should a WHERE clause use @> instead of ->>?

level: middleimportance: must knowfreq 68%

basics

~20 s

The -> operator returns jsonb, ->> returns text, and @> returns a boolean saying whether the left document contains the right one's keys, values and structure. Use @> for type-aware matches on nested values or array members; ->> to read one scalar.

open as a page

In PostgreSQL, how do jsonpath queries through jsonb_path_query, @? and @@ work, and what changes between lax and strict mode?

level: middleimportance: should knowfreq 35%

basics

~20 s

A jsonpath such as $.ratings[*] ? (@.score < 3) selects items from a jsonb document: jsonb_path_query returns them as rows, @? tests whether any exist, and @@ evaluates a predicate. Lax mode, the default, unwraps arrays and hides structural errors; strict raises them.

open as a page

In PostgreSQL, how do jsonb_set, ||, - and jsonb_insert change part of a jsonb document, and what happens when jsonb_set gets SQL NULL?

level: middleimportance: should knowfreq 45%

basics

~20 s

Each returns a new document: jsonb_set replaces or adds the value at a path, || merges top-level keys, - removes keys or elements, and jsonb_insert inserts. jsonb_set is STRICT, so a SQL NULL new value makes the whole result NULL.

open as a page

In a PostgreSQL survey-builder, reports filter and sort on a few jsonb answer keys; what signs say a key should become a real column, and how do you promote it?

level: seniorimportance: should knowfreq 55%

basics

~20 s

Promote a key that nearly every row has and that queries filter, join, sort or constrain on: the planner keeps no per-key statistics, jsonb enforces no type, NOT NULL or foreign key, and every change rewrites the whole document.

open as a page

A PostgreSQL 18 survey report averages JSON_VALUE(answers, '$.nps' RETURNING int) and the number looks wrong; what is JSON_VALUE doing with bad answers, and how do you expose them?

level: seniorimportance: nice to knowfreq 22%

basics

~20 s

JSON_VALUE defaults to NULL ON EMPTY and NULL ON ERROR, so a missing nps, a JSON null and an unconvertible value like "ten" all become SQL NULL, which AVG silently skips. Add ERROR ON EMPTY and ERROR ON ERROR, or count the NULLs.

open as a page