Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 90 ยท Data Types

JSON and JSONB

Learn the difference between json and jsonb in PostgreSQL, the -> ->> #>> and @> operators, jsonb_build_object, jsonb_set and a first look at jsonpath.

Sometimes data does not fit neatly into columns: settings that differ per customer, events with varying fields, responses copied from an outside API. PostgreSQL stores such documents natively with two types, json and jsonb, and offers a large set of operators and functions to read, search and change them. It is one of the main reasons people choose PostgreSQL over simpler databases.

This lesson covers the two types, the core operators, building and modifying documents, containment, and a first look at jsonpath. The PostgreSQL operators are shown in read-only blocks with the real results. The live examples use SQLite's JSON functions, which are close analogues: json_extract, ->, ->>, json_set, json_object and json_each. See Working with JSON for the full SQLite tour.

json versus jsonb

json Keeps your exact text, whitespace and key order.
jsonb Parsed into a binary form. Faster to query.
Normalized jsonb drops duplicate keys and extra spaces.
Indexable Only jsonb supports GIN indexes.

Use jsonb unless you must preserve the original text.

1 / 2

json versus jsonb

json stores the exact text you gave it and re-parses it every time you read a field. jsonb ("binary") parses the text once on input and stores a decomposed form. That makes writes slightly slower and reads much faster, and it enables indexes.

SQL
-- Read-only: PostgreSQL
SELECT '{"b": 1,   "a": 2, "a": 3}'::json;
-- {"b": 1,   "a": 2, "a": 3}       (stored exactly as typed)

SELECT '{"b": 1,   "a": 2, "a": 3}'::jsonb;
-- {"a": 3, "b": 1}                 (whitespace gone, duplicate key: last wins, keys reordered)

With jsonb, key order is not preserved, duplicate keys keep only the last value, and numbers are stored as numeric. Choose json only when the exact original text matters, for example when you must echo back a signed payload byte for byte. For everything else, use jsonb.

SQL
-- Read-only: PostgreSQL
CREATE TABLE events (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT now(),
  payload    jsonb NOT NULL
);

INSERT INTO events (payload) VALUES
  ('{"type": "signup", "user": {"name": "Amina", "age": 34}, "tags": ["new", "mobile"]}'),
  ('{"type": "purchase", "user": {"name": "Ben", "age": 41}, "amount": 59.90}');

Invalid JSON is rejected at insert time, so a jsonb column is always valid.

Reading values

The two workhorse operators differ only in the type they return:

Operator Returns Example
-> jsonb payload -> 'user' gives {"age": 34, "name": "Amina"}
->> text payload ->> 'type' gives signup
#> jsonb at a path payload #> '{user,name}' gives "Amina"
#>> text at a path payload #>> '{user,name}' gives Amina

With a number instead of a key, -> reads an array element (0-based, unlike PostgreSQL arrays): payload -> 'tags' -> 0 returns "new", and a negative index counts from the end.

