JSONB, Arrays & FTS — Series 2

Preview — 3 of 10 questions

How do you update just the city field inside a nested JSONB column, without replacing the whole document?

javascript

-- data column: {"name": "Alice", "address": {"city": "Paris", "zip": "75001"}}
UPDATE users SET data = jsonb_set(data, '{address,city}', '"Lyon"') WHERE id = 1;

Ajsonb_set(target, path, new_value) returns a new JSONB value with the value at the given path array replaced, leaving all other keys untouched — the original data column must still be reassigned via UPDATE ... SET, since jsonb_set doesn't mutate in place

Bjsonb_set() only works on top-level keys, never nested paths

Cjsonb_set() permanently modifies the JSONB value on disk without needing an UPDATE statement

DThe third argument to jsonb_set() must be a plain string without quotes, not a JSON-encoded value

What does arr[2:3] return for arr = ARRAY[10, 20, 30, 40]?

javascript

SELECT (ARRAY[10, 20, 30, 40])[2:3];
-- {20,30}   -- elements at positions 2 and 3

SELECT (ARRAY[10, 20, 30, 40])[1];
-- 10   -- position 1 is the FIRST element, not the second

A{10, 20} — PostgreSQL arrays are 0-indexed like most languages

B{20, 30} — a sub-array containing elements from index 2 to index 3, inclusive

C30 — a single scalar value at position 2+3

DAn error — array slicing syntax does not exist in PostgreSQL

What does ARRAY_AGG(tag) return for a group where all 3 rows have tag = NULL?

javascript

SELECT category, ARRAY_AGG(tag) FROM items GROUP BY category;
-- category = 'misc' has 3 rows, all with tag = NULL

ANULL (the aggregate itself returns NULL) — treating NULL tag values as if there were no rows to aggregate

B{} — an empty array literal

C{NULL,NULL,NULL} — unlike most aggregate functions (SUM, AVG, COUNT), ARRAY_AGG does not skip NULL input values — it includes them as elements in the resulting array

DAn error — ARRAY_AGG cannot be used on a column containing NULL values

Answer all 10 questions (7 more), see explanations for every answer, and track your score.