SQL JSON — Basics of Partial Updates, Building, Type Checks

QUESTION 6

jsonb_set and concatenation — Rewrite only a part of a JSON document

jsonb_setConcatenation operatorPartial update

Background ▼

To replace only a part of a JSON document, use jsonb_set(target, path, new_value). To add or overwrite a key at the top level, the concatenation operator || is more concise. Both merely return a new jsonb; neither rewrites the original value.

SELECT jsonb_set(jsonb_col, '{parent, child}', 'false'::jsonb)  AS replaced,  -- replace the given path
       jsonb_col || '{"key": "value"}'                       AS merged     -- on a clash the right side wins
FROM   table_name;

Nothing happens when the parent path is missing: jsonb_set can only create the last step of the path. On a row where notify in '{notify, email}' does not itself exist, there is no error and no warning — the target comes back unchanged.

Problem ▼

For every row of the user_settings table, produce the result of changing notify.email to false and then setting "theme": "dark" at the top level. Return user_id, settings, sorted by user_id ascending.

Tables used

▸ user_settings

user_id	settings (jsonb)
1	{"theme": "light", "notify": {"push": false, "email": true}}
2	{"notify": {"push": true, "email": true}}
3	{"theme": "light"}

Expected Output ▼

user_id	settings
1	{"theme": "dark", "notify": {"push": false, "email": false}}
2	{"theme": "dark", "notify": {"push": true, "email": false}}
3	{"theme": "dark"}

Model Answer ▼

SELECT
  user_id,
  jsonb_set(settings, '{notify, email}', 'false'::jsonb)  -- only rows that have notify change
    || '{"theme": "dark"}' AS settings                    -- top level: overwrite or add
FROM   user_settings
ORDER BY user_id;

/*
  Execution order (logical evaluation order):
  1. FROM user_settings           → Read 3 rows
  2. SELECT jsonb_set(...)        → Replace notify.email with false
  3. SELECT ... || '{"theme"...}' → Overwrite or add theme
  4. ORDER BY user_id             → Ascending by user_id
  */

Explanation (table transitions & key points) ▼

SELECT
  user_id,
  jsonb_set(settings, '{notify, email}', 'false'::jsonb)
    || '{"theme": "dark"}' AS settings
FROM   user_settings
ORDER BY user_id;

LEGEND ▼

Rows read / loaded

① FROM user_settings ▼

FROM user_settingsRead all 3 rows of user_settings. User 2 has no theme and user 3 has no notify. Because the column is jsonb, the keys are held reordered by key length and then byte order.

1 / 4

user_id	settings (jsonb)
1	{"theme": "light", "notify": {"push": false, "email": true}}
2	{"notify": {"push": true, "email": true}}
3	{"theme": "light"}

All 3 rows read

LEARNING POINTS

Which one to reach for: use jsonb_set to change a single point deep in the nesting, and || to add or set a top-level key. || is a shallow merge: a clashing key is replaced whole rather than blended.

Deleting a key is an operator: removal is written as settings - 'theme' (top level) or settings #- '{notify, push}' (by path). That it is an operator rather than a function makes it asymmetric with adding and updating.

Display order is key length, then bytes: the output of jsonb is not in insertion order. theme appears before notify because its key is shorter, which says nothing about the meaning of the values.

ANTI-PATTERNS

Rewriting without checking the parent: a row missing an intermediate key, like user 3, is silently not updated. An UPDATE still reports 3 rows affected, so the gap goes unnoticed until you aggregate. Name the target explicitly with WHERE settings ? 'notify' when it matters.

Read, modify, write back: fetching the whole document in the application, rebuilding it and issuing an UPDATE loses one of two concurrent updates. jsonb_set completes in a single server-side statement, so that race does not arise.

Field Notes: a partial JSON update rewrites the whole row

Although the SQL looks like a one-key update, PostgreSQL creates an entire new version of the row. A table where a few hundred kilobytes of JSON are partially updated many times a day accumulates write volume and TOAST traffic until VACUUM can no longer keep up. Promoting the frequently updated fields to ordinary columns, and leaving "incidental information that rarely changes" in the JSON, is the design that pays off later.