SQL
-- Read-only: PostgreSQL
SELECT payload ->> 'type'                 AS type,
       payload #>> '{user,name}'          AS name,
       (payload #>> '{user,age}')::int    AS age,
       payload -> 'tags' ->> 0            AS first_tag
FROM events;
-- signup   | Amina | 34 | new
-- purchase | Ben   | 41 | (null)

Missing keys return NULL instead of an error. ->> always gives text, so cast it with ::int or ::numeric before comparing numbers. Since PostgreSQL 14 you can also use subscript syntax: payload['user']['name'] returns the jsonb value.

Here is the same query in SQLite, which has json_extract() and ->/->> operators with a different path syntax ($.user.name, $.tags[0]):

SQL Reading fields from a JSON document

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

SQLite's -> returns JSON text (with quotes around strings) and ->> returns an SQL value, much like PostgreSQL. A missing path gives NULL in both.

Building documents

PostgreSQL creates JSON from SQL values with a few functions:

SQL
-- Read-only: PostgreSQL
SELECT jsonb_build_object('name', 'Amina', 'age', 34);
-- {"age": 34, "name": "Amina"}

SELECT jsonb_build_array(1, 'two', true);
-- [1, "two", true]

SELECT to_jsonb(u) FROM users u WHERE id = 1;       -- an entire row as an object
SELECT jsonb_agg(title ORDER BY title) FROM posts;   -- many rows into one array

to_jsonb(row) is a quick way to return a row as a document, and jsonb_agg() is the aggregate that turns a group of rows into a JSON array. SQLite's counterparts are json_object() and json_group_array().

Modifying documents

jsonb values are immutable, so "changing" one means building a new value and storing it:

Task Operator or function
Merge or add keys doc || '{"plan": "pro"}'::jsonb
Replace a nested value jsonb_set(doc, '{user,age}', '35')
Delete a key doc - 'tags'
Delete a nested path doc #- '{user,age}'
SQL
-- Read-only: PostgreSQL
SELECT jsonb_set('{"plan": "free", "seats": 1}'::jsonb, '{seats}', '5');
-- {"plan": "free", "seats": 5}

SELECT '{"plan": "free", "seats": 1}'::jsonb - 'seats';
-- {"plan": "free"}

UPDATE events
SET payload = jsonb_set(payload, '{user,age}', to_jsonb(35))
WHERE id = 1;

The third argument of jsonb_set must be valid JSON: use '5' for a number and '"pro"' (with inner double quotes) for a string, or wrap a value with to_jsonb(). If the path does not exist, jsonb_set adds the last key by default (its fourth argument, create_missing, defaults to true).

SQLite has the same family under different names:

SQL Building and changing JSON

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

Unlike jsonb, SQLite keeps JSON as text, so its result keeps your key order and has no spaces after commas.

Containment and key tests

The @> operator is the characteristic jsonb feature. a @> b is true when a contains everything in b: every key and value of b appears in a, recursively, and array elements of b all appear in the corresponding array in a.

SQL
-- Read-only: PostgreSQL
SELECT '{"type": "signup", "tags": ["new", "mobile"]}'::jsonb @> '{"type": "signup"}';        -- true
SELECT '{"type": "signup", "tags": ["new", "mobile"]}'::jsonb @> '{"tags": ["mobile"]}';      -- true
SELECT '{"type": "signup"}'::jsonb @> '{"type": "purchase"}';                                 -- false

SELECT payload ? 'amount' FROM events;          -- does the top-level key exist?
SELECT payload ?| array['amount','coupon'];     -- any of these keys?
SELECT payload ?& array['type','user'];         -- all of these keys?

SELECT id FROM events WHERE payload @> '{"type": "purchase"}';

A GIN index on the column makes @> and the ? operators fast on large tables (GIN and GiST Indexes). Containment is cheaper to index than a chain of ->> comparisons, so prefer it for filters on equality.

SQLite has no containment operator, but json_each() expands an object into one row per key, which lets you test a key and value pair:

SQL Expanding an object and testing key and value

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

A first look at jsonpath

PostgreSQL 12 added SQL/JSON path, a small query language that starts with $ for the whole document. It can filter and navigate in one expression:

SQL
-- Read-only: PostgreSQL
SELECT jsonb_path_query('{"tags": ["new", "mobile"]}', '$.tags[*]');
-- "new"
-- "mobile"

SELECT payload @? '$.user ? (@.age > 40)'  FROM events;   -- does a match exist?
SELECT payload @@ '$.user.age > 40'        FROM events;   -- does the predicate hold?

@? asks whether the path produces any item, and @@ evaluates a yes-or-no predicate. PostgreSQL 17 also added the standard JSON_TABLE, JSON_QUERY, JSON_VALUE and JSON_EXISTS functions, which turn JSON into rows and columns. JSONB in Practice shows how to use these in real designs.

jsonb is not a replacement for proper columns. Values inside a document have no data types, foreign keys or constraints unless you add checks yourself, and the planner knows little about their statistics. Keep fields you filter, join or constrain on as real columns.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the main difference between json and jsonb in PostgreSQL?

    jsonb parses once on input and supports GIN indexes, but normalizes the document: whitespace is dropped, duplicate keys keep the last value, and key order is not preserved. json stores the exact text.
  2. 2. What does payload ->> 'type' return?

    ->> returns the value as plain text, while -> returns jsonb. Cast the text, for example ::int, before doing numeric comparisons.
  3. 3. Which expression adds or replaces a nested value without touching the rest of the document?

    jsonb_set builds a new document with the value at the given path replaced. The minus operator deletes a key, ->> reads, and @> only tests containment.
  4. 4. Which query finds events whose payload contains "type": "purchase" and can use a GIN index?

    The containment operator @> is what GIN indexes on jsonb accelerate. The other forms either do not compare the right thing or cannot use the index.

Summary

  • Use jsonb unless you must keep the exact text; it normalizes the document and supports GIN indexes.
  • -> and #> return jsonb; ->> and #>> return text, and missing keys give NULL.
  • Build with jsonb_build_object, jsonb_agg and to_jsonb; change with ||, jsonb_set, - and #-.
  • @> tests containment, and ?, ?|, ?& test for keys; a GIN index speeds them up.
  • jsonpath (@?, @@, jsonb_path_query) filters inside documents, and PostgreSQL 17 adds JSON_TABLE.
  • SQLite has json_extract, ->, ->>, json_set, json_object and json_each, which were used here as live analogues.

Next: JSONB in Practice.