In this post we are going to cover JSONB and arrays in PostgreSQL: json against jsonb, the operators to read a document, GIN indexes, updating a document in SQL, arrays, and when a document column is the right design. These are 7 real PostgreSQL interview questions, asked the way they come up on the job.
Postgres can store a whole JSON document in one column and still query inside it. That makes it popular for events, API payloads and settings. It also makes it easy to misuse. The questions below are the ones that show whether you have really worked with JSONB: which operator returns what, which index helps which query, and what the planner knows about the inside of a document.
Every query below is complete, so you can copy it, run it on the sample data and get exactly the output shown under it.
Part 8 of the Real Time PostgreSQL Interview Questions and Answers series on TechBrothersIT.
- Let's create some sample data
- Question 1: A teammate created the payload column with the type json. In code review, someone says it should be jsonb. What is the difference, and which one do you choose?
- Question 2: You need the purchases with an amount over 100 from a JSONB payload column, and the operating system of the device, which is nested one level down. What is the difference between the arrow and the double arrow?
- Question 3: The events table has 100,000 rows. A support query filters on a value inside the JSONB payload, and it scans the whole table. You add a GIN index, and the query is still slow. Why?
- Question 4: The app needs to change the app version inside the device object of one event, add a reviewed flag, and remove the coupon key. A developer wants to read the document, change it in code, and write it back. Can SQL do it?
- Question 5: Articles store their tags in a text array column. Product wants the articles tagged postgres, the articles that have both postgres and index, and a count of articles per tag. How do you query an array?
- Question 6: An API stores each order as one JSON document, with an items array inside. Finance needs one row per item, with the SKU, quantity and price as real typed columns. How do you unpack the array?
- Question 7: The team wants to keep every attribute in one JSONB column, so they never need a migration again. Is that a good design? What do you say in the design review?
- Key points to remember
- The full SQL script
Let's create some sample data
This post uses a small events table with a JSONB payload column, a generated events_big table with 100,000 events for the index questions, an articles table with a text array of tags, and api_orders, where each order is one JSON document with an array of items. The script below creates everything this post needs. It was written for and run on PostgreSQL 16.
SET max_parallel_workers_per_gather = 0; -- keep the plans simple
CREATE TABLE events (event_id INT PRIMARY KEY, user_id INT, payload JSONB);
INSERT INTO events VALUES
(1, 1, '{"type": "purchase", "amount": 120, "device": {"os": "iOS", "app": "3.2"}, "tags": ["sale", "mobile"]}'),
(2, 2, '{"type": "login", "device": {"os": "Android", "app": "3.1"}, "tags": ["mobile"]}'),
(3, 1, '{"type": "purchase", "amount": 40, "device": {"os": "Windows"}, "tags": ["web"]}'),
(4, 3, '{"type": "refund", "amount": 40, "device": {"os": "iOS", "app": "3.2"}, "tags": []}'),
(5, 2, '{"type": "purchase", "amount": 300, "coupon": null, "device": {"os": "Android", "app": "3.0"}, "tags": ["sale", "vip", "mobile"]}'),
(6, 3, '{"type": "login", "device": {"os": "macOS"}}');
-- 100,000 generated events: 1% refunds
CREATE TABLE events_big AS
SELECT g AS event_id, g % 2000 AS user_id,
jsonb_build_object(
'type', CASE WHEN g % 100 = 0 THEN 'refund' WHEN g % 4 = 0 THEN 'purchase' ELSE 'login' END,
'amount', g % 500,
'device', jsonb_build_object('os', (ARRAY['iOS', 'Android', 'Windows', 'macOS'])[1 + g % 4])
) AS payload
FROM generate_series(1, 100000) AS g;
ALTER TABLE events_big ADD PRIMARY KEY (event_id);
VACUUM ANALYZE events_big;
CREATE TABLE articles (article_id INT PRIMARY KEY, title TEXT, tags TEXT[]);
INSERT INTO articles VALUES
(1, 'Index basics', ARRAY['postgres', 'index']),
(2, 'JSONB in practice', ARRAY['postgres', 'jsonb', 'index']),
(3, 'Spark partitioning', ARRAY['spark']),
(4, 'Untagged draft', '{}');
CREATE TABLE api_orders (order_id INT PRIMARY KEY, doc JSONB);
INSERT INTO api_orders VALUES
(9001, '{"customer": "Sara", "items": [{"sku": "LAPTOP", "qty": 1, "price": 1200}, {"sku": "KEYBOARD", "qty": 2, "price": 50}]}'),
(9002, '{"customer": "Omar", "items": [{"sku": "MONITOR", "qty": 1, "price": 300}]}');Question 1: A teammate created the payload column with the type json. In code review, someone says it should be jsonb. What is the difference, and which one do you choose?
Choose jsonb almost always. json stores the exact text you sent and parses it again on every read. jsonb stores a parsed binary form: slightly slower to insert, much faster to query, and it supports GIN indexes and operators like @>.
Send the same text to both types. json keeps it exactly as typed: the spaces, the order of the keys, even the duplicate key. jsonb parses it once on the way in. The spaces are gone, the keys are sorted, and for the duplicate key only the last value survives. That parsed form is what makes fast access and indexing possible.
Here is that exact test on Postgres. The json column returns the text unchanged. The jsonb column returns the cleaned document: a is 3, b is 1.
SELECT '{"b": 1, "a": 2, "a": 3}'::json AS as_json,
'{"b": 1, "a": 2, "a": 3}'::jsonb AS as_jsonb;| as_json | as_jsonb |
|---|---|
| {"b": 1, "a": 2, "a": 3} | {"a": 3, "b": 1} |
When is json still right? Only when you must give back the document exactly as it arrived, byte for byte, like an audit copy of an API request, and you never search inside it.
json: exact copy, never queried. jsonb: everything else.
Question 2: You need the purchases with an amount over 100 from a JSONB payload column, and the operating system of the device, which is nested one level down. What is the difference between the arrow and the double arrow?
The single arrow -> returns jsonb, so you can keep walking into the document. The double arrow ->> returns text, the final value. To compare a number, take it out as text and cast it.
Walk down the document of event 1. payload arrow device returns the inner object, still jsonb. From there, double arrow os returns the text iOS, without quotes. For the amount, double arrow gives the text 120, and a cast to integer makes it a number that we can compare.
payload -> 'device'jsonb: {"os": "iOS", "app": "3.2"}-> 'device' ->> 'os'text: iOSpayload ->> 'amount'text: 120(payload ->> 'amount')::intinteger: 120See the two operators side by side. With the single arrow, the type comes back in double quotes, because it is still a JSON value. With the double arrow, it is plain text. And for the login events, which have no amount, the result is simply NULL.
SELECT event_id,
payload -> 'type' AS single_arrow,
payload ->> 'type' AS double_arrow,
payload ->> 'amount' AS amount_text
FROM events
ORDER BY event_id;| event_id | single_arrow | double_arrow | amount_text |
|---|---|---|---|
| 1 | "purchase" | purchase | 120 |
| 2 | "login" | login | NULL |
| 3 | "purchase" | purchase | 40 |
| 4 | "refund" | refund | 40 |
| 5 | "purchase" | purchase | 300 |
| 6 | "login" | login | NULL |
Now the real question. Filter on the type as text, cast the amount to an integer, and read the nested OS. Two purchases are over 100.
SELECT event_id,
(payload ->> 'amount')::int AS amount,
payload -> 'device' ->> 'os' AS os
FROM events
WHERE payload ->> 'type' = 'purchase'
AND (payload ->> 'amount')::int > 100
ORDER BY event_id;| event_id | amount | os |
|---|---|---|
| 1 | 120 | iOS |
| 5 | 300 | Android |
A classic bug is comparing the text without the cast. As text, 40 is greater than 100, because 4 comes after 1. For a deep path, the hash arrow operators take the whole path as an array.
Deep path: payload #>> '{device,os}'. Always cast before comparing numbers.
Question 3: The events table has 100,000 rows. A support query filters on a value inside the JSONB payload, and it scans the whole table. You add a GIN index, and the query is still slow. Why?
A default GIN index on a jsonb column supports the containment operator @> and the key-exists operators. It does not support ->> with equals. Rewrite the filter as payload @> '{"type": "refund"}'.
Two ways to ask the same question. The first one extracts a text value and compares it. To Postgres, that is just a function result, and the GIN index knows nothing about it. The second one asks: does this document contain this smaller document? That is exactly what the GIN index was built to answer. It looks up the key and value, and jumps to the matching rows.
payload ->> 'type' = 'refund'extract text, then comparepayload @> '{"type": "refund"}'does the document contain this?Create the GIN index, and try the double arrow filter. Sequential scan. The index is not even considered.
CREATE INDEX idx_events_payload
ON events_big USING gin (payload);EXPLAIN (COSTS OFF)
SELECT event_id FROM events_big
WHERE payload ->> 'type' = 'refund';Seq Scan on events_big Filter: ((payload ->> 'type'::text) = 'refund'::text)
Same rows, written as containment. A Bitmap Index Scan on the GIN index.
EXPLAIN (COSTS OFF)
SELECT event_id FROM events_big
WHERE payload @> '{"type": "refund"}';Bitmap Heap Scan on events_big
Recheck Cond: (payload @> '{"type": "refund"}'::jsonb)
-> Bitmap Index Scan on idx_events_payload
Index Cond: (payload @> '{"type": "refund"}'::jsonb)Two refinements. The jsonb_path_ops operator class makes a smaller, faster GIN index when you only need containment. And if you always filter on one key, a plain B-tree index on that expression is smaller still.
One key only? CREATE INDEX ... ((payload ->> 'type')): a small B-tree.
Question 4: The app needs to change the app version inside the device object of one event, add a reviewed flag, and remove the coupon key. A developer wants to read the document, change it in code, and write it back. Can SQL do it?
Yes. jsonb_set replaces the value at a path, the || operator merges in new top-level keys, and the minus operator removes a key. One UPDATE, no read-modify-write in the application.
Three small tools. jsonb_set takes the document, a path, and a new value, and returns the changed document. The double pipe operator merges two objects. Keys on the right side win. And minus with a key name removes that key. You can chain all three in one expression. Just add parentheses, because minus binds tighter than the double pipe.
jsonb_set(doc, '{device,app}', '"4.0"')replace the value at a pathdoc || '{"reviewed": true}'merge: add or overwrite top-level keysdoc - 'coupon'remove a keyHere is event 5 before the change. The app version is 3.0, and there is a coupon key with a null value.
SELECT jsonb_pretty(payload) AS payload
FROM events
WHERE event_id = 5;{
"tags": [
"sale",
"vip",
"mobile"
],
"type": "purchase",
"amount": 300,
"coupon": null,
"device": {
"os": "Android",
"app": "3.0"
}
}One UPDATE does all three changes, and RETURNING shows the new document. App is 4.0, reviewed is true, and the coupon key is gone.
UPDATE events
SET payload = (jsonb_set(payload, '{device,app}', '"4.0"')
|| '{"reviewed": true}')
- 'coupon'
WHERE event_id = 5
RETURNING jsonb_pretty(payload) AS payload;{
"tags": [
"sale",
"vip",
"mobile"
],
"type": "purchase",
"amount": 300,
"device": {
"os": "Android",
"app": "4.0"
},
"reviewed": true
}Remember what happens underneath. Postgres never edits a row in place. Any change, even to one small key, writes a new version of the whole row. So a large document that changes every few seconds becomes a bloat and vacuum problem. Keep hot counters in their own columns.
A one-key change still rewrites the whole row. Hot fields → real columns.
Question 5: Articles store their tags in a text array column. Product wants the articles tagged postgres, the articles that have both postgres and index, and a count of articles per tag. How do you query an array?
Use = ANY(tags) to test for one value, the @> operator to test that the array contains all the listed values, and unnest to turn the array into one row per element for grouping.
Three questions, three tools. ANY asks: is this value somewhere in the array? Two articles pass. The contains operator asks: does the array hold every one of these values? Index basics and JSONB in practice both have postgres and index, so both pass, and the Spark article does not. And unnest opens each array, so every tag becomes its own row, ready for GROUP BY.
| title | tags |
|---|---|
| Index basics | {postgres,index} |
| JSONB in practice | {postgres,jsonb,index} |
| Spark partitioning | {spark} |
'postgres' = ANY(tags)one value, anywhere in the arraytags @> ARRAY['postgres','index']the array contains all of theseunnest(tags)one row per element → GROUP BYFirst, the articles tagged postgres, with ANY. Two rows.
SELECT article_id, title
FROM articles
WHERE 'postgres' = ANY(tags)
ORDER BY article_id;| article_id | title |
|---|---|
| 1 | Index basics |
| 2 | JSONB in practice |
Then unnest for the count per tag. Each article is repeated once for every tag, and we group. Postgres and index have two articles each.
SELECT t.tag, COUNT(*) AS articles
FROM articles a
CROSS JOIN LATERAL unnest(a.tags) AS t(tag)
GROUP BY t.tag
ORDER BY articles DESC, t.tag;| tag | articles |
|---|---|
| index | 2 |
| postgres | 2 |
| jsonb | 1 |
| spark | 1 |
Notice the untagged draft disappeared from the count. Unnest of an empty array returns no rows. And when should tags be an array? When they are a simple list that you read together with the row. If tags need their own attributes, or foreign keys, use a child table. A GIN index makes the contains operator fast.
Simple list read with the row → array + GIN. Needs attributes or FKs → child table.
Question 6: An API stores each order as one JSON document, with an items array inside. Finance needs one row per item, with the SKU, quantity and price as real typed columns. How do you unpack the array?
Use jsonb_to_recordset in a LATERAL join. It turns an array of objects into rows, and the column list you give it sets the names and types. For simpler cases, jsonb_array_elements returns one jsonb value per element.
One order row holds two items inside its document. We point the function at the items array. For every element of the array it produces one row. And because we declare SKU as text, quantity as integer and price as numeric, the values arrive already typed. The order row is repeated next to each of its items.
| order_id | sku | qty | price |
|---|---|---|---|
| 9001 | LAPTOP | 1 | 1200 |
| 9001 | KEYBOARD | 2 | 50 |
Here is the query. The lateral join calls the function once per order, on that order's items. The AS clause names and types the columns. Three item rows come back for the two orders, with a calculated line total.
SELECT o.order_id,
o.doc ->> 'customer' AS customer,
i.sku, i.qty, i.price,
i.qty * i.price AS line_total
FROM api_orders o
CROSS JOIN LATERAL jsonb_to_recordset(o.doc -> 'items')
AS i(sku TEXT, qty INT, price NUMERIC)
ORDER BY o.order_id, i.sku;| order_id | customer | sku | qty | price | line_total |
|---|---|---|---|---|---|
| 9001 | Sara | KEYBOARD | 2 | 50 | 100 |
| 9001 | Sara | LAPTOP | 1 | 1200 | 1200 |
| 9002 | Omar | MONITOR | 1 | 300 | 300 |
This is how you load API data into a normal table: insert from this SELECT. If the elements are plain values instead of objects, use jsonb_array_elements_text. And to go the other way, from rows back to a document, use jsonb_agg and jsonb_build_object.
Rows → JSON: jsonb_agg(jsonb_build_object(...)). JSON → rows: jsonb_to_recordset.
Question 7: The team wants to keep every attribute in one JSONB column, so they never need a migration again. Is that a good design? What do you say in the design review?
Use real columns for the fields you filter, join, sort or constrain. Keep JSONB for attributes that truly vary. Inside a document, Postgres has no statistics, no types and no foreign keys. A generated column gives you both.
Here is what you give up inside a document. The planner keeps statistics per column, but nothing per JSON key, so its row estimates are guesses. There are no data types, no NOT NULL, no foreign keys. And every row repeats every key name, so the table is bigger. A generated column fixes the important fields: Postgres extracts the value into a real, indexed column, and keeps it in sync by itself.
| Real column | Key inside JSONB | |
|---|---|---|
| Planner statistics | yes: good estimates | none: default guesses |
| Types and constraints | INT, NOT NULL, FOREIGN KEY | none |
| Storage | value only | key name repeated in every row |
| Schema change | ALTER TABLE | nothing to change |
| Best for | filter, join, sort | optional, varying attributes |
event_type TEXT GENERATED ALWAYS AS (payload ->> 'type') STOREDLook at the estimate for refunds through the document. The planner expects 500 rows, a fixed default guess. The real number is 1,000. On a bigger query, a guess like this picks the wrong join.
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT event_id FROM events_big
WHERE payload ->> 'type' = 'refund';Seq Scan on events_big (cost=0.00..2972.00 rows=500 width=4) (actual rows=1000 loops=1) Filter: ((payload ->> 'type'::text) = 'refund'::text) Rows Removed by Filter: 99000
Add a generated column for the type, index it, and analyze. Now the filter uses the index, and the estimate is close to the real 1,000, because the column has its own statistics.
ALTER TABLE events_big
ADD COLUMN event_type TEXT
GENERATED ALWAYS AS (payload ->> 'type') STORED;
CREATE INDEX idx_events_type ON events_big (event_type);
ANALYZE events_big;EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT event_id FROM events_big
WHERE event_type = 'refund';Bitmap Heap Scan on events_big (cost=12.22..1507.74 rows=1023 width=4) (actual rows=1000 loops=1)
Recheck Cond: (event_type = 'refund'::text)
Heap Blocks: exact=1000
-> Bitmap Index Scan on idx_events_type (cost=0.00..11.96 rows=1023 width=0) (actual rows=1000 loops=1)
Index Cond: (event_type = 'refund'::text)So my answer in the review: a hybrid. Columns for the identity and the fields every query touches, one JSONB column for the long tail of optional attributes, and generated columns when a JSON key becomes important.
Hybrid design: columns for the core, JSONB for the long tail.
Key points to remember
- Use jsonb: parsed once, indexable. json only keeps the exact text
- -> returns jsonb, ->> returns text. Cast before you compare numbers
- A GIN index serves @> containment, not
->>comparisons - jsonb_set and || change a document in SQL, and jsonb_to_recordset turns arrays into rows
- Fields you filter and join on deserve real columns, or a generated column
The full SQL script
Here is everything in one script: the sample data and every query from this post, in order.
Show the full SQL script (sample data and every query)
-- ========================================================================
-- PostgreSQL Interview Q&A #8 - JSONB, arrays and Postgres types
-- TechBrothersIT - run on PostgreSQL 16
-- ========================================================================
-- Sample data
SET max_parallel_workers_per_gather = 0; -- keep the plans simple
CREATE TABLE events (event_id INT PRIMARY KEY, user_id INT, payload JSONB);
INSERT INTO events VALUES
(1, 1, '{"type": "purchase", "amount": 120, "device": {"os": "iOS", "app": "3.2"}, "tags": ["sale", "mobile"]}'),
(2, 2, '{"type": "login", "device": {"os": "Android", "app": "3.1"}, "tags": ["mobile"]}'),
(3, 1, '{"type": "purchase", "amount": 40, "device": {"os": "Windows"}, "tags": ["web"]}'),
(4, 3, '{"type": "refund", "amount": 40, "device": {"os": "iOS", "app": "3.2"}, "tags": []}'),
(5, 2, '{"type": "purchase", "amount": 300, "coupon": null, "device": {"os": "Android", "app": "3.0"}, "tags": ["sale", "vip", "mobile"]}'),
(6, 3, '{"type": "login", "device": {"os": "macOS"}}');
-- 100,000 generated events: 1% refunds
CREATE TABLE events_big AS
SELECT g AS event_id, g % 2000 AS user_id,
jsonb_build_object(
'type', CASE WHEN g % 100 = 0 THEN 'refund' WHEN g % 4 = 0 THEN 'purchase' ELSE 'login' END,
'amount', g % 500,
'device', jsonb_build_object('os', (ARRAY['iOS', 'Android', 'Windows', 'macOS'])[1 + g % 4])
) AS payload
FROM generate_series(1, 100000) AS g;
ALTER TABLE events_big ADD PRIMARY KEY (event_id);
VACUUM ANALYZE events_big;
CREATE TABLE articles (article_id INT PRIMARY KEY, title TEXT, tags TEXT[]);
INSERT INTO articles VALUES
(1, 'Index basics', ARRAY['postgres', 'index']),
(2, 'JSONB in practice', ARRAY['postgres', 'jsonb', 'index']),
(3, 'Spark partitioning', ARRAY['spark']),
(4, 'Untagged draft', '{}');
CREATE TABLE api_orders (order_id INT PRIMARY KEY, doc JSONB);
INSERT INTO api_orders VALUES
(9001, '{"customer": "Sara", "items": [{"sku": "LAPTOP", "qty": 1, "price": 1200}, {"sku": "KEYBOARD", "qty": 2, "price": 50}]}'),
(9002, '{"customer": "Omar", "items": [{"sku": "MONITOR", "qty": 1, "price": 300}]}');
-- Step 1 (question 1): json vs jsonb
SELECT '{"b": 1, "a": 2, "a": 3}'::json AS as_json,
'{"b": 1, "a": 2, "a": 3}'::jsonb AS as_jsonb;
-- Step 2 (question 2): arrow types
SELECT event_id,
payload -> 'type' AS single_arrow,
payload ->> 'type' AS double_arrow,
payload ->> 'amount' AS amount_text
FROM events
ORDER BY event_id;
-- Step 3 (question 2): purchases over 100
SELECT event_id,
(payload ->> 'amount')::int AS amount,
payload -> 'device' ->> 'os' AS os
FROM events
WHERE payload ->> 'type' = 'purchase'
AND (payload ->> 'amount')::int > 100
ORDER BY event_id;
-- Step 4 (question 3): create gin
CREATE INDEX idx_events_payload
ON events_big USING gin (payload);
-- Step 5 (question 3): plan double arrow
EXPLAIN (COSTS OFF)
SELECT event_id FROM events_big
WHERE payload ->> 'type' = 'refund';
-- Step 6 (question 3): plan containment
EXPLAIN (COSTS OFF)
SELECT event_id FROM events_big
WHERE payload @> '{"type": "refund"}';
-- Step 7 (question 4): before update
SELECT jsonb_pretty(payload) AS payload
FROM events
WHERE event_id = 5;
-- Step 8 (question 4): update document
UPDATE events
SET payload = (jsonb_set(payload, '{device,app}', '"4.0"')
|| '{"reviewed": true}')
- 'coupon'
WHERE event_id = 5
RETURNING jsonb_pretty(payload) AS payload;
-- Step 9 (question 5): tag any
SELECT article_id, title
FROM articles
WHERE 'postgres' = ANY(tags)
ORDER BY article_id;
-- Step 10 (question 5): tag counts
SELECT t.tag, COUNT(*) AS articles
FROM articles a
CROSS JOIN LATERAL unnest(a.tags) AS t(tag)
GROUP BY t.tag
ORDER BY articles DESC, t.tag;
-- Step 11 (question 6): items to rows
SELECT o.order_id,
o.doc ->> 'customer' AS customer,
i.sku, i.qty, i.price,
i.qty * i.price AS line_total
FROM api_orders o
CROSS JOIN LATERAL jsonb_to_recordset(o.doc -> 'items')
AS i(sku TEXT, qty INT, price NUMERIC)
ORDER BY o.order_id, i.sku;
-- Step 12 (question 7): estimate jsonb
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT event_id FROM events_big
WHERE payload ->> 'type' = 'refund';
-- Step 13 (question 7): add generated
ALTER TABLE events_big
ADD COLUMN event_type TEXT
GENERATED ALWAYS AS (payload ->> 'type') STORED;
CREATE INDEX idx_events_type ON events_big (event_type);
ANALYZE events_big;
-- Step 14 (question 7): estimate column
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT event_id FROM events_big
WHERE event_type = 'refund';
Part 9: Upserts and data changes
The sample data on this page is invented for teaching. Every output table and query plan is the real result of the query above it, run on PostgreSQL 16 over that sample data.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.