QUESTION 7

jsonb_build_object and jsonb_agg — Build JSON out of rows

jsonb_build_objectjsonb_aggShaping for an API

Background ▼

To turn a row into JSON, use jsonb_build_object(key, value, key, value, …). The arguments alternate between key and value, and an odd number of them is an error. To collect the values of a group into a single JSON array, use the aggregate function jsonb_agg.

SELECT jsonb_agg(
         jsonb_build_object('key1', col1, 'key2', col2)
         ORDER BY sort_col                              -- pin down the array order
       ) AS items
FROM     table_name
GROUP BY group_col;

To take a whole row as it is, use to_jsonb: to_jsonb(t) turns one row into JSON without picking columns again. Added columns are reflected automatically, but internal columns are exposed too, so a shape returned to the outside world is safer written out with jsonb_build_object.

Problem ▼

Group the sales table by region and turn the product and amount pairs into a JSON array. Each element takes the form {"product": …, "amount": …}, and the elements are ordered by sale_id ascending. Return region, items, sorted by region ascending.

Tables used

▸ sales

sale_id	region	product	amount
1	East	Mouse	1500
2	East	Keyboard	3000
3	West	Monitor	20000

Expected Output ▼

region	items
East	[{"amount": 1500, "product": "Mouse"}, {"amount": 3000, "product": "Keyboard"}]
West	[{"amount": 20000, "product": "Monitor"}]

Model Answer ▼

SELECT
  region,
  jsonb_agg(
    jsonb_build_object('product', product, 'amount', amount)
    ORDER BY sale_id                                    -- make the array order unique
  ) AS items
FROM     sales
GROUP BY region
ORDER BY region;

/*
  Execution order (logical evaluation order):
  1. FROM sales                    → Read 3 rows
  2. GROUP BY region               → Split into 2 groups
  3. SELECT jsonb_build_object     → Build one JSON object per row
  4. SELECT jsonb_agg(... ORDER BY sale_id) → Aggregate into an array per group
  5. ORDER BY region               → Ascending by region
  */

Explanation (table transitions & key points) ▼

SELECT
  region,
  jsonb_agg(
    jsonb_build_object('product', product, 'amount', amount)
    ORDER BY sale_id
  ) AS items
FROM     sales
GROUP BY region
ORDER BY region;

LEGEND ▼

Rows read / loaded

① FROM sales ▼

FROM salesRead all 3 rows of sales. Two rows belong to East and one to West.

1 / 4

sale_id	region	product	amount
1	East	Mouse	1500
2	East	Keyboard	3000
3	West	Monitor	20000

All 3 rows read

LEARNING POINTS

ORDER BY inside an aggregate: jsonb_agg(expr ORDER BY sort_col) is syntax reserved for aggregate functions and decides the order within the array. Leave it out and no order is guaranteed — the array reorders itself the day the plan changes.

Keys do not stay in the order you wrote them: amount comes first in the output because jsonb sorts keys by length and then by bytes. Use json_build_object (json, not jsonb) only when you must preserve the order.

Nesting uses the same functions: placing another jsonb_build_object or jsonb_agg in a value position assembles a nested response in a single query. The loop that builds it in the application can move into SQL.

ANTI-PATTERNS

Building JSON by string concatenation: '{"product": "' || product || '"}' emits broken JSON the moment a value contains a quote or a newline. Leave the escaping to the builder functions.

Returning NULL for an empty group: jsonb_agg returns NULL for a group with no matching rows. If the consumer expects an empty array, make COALESCE(jsonb_agg(…), '[]'::jsonb) your default shape.

Field Notes: where should JSON be assembled?

Assembling the whole response in one query removes both the N+1 queries and the marshalling code in the application. In exchange the SQL grows longer, and a change to the response spec becomes a change to the SQL. As a rule of thumb: a read-only API whose fetch shape is already the output shape belongs in SQL; something that mixes several sources, or changes shape by condition, belongs in the application. Generating the JSON costs the same either way, so choose by which is easier to change.

QUESTION 8

jsonb_each — Open the keys and values of an object into rows

jsonb_eachLATERALPivot to long form

Background ▼

For an object whose key names are not known in advance, jsonb_each extracts the key-value pairs as rows. It is a set-returning function that returns the two columns key and value, and it is used in the FROM clause.

SELECT kv.key, kv.value
FROM   table_name t
CROSS JOIN LATERAL jsonb_each_text(t.jsonb_col) AS kv;
-- jsonb_each      : value is jsonb (strings keep their quotes)
-- jsonb_each_text : value is text (no quotes)
-- jsonb_object_keys : when you only want the key names

Only the top level is opened: a nested object is not expanded and comes back as the value itself. To open a deeper level too, apply jsonb_each once more to the extracted value.

Problem ▼

Expand params of the configs table into one row per key. Return config_id, key, value, sorted by config_id ascending and then key ascending. Return value as text without quotes.

Tables used

▸ configs

config_id	params (jsonb)
1	{"debug": "true", "limit": "100"}
2	{"limit": "50"}

Expected Output ▼

config_id	key	value
1	debug	true
1	limit	100
2	limit	50

Model Answer ▼

SELECT
  c.config_id,
  kv.key,
  kv.value
FROM   configs c
CROSS JOIN LATERAL jsonb_each_text(c.params) AS kv  -- receive value as text
ORDER BY c.config_id, kv.key;

/*
  Execution order (logical evaluation order):
  1. FROM configs c                     → Read 2 rows
  2. CROSS JOIN LATERAL jsonb_each_text → Expand per key into 3 rows
  3. SELECT config_id, key, value       → Select 3 columns
  4. ORDER BY c.config_id, kv.key       → config_id ascending, key ascending
  */

Explanation (table transitions & key points) ▼

SELECT
  c.config_id,
  kv.key,
  kv.value
FROM   configs c
CROSS JOIN LATERAL jsonb_each_text(c.params) AS kv
ORDER BY c.config_id, kv.key;

LEGEND ▼

Rows read / loaded

① FROM configs ▼

FROM configs cRead all 2 rows of configs. Config 1 holds two keys and config 2 holds one. The key names appear nowhere in the table definition.

1 / 3

config_id	params (jsonb)
1	{"debug": "true", "limit": "100"}
2	{"limit": "50"}

All 2 rows read

LEARNING POINTS

You do not need to know the key names: ->> requires you to write the key, while jsonb_each pulls out every key without naming any. It suits taking stock of data whose fields come and go, such as settings.

The _text variant drops the quotes: the value of jsonb_each is jsonb, so a string appears quoted as "true". Choose jsonb_each_text when you display or compare the value directly.

State the ordering: the row order right after the expansion is not guaranteed. If the output order is part of the requirement, always name the key and the parent identifier in ORDER BY.

ANTI-PATTERNS

Expanding first and then hunting for one key: opening every key with jsonb_each and narrowing with WHERE key = 'limit' pays a high price for the same result as params ->> 'limit'. Extract directly when you know the key name.

Expecting nesting to open: a key whose value is an object is not expanded; the object lands in value as it is. With jsonb_each_text it comes back as JSON text, which then needs parsing of its own.

Field Notes: beware of reinventing EAV

Holding keys and values in long form lets you express anything by adding rows, at the price of types, constraints and foreign keys. If the reason for JSON is that the fields are not decided in advance, that is reasonable; if it is only that you do not want to change the table definition, what remains months later is a huge untyped settings table. jsonb_each is healthiest as a tool for investigating such data — making it part of a daily read path is a signal to promote the fields to columns.

QUESTION 9

jsonb_typeof — Tell JSON null, a missing key and a NULL column apart

jsonb_typeofNULLData quality

Background ▼

With JSON, "there is no value" splits into three states: the value is JSON null, the key itself is absent, and the column itself is SQL NULL. ->> returns SQL NULL for all three, so use jsonb_typeof to tell them apart.

