A jsonb column is the pressure valve in a relational schema. Event payloads, webhook bodies, per-tenant settings and feature flags all have shapes that change faster than migrations do, and PostgreSQL lets you store them without giving up SQL. The interview questions come from the edges: which operator returns what type, why an index isn’t being used, and when a JSON field should have been a column all along.
json versus jsonb
PostgreSQL has two JSON types. json stores the exact input text and re-parses it every time you query it. jsonb parses once on write and stores a binary form. The documentation spells out what that costs you: jsonb doesn’t keep whitespace, doesn’t keep key order, and if an object has the same key twice, only the last value survives. In psql it looks like this (PostgreSQL output, shown for illustration):
SELECT '{"b":1, "a":2, "a":3}'::json; -- {"b":1, "a":2, "a":3}
SELECT '{"b":1, "a":2, "a":3}'::jsonb; -- {"a": 3, "b": 1}What you gain is speed on reads, a rich set of operators, and indexing. Pick json only when you must reproduce the original text byte for byte, for example to verify a webhook signature, and even then many teams store the raw body as text alongside a jsonb copy for querying.
Extracting values: -> versus ->>
Take an events table whose payload column holds documents like:
{"plan": "pro", "utm": {"source": "newsletter"}, "seats": 10}The two arrow operators differ in what they return. -> returns the value as jsonb, so you can keep navigating. ->> returns it as text, which is what you want for display and comparison. For nested paths there’s #> and #>> with a text array, or simply chained arrows:
SELECT payload -> 'utm' AS utm_jsonb, -- {"source": "newsletter"}
payload -> 'utm' ->> 'source' AS source, -- newsletter
payload #>> '{utm,source}' AS same_source -- newsletter
FROM events;SQLite 3.38 and later copied these operators, which made it possible to run the examples here (SQLite 3.53 through Python) on four rows. Extracting utm and utm.source gave:
id | ->'utm' | ->'$.utm.source' | ->>'$.utm.source'
----+---------------------------+------------------+-------------------
1 | {"source":"newsletter"} | "newsletter" | newsletter
2 | {"source":"google"} | "google" | google
3 | NULL | NULL | NULL
4 | NULL | NULL | NULLNotice the quotes: -> gives back a JSON string, "newsletter", while ->> gives the bare text. Comparing payload -> 'plan' = 'pro' in PostgreSQL fails or misbehaves because you’re comparing jsonb to a literal; use ->>.
The type of ->> is the classic trap. In PostgreSQL it’s always text, so this filter compares strings:
WHERE payload ->> 'seats' > '9' -- '10' > '9' is false: text compares character by character
WHERE (payload ->> 'seats')::int > 9 -- what you meantSQLite is different here: its ->> returns an integer for a JSON number (the run above reported typeof as integer), so a numeric comparison works there and the bug only appears once the code reaches PostgreSQL. Cast explicitly and the query means the same thing on both.
JSON null is not a missing key
Row 3’s payload contains "seats": null; row 4 has no seats key at all. They come back differently from ->:
id | ->>'seats' | ->'seats'
----+------------+-----------
1 | 10 | 10
2 | 9 | 9
3 | NULL | null
4 | NULL | NULL->> collapses both cases to SQL NULL. -> keeps them apart: row 3 returns the JSON value null, row 4 returns SQL NULL because there’s nothing to extract. PostgreSQL behaves the same way. If the distinction matters (“the client explicitly cleared seats” versus “the client didn’t send seats”), test for the key with the ? operator: payload ? 'seats' is true for row 3 and false for row 4.
Indexing the queries you actually run
There are two index strategies, and they answer different query shapes.
A GIN index on the whole column serves containment and key-existence queries:
CREATE INDEX events_payload_gin ON events USING gin (payload);
SELECT id FROM events WHERE payload @> '{"plan": "pro"}';
SELECT id FROM events WHERE payload ? 'utm';@> asks “does the left document contain this fragment?”, and it works at any depth: payload @> '{"utm": {"source": "google"}}' matches nested values too. The default operator class, jsonb_ops, supports the key-exists operators ?, ?| and ?&, containment @>, and the jsonpath operators @? and @@. The alternative class, jsonb_path_ops (USING gin (payload jsonb_path_ops)), drops support for the key-exists operators in exchange for a smaller, usually faster index. If you only ever query with @>, it’s the better pick.
A B-tree index on one extracted expression serves equality and range filters on a single field:
CREATE INDEX events_plan ON events ((payload ->> 'plan'));
SELECT id FROM events WHERE payload ->> 'plan' = 'pro';Note the double parentheses, which PostgreSQL requires around an expression in an index definition. In SQLite the same index turned the lookup into SEARCH events USING COVERING INDEX events_plan (<expr>=?), and the query returned rows 1 and 3.
Here’s the mistake interviewers look for: a GIN index does nothing for payload ->> 'plan' = 'pro'. The planner only uses an index whose operator class supports the operator in your WHERE clause, and ->> followed by = isn’t a GIN-supported operator. Teams add a GIN index, keep writing ->> filters, and wonder why EXPLAIN still shows a sequential scan. Either rewrite the filter as payload @> '{"plan": "pro"}' or add the expression index. Similarly, the expression index is only used when the query repeats the expression exactly; (payload ->> 'plan') won’t help a filter on payload #>> '{plan}'.
When a field should become a column
JSON fields give up things the relational side gives you for free: a type, NOT NULL, foreign keys, CHECK constraints, accurate planner statistics, and cheap updates. That last one surprises people. Changing one key with jsonb_set(payload, '{plan}', '"team"') writes a new version of the entire row, document included, so frequently updated counters inside a large document are expensive.
A reasonable rule: if a field is in most queries’ WHERE clauses, in a join, under a constraint, or updated on its own, promote it to a real column. Keep jsonb for the long tail of attributes that are read with the row and rarely filtered. Migrating is straightforward: add the column, backfill it from payload ->> 'field' in batches, then switch reads. A generated column is a middle ground when you want a typed, indexable copy of a JSON field that always stays in sync.
Interview question: the slow tenant settings lookup
A tenants table has a settings jsonb column with a GIN index. This query runs on every request and EXPLAIN shows a sequential scan over 2 million rows:
SELECT id FROM tenants WHERE settings ->> 'region' = 'eu-west-1';Why isn’t the index used, and what are your options?
Answer. The GIN index (with either jsonb_ops or jsonb_path_ops) supports containment and existence operators, not equality on a text value extracted with ->>, so the planner can’t use it for this predicate. Two fixes. Rewrite the predicate as containment, WHERE settings @> '{"region": "eu-west-1"}', which the existing GIN index can serve. Or create a B-tree expression index, CREATE INDEX ON tenants ((settings ->> 'region')), which is smaller than the GIN index and also supports ranges and sorting on that field. Since region is queried on every request and probably has a fixed set of valid values, the strongest answer goes further: promote it to a real region column with a CHECK or a foreign key to a regions table, which gives correct planner statistics and stops tenants being saved with "eu-west1". Mention verifying the chosen fix with EXPLAIN ANALYZE afterwards.
Further reading
- SQL interview questions and SQL MCQs
- Generated columns for a typed copy of a JSON field
- Reading EXPLAIN ANALYZE to check which index a query uses
- PostgreSQL docs: JSON types, containment and jsonb indexing