SELECT jsonb_typeof(jsonb_col -> 'key') AS json_type
FROM   table_name;
-- 'string' / 'number' / 'boolean' / 'object' / 'array' : a value is there
-- 'null'  : the value is JSON null
-- SQL NULL: the key is absent, or the column is NULL

How this differs from ? : jsonb_col ? 'key' only looks at whether the key is there, so it is true even when the value is JSON null. Which test you want depends on whether "the key exists but its value is null" should pass or be rejected.

Problem ▼

For every row of the profiles table, line up the value of nickname and its type in JSON terms. Return profile_id, nickname, json_type, sorted by profile_id ascending. Do not filter any rows.

Tables used

▸ profiles

profile_id	data (jsonb)
1	{"nickname": "Taro"}
2	{"nickname": null}
3	{"age": 20}
4	NULL
5	{"nickname": "Hanako"}

Expected Output ▼

profile_id	nickname	json_type
1	Taro	string
2	NULL	null
3	NULL	NULL
4	NULL	NULL
5	Hanako	string

Model Answer ▼

SELECT
  profile_id,
  data ->> 'nickname'                  AS nickname,   -- SQL NULL in all 3 states
  jsonb_typeof(data -> 'nickname')     AS json_type   -- only JSON null gives the text 'null'
FROM   profiles
ORDER BY profile_id;

/*
  Execution order (logical evaluation order):
  1. FROM profiles                → Read 5 rows
  2. SELECT ->> extracts a value  → All 3 states become SQL NULL
  3. SELECT jsonb_typeof types it → Separates JSON null from the missing cases
  4. ORDER BY profile_id          → Ascending by profile_id
  */

Explanation (table transitions & key points) ▼

SELECT
  profile_id,
  data ->> 'nickname'              AS nickname,
  jsonb_typeof(data -> 'nickname') AS json_type
FROM   profiles
ORDER BY profile_id;

LEGEND ▼

Rows read / loaded

① FROM profiles ▼

FROM profilesRead all 5 rows of profiles. Profile 2 holds the JSON value null, profile 3 has no nickname key at all, and profile 4 has a SQL NULL in the data column.

1 / 3

profile_id	data (jsonb)
1	{"nickname": "Taro"}
2	{"nickname": null}
3	{"age": 20}
4	NULL
5	{"nickname": "Hanako"}

All 5 rows read

LEARNING POINTS

There are three kinds of NULL: JSON null, a missing key, and a SQL NULL column. Judging by the result of ->> alone they look identical, but they mean "explicitly unset", "the field is not there" and "the record itself is missing".

'null' is a string: what jsonb_typeof returns is the type name as text. Test it with the string comparison jsonb_typeof(…) = 'null' and do not confuse it with IS NULL.

The standard shape for "a value is present": to keep only the rows that really hold a string, write WHERE jsonb_typeof(data -> 'nickname') = 'string'. In this example profiles 1 and 5 survive.

ANTI-PATTERNS

Looking for JSON null with IS NULL: data -> 'nickname' IS NULL hits only profiles 3 and 4 — profile 2, whose value is JSON null, is not matched. That is because data -> 'nickname' is the jsonb value null, not SQL NULL.

Representing "unset" with an empty string: writing {"nickname": ""} from the application keeps the type as string and adds a fourth state. Decide up front that unset means either no key or JSON null, and keep to it.

Field Notes: constraints do not reach inside JSON

On an ordinary column a single NOT NULL keeps out rows with no value, but nothing of the sort reaches inside a JSON document. A row missing a required field is found months later during aggregation, not at insert time. There are two remedies: promote required fields to columns, or add a constraint such as CHECK (data ? 'nickname') to the table. If you take neither, at least run a stocktaking query with jsonb_typeof on a schedule, so the share of missing values stays visible.

QUESTION 10

Containment and GIN — Write a nested condition in an indexable form

Containment operatorGIN indexSearch performance

Background ▼

Whether an index can serve a JSON filter depends on how you write it. With a GIN index on a jsonb column, the operators @>, ?, ?| and ?& are served by the index. An equality test through ->> is not accelerated by it.

CREATE INDEX ON table_name USING GIN (jsonb_col);

SELECT * FROM table_name
WHERE  jsonb_col @> '{"key": "value", "parent": {"child": "value2"}}';
-- write the structure you are looking for and one condition covers the nesting

Fold several conditions into one @>: because the right side of @> is judged as a subset of the whole object, adding conditions still leaves one operator. That is easier for the index to use than splitting them with AND.

Problem ▼

From the events table, retrieve the events whose type is purchase and whose user.plan is pro. Write the condition as a single containment operator so that a GIN index can be used. Return event_id, amount, sorted by event_id ascending. Return amount as a number.

Tables used

▸ events

event_id	payload (jsonb)
1	{"type": "purchase", "user": {"plan": "pro"}, "amount": 1200}
2	{"type": "purchase", "user": {"plan": "free"}, "amount": 500}
3	{"type": "login", "user": {"plan": "pro"}}
4	{"type": "purchase", "user": {"plan": "pro"}, "amount": 3000}

Expected Output ▼

event_id	amount
1	1200
4	3000

Model Answer ▼

SELECT
  event_id,
  (payload ->> 'amount')::int AS amount
FROM   events
WHERE  payload @> '{"type": "purchase", "user": {"plan": "pro"}}'  -- 2 conditions in one containment
ORDER BY event_id;

/*
  Execution order (logical evaluation order):
  1. FROM events                  → Read 4 rows (only candidate rows with a GIN index)
  2. WHERE payload @> '{...}'     → Narrow to the 2 rows holding the sub-structure
  3. SELECT (payload->>'amount')::int → Select 2 columns
  4. ORDER BY event_id            → Ascending by event_id
  */

Explanation (table transitions & key points) ▼

SELECT
  event_id,
  (payload ->> 'amount')::int AS amount
FROM   events
WHERE  payload @> '{"type": "purchase", "user": {"plan": "pro"}}'
ORDER BY event_id;

LEGEND ▼

Rows read / loaded

① FROM events ▼

FROM eventsRead all 4 rows of events. payload holds type, user and amount, where user is a nested object. Event 3 has no amount.

1 / 3

event_id	payload (jsonb)
1	{"type": "purchase", "user": {"plan": "pro"}, "amount": 1200}
2	{"type": "purchase", "user": {"plan": "free"}, "amount": 500}
3	{"type": "login", "user": {"plan": "pro"}}
4	{"type": "purchase", "user": {"plan": "pro"}, "amount": 3000}

All 4 rows read

LEARNING POINTS

The operator decides whether the index applies: GIN (jsonb_ops) handles only @> and the existence operators. payload ->> 'type' = 'purchase', which returns the same rows, becomes a full scan under that index.

Two operator classes: the default jsonb_ops indexes both keys and values, so the ? family works too. USING GIN (payload jsonb_path_ops) gives up everything but @> in exchange for a smaller, faster index. Choose the latter when containment is all you search on.

An expression index for a single key: if you always search the same key for equality, the B-tree expression index CREATE INDEX ON events ((payload ->> 'type')) is smaller and faster than GIN. Range searches work only with this one.

ANTI-PATTERNS

Splitting the condition with AND: payload @> '{"type": "purchase"}' AND payload @> '{"user": {"plan": "pro"}}' returns the same rows, but the index is scanned twice. Folding it into one object is more direct.

Filtering a numeric range with @>: @> only tests containment by exact match and cannot express a comparison such as amount > 1000. A range condition needs (payload ->> 'amount')::int together with an expression index.

Field Notes: what a GIN index costs

GIN expands the keys and values of a JSON document into index terms one by one, so it is not unusual for the index to approach the size of the table. Updates are heavy too: by default fastupdate defers the insert cost to a pending list, but response times jump when that list is cleaned up. Rather than "just put a GIN index on the JSON column", check the search conditions that actually run, then choose between jsonb_path_ops, an expression index and the default.