SQL JSON Functions: JSON_VALUE, JSON_EXTRACT, JSONB, JSON_TABLE Across Dialects

sql json is the single most-searched semi-structured keyword in modern warehouse SQL — and the single most dialect-fractured one in 2026. The same "extract user.id out of an event payload and use it as a join key" ask has five different answers across the five engines most data teams live in: Postgres uses payload->'user'->>'id' or jsonb_path_query, MySQL 8 ships JSON_EXTRACT(payload, '$.user.id') and the ->> shortcut, SQL Server ships JSON_VALUE(payload, '$.user.id'), Snowflake reaches for its dot-notation payload:user.id::string on top of a VARIANT column, and BigQuery ships JSON_VALUE(payload, '$.user.id') against a native JSON type. There is no portable spelling of a JSON path; the interviewer wants to hear you say that out loud in the first sentence.

This guide is the mid-to-senior comparison you wished existed the first time an interviewer asked you to write a json_value sql expression on the whiteboard, sketch a json_extract sql join in MySQL, wire a jsonb postgres GIN index for a hot containment filter, flatten a nested payload with json_table sql, chase a schema-drift regression in bigquery json, or design a snowflake variant column for a fast-changing event stream. It walks through the JSONPath grammar every engine borrows ($ root, .key child, [i] index, [*] wildcard, ..desc recursive descent), the scalar-vs-sub-tree split that separates JSON_VALUE from JSON_QUERY and Postgres ->> from ->, the Postgres jsonb type with its jsonb_ops and jsonb_path_ops GIN indexes plus the @> containment operator, the ANSI JSON_TABLE clause with its NESTED PATH sub-block and its Snowflake LATERAL FLATTEN, BigQuery UNNEST(JSON_QUERY_ARRAY(...)), and Postgres jsonb_to_recordset equivalents, and the storage-plus-index performance matrix across all five engines including when a materialised generated column beats every path expression. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the JSON practice library →, rehearse on parsing problems →, and sharpen the reshape axis with data-transformation drills →.

---

On this page

• Why JSON in SQL matters in 2026
• JSON_VALUE / JSON_EXTRACT — path expressions
• JSONB in Postgres — index it or regret it
• JSON_TABLE — flatten JSON to rows
• Dialect matrix + performance
• Cheat sheet — SQL JSON recipe list
• Frequently asked questions
• Practice on PipeCode

---

1. Why JSON in SQL matters in 2026

JSON columns are the default event-payload shape — and the mental model every senior data engineer needs to lock in on day one

The one-sentence invariant: a sql json column stores semi-structured data whose schema drifts faster than the surrounding relational model, and every senior interviewer expects you to name the dialect's storage type, its path syntax, its index options, and the flattening operator in the same breath. Once you internalise that "storage type + path syntax + index shape + flatten operator" is the whole design space, the JSON-in-SQL interview surface collapses into a four-cell mental card per dialect.

Four axes interviewers actually probe.

• Storage type. Postgres has both json (raw text, ordered, no dedup) and jsonb (parsed, deduped, binary, indexable); MySQL 8 has one binary JSON type; SQL Server piggybacks on NVARCHAR(MAX) with ISJSON validation; Snowflake has VARIANT (columnar, self-describing); BigQuery has a native JSON type on top of the older STRING-of-JSON pattern. Every choice trades parse cost, storage size, and query cost against schema flexibility.
• Path syntax. The five engines share the JSONPath grammar ($ root, .key child, [i] index, [*] wildcard) but disagree on the wrapper: Postgres exposes ->, ->>, #>, #>> arrow operators plus SQL/JSON jsonb_path_query; MySQL 8 ships JSON_EXTRACT and the ->> shortcut; SQL Server ships JSON_VALUE and JSON_QUERY; Snowflake extends the SQL grammar with col:key.sub.notation; BigQuery ships JSON_VALUE, JSON_QUERY, and a dot-syntax on the native JSON type.
• Scalar vs sub-tree. Every dialect distinguishes "extract a scalar and coerce it to a SQL type" from "extract a JSON sub-tree that stays JSON." SQL Server draws the line at JSON_VALUE (scalar) vs JSON_QUERY (sub-tree); Postgres draws it at ->> (scalar text) vs -> (JSON child); MySQL draws it at ->> vs ->; BigQuery draws it at JSON_VALUE vs JSON_QUERY. Missing this split is the most-asked interview correction.
• Flatten to rows. Nested arrays cannot be joined against a relational query without a flattening operator. The ANSI answer is JSON_TABLE (Oracle, MySQL 8, SQL Server 2022); Snowflake ships LATERAL FLATTEN; BigQuery pairs UNNEST with JSON_QUERY_ARRAY; Postgres uses jsonb_array_elements, jsonb_to_recordset, or a LATERAL join.

Where semi-structured data lands in your pipelines.

• Event tracking payloads. Segment, Rudderstack, Snowplow, and PostHog all emit event(id, timestamp, event_type, payload jsonb) schemas. The event-type-specific columns live inside payload because they change too fast to materialise as columns.
• Third-party API bodies. Webhooks from Stripe, Shopify, Zendesk, HubSpot, and 100 other SaaS integrations arrive as JSON. Landing them in a JSON column preserves the source-of-truth shape; downstream transforms extract typed columns as they stabilise.
• CDC envelopes. Debezium, Fivetran, Airbyte, and Meltano wrap each row change in a JSON envelope with before, after, op, source, ts_ms keys. Consumer pipelines routinely JSON_EXTRACT(payload, '$.after.user_id') to project the changed row.
• Config and feature flags. LaunchDarkly, GrowthBook, and internal feature-flag tables often store variant assignments as a JSON blob per user. Filtering on payload->>'variant' is a hot analytics path.
• Semi-structured logs. Application logs (structured JSON) land as JSON columns for indexing; SELECT * FROM logs WHERE payload @> '{"level":"error"}' is a common Postgres pattern.

When JSON-in-SQL beats a separate document store.

• One system, one backup, one auth model. Running MongoDB or DynamoDB alongside your warehouse doubles your operational surface. Modern warehouses ship JSON support strong enough that most read-heavy workloads never leave SQL.
• Joins with the relational world. Every event has a user_id that joins against your users table. Doing that join across a warehouse and MongoDB is painful; doing it inside Postgres with jsonb extraction is a two-line query.
• Analytics-friendly shape. Once you flatten with JSON_TABLE or LATERAL FLATTEN, you get a relational shape that every BI tool understands. Grafana, Metabase, Superset, Looker — every dashboard tool wants columns, not documents.
• Cost. Snowflake VARIANT and BigQuery native JSON push semi-structured storage into columnar formats. Query cost on a 10TB event log is often lower than a document store's query cost on the same shape.

When a separate document store still wins.

• Sub-10ms point-read latency. MongoDB and DynamoDB are optimised for OLTP-style single-document reads. Warehouses are not.
• Deep nesting with heavy write-heavy update patterns. JSONB updates in Postgres rewrite the whole binary blob; MongoDB updates individual sub-documents. If your workload is 10× writes per read on a big nested doc, a document store fits better.
• Write throughput above 100k QPS. Warehouses are batch-write friendly; document stores handle single-row streaming writes better.

What senior interviewers probe on JSON-in-SQL.

• Path vs pointer. JSONPath ($.a.b.c) is the query grammar; JSON Pointer (/a/b/c) is the addressing grammar (RFC 6901). Both exist; pointers are what Postgres #> and #>> use.
• Scalar vs sub-tree. Do you say "JSON_VALUE is scalar, JSON_QUERY is sub-tree" without prompting? Do you know Postgres draws the same line at ->> vs ->?
• Storage cost. jsonb is ~10% smaller than json (binary encoded, deduped keys), Snowflake VARIANT is columnar-encoded, BigQuery native JSON beats STRING-of-JSON for both storage and query cost.
• Index shape. GIN indexes with jsonb_ops (universal, larger) vs jsonb_path_ops (containment-only, smaller). When to add a functional expression index on a single path. When to materialise a generated column instead.
• When to flatten. Every join key, filter key, and group-by key that comes out of JSON should be materialised as a column. Every read-only projection can stay in JSON. Naming this rule out loud is the senior signal.

Worked example — the same "extract user id" ask in five dialects

Detailed explanation. The canonical warm-up interview ask — given an events table with a JSON payload, extract user_id and count events per user — reads identically as an English requirement across every warehouse. The SQL to express it looks wildly different by dialect. Writing the same extraction five ways builds a mental dialect matrix that pays off in every JSON follow-up question.

Question. Given an events table with (event_id, ts, payload) where payload is a JSON column containing at minimum a user_id key, write a query that counts events per user in Postgres (jsonb), MySQL 8, SQL Server, Snowflake, and BigQuery.

Input.

event_id	ts	payload
1	2026-07-01	{"user_id": "u1", "kind": "click"}
2	2026-07-01	{"user_id": "u1", "kind": "view"}
3	2026-07-01	{"user_id": "u2", "kind": "click"}
4	2026-07-02	{"user_id": "u1", "kind": "purchase"}

Code.

-- Postgres (jsonb) — arrow operator shortcut
SELECT payload->>'user_id' AS user_id, COUNT(*) AS n
FROM events
GROUP BY payload->>'user_id';

-- MySQL 8 — JSON_EXTRACT + JSON_UNQUOTE (or the ->> shortcut)
SELECT payload->>'$.user_id' AS user_id, COUNT(*) AS n
FROM events
GROUP BY payload->>'$.user_id';

-- SQL Server — JSON_VALUE
SELECT JSON_VALUE(payload, '$.user_id') AS user_id, COUNT(*) AS n
FROM events
GROUP BY JSON_VALUE(payload, '$.user_id');

-- Snowflake — dot-notation VARIANT access + cast
SELECT payload:user_id::string AS user_id, COUNT(*) AS n
FROM events
GROUP BY payload:user_id::string;

-- BigQuery — JSON_VALUE on native JSON type
SELECT JSON_VALUE(payload, '$.user_id') AS user_id, COUNT(*) AS n
FROM events
GROUP BY JSON_VALUE(payload, '$.user_id');

Step-by-step explanation.

1. Postgres's payload->>'user_id' is the "arrow followed by arrow followed by greater-than" operator that reads: "extract the user_id key and return the value as text." The double-arrow ->> unwraps the JSON string to a SQL text — critical for GROUP BY.
2. MySQL 8's payload->>'$.user_id' is a syntactic shortcut for JSON_UNQUOTE(JSON_EXTRACT(payload, '$.user_id')). Same mechanics as Postgres's ->> but the path is JSONPath (with $. prefix) rather than a bare key name.
3. SQL Server's JSON_VALUE(payload, '$.user_id') returns a scalar SQL string. It errors if the target is a JSON object or array — JSON_VALUE is scalar-only. For sub-tree extraction you would reach for JSON_QUERY instead.
4. Snowflake's payload:user_id::string uses colon-notation for the key access and the SQL :: cast operator to force the return type. Without the cast, the value stays a VARIANT — which is fine for further chaining but often surprises consumers who expect a VARCHAR.
5. BigQuery's JSON_VALUE(payload, '$.user_id') looks like SQL Server's but runs against the native JSON type. If your column is STRING-of-JSON, use JSON_EXTRACT_SCALAR instead — a common bigquery json gotcha.

Output.

user_id	n
u1	3
u2	1

Rule of thumb. In every dialect, always cast or unwrap the extracted value to a SQL scalar type before GROUP BY, JOIN, or WHERE. Grouping on a raw JSON sub-tree produces different equality semantics per dialect and is almost always a bug.

Worked example — sub-tree vs scalar (JSON_VALUE vs JSON_QUERY)

Detailed explanation. The most common junior-to-senior correction: candidates reach for JSON_VALUE to grab a nested object and hit "the requested JSON path expression is not a scalar." The interviewer wants to see you know that JSON_VALUE returns a scalar, JSON_QUERY returns a sub-tree, and the same split exists in Postgres (->> vs ->), MySQL (->> vs ->), and Snowflake (::string cast vs raw VARIANT).

Question. Given a payload {"user": {"id": "u1", "name": "Ada"}, "kind": "click"}, write two queries per dialect (SQL Server + Postgres): one that returns the user sub-tree as JSON, and one that returns the user.id scalar as a SQL string. Explain the error you'd hit if you mis-used them.

Input.

id	payload
1	{"user": {"id": "u1", "name": "Ada"}, "kind": "click"}
2	{"user": {"id": "u2", "name": "Bo"}, "kind": "view"}

Code.

-- SQL Server — JSON_QUERY for sub-tree, JSON_VALUE for scalar
SELECT
  JSON_QUERY(payload, '$.user')       AS user_json,   -- returns JSON object
  JSON_VALUE(payload, '$.user.id')    AS user_id      -- returns scalar
FROM events;

-- WRONG — JSON_VALUE on a sub-tree returns NULL (or errors depending on option)
SELECT JSON_VALUE(payload, '$.user') FROM events;

-- Postgres (jsonb) — -> for sub-tree, ->> for scalar
SELECT
  payload -> 'user'              AS user_json,   -- returns jsonb
  payload -> 'user' ->> 'id'     AS user_id      -- returns text
FROM events;

-- Alt — Postgres path pointer #> (sub-tree), #>> (scalar text)
SELECT
  payload #>  '{user}'           AS user_json,
  payload #>> '{user,id}'        AS user_id
FROM events;

Step-by-step explanation.

1. JSON_QUERY and JSON_VALUE are complementary: one returns a JSON sub-tree (still JSON), the other returns a scalar coerced to a SQL type. Mixing them up is the most-asked correction.
2. In SQL Server, JSON_VALUE(payload, '$.user') on an object target returns NULL in lax mode (default) or errors in strict mode. Neither is what you want — reach for JSON_QUERY instead.
3. Postgres's -> returns a jsonb child; ->> returns the text representation of a scalar child. The double-arrow always unwraps quotes; the single-arrow leaves them.
4. Postgres's #> and #>> are the path-pointer variants that accept a text-array of keys. payload #>> '{user,id}' is equivalent to payload -> 'user' ->> 'id'. Path pointers are useful for programmatic construction; arrow chains are more readable for humans.
5. When the extracted value is used as a GROUP BY key or a JOIN target, always use the scalar form — JSON_VALUE in SQL Server, ->> in Postgres/MySQL, ::string in Snowflake, JSON_VALUE in BigQuery. The sub-tree form is only for further JSON chaining or for embedding in another JSON output.

Output.

id	user_json	user_id
1	{"id": "u1", "name": "Ada"}	u1
2	{"id": "u2", "name": "Bo"}	u2

Rule of thumb. Every extraction is either scalar (headed for a SQL type) or sub-tree (headed for further JSON work). Name the direction before you write the operator — the operator falls out of the direction.

Worked example — the JSONPath grammar every engine borrows

Detailed explanation. JSONPath is the ANSI-flavoured grammar every engine implements. The five must-know constructs are $ (root), .key (child by name), [i] (child by index), [*] (wildcard over an array), and ..desc (recursive descent). Once you know the grammar, the dialect surface reduces to "which function wraps the path?"

Question. Given a payload with a nested orders array {"user_id": "u1", "orders": [{"id": 1, "total": 10}, {"id": 2, "total": 25}]}, write the JSONPath expressions to extract (a) the user_id, (b) the first order's total, (c) all order totals, (d) any id anywhere in the payload. Show one dialect for each — Postgres jsonb_path_query, MySQL JSON_EXTRACT, SQL Server JSON_VALUE / JSON_QUERY, BigQuery JSON_VALUE / JSON_QUERY_ARRAY.

Input.

id	payload
1	{"user_id": "u1", "orders": [{"id": 1, "total": 10}, {"id": 2, "total": 25}]}

Code.

-- Postgres — SQL/JSON jsonb_path_query
SELECT
  jsonb_path_query_first(payload, '$.user_id')      AS user_id,
  jsonb_path_query_first(payload, '$.orders[0].total') AS first_total,
  jsonb_path_query(payload, '$.orders[*].total')    AS every_total,
  jsonb_path_query(payload, '$..id')                AS every_id
FROM events;

-- MySQL 8 — JSON_EXTRACT
SELECT
  JSON_EXTRACT(payload, '$.user_id')            AS user_id,
  JSON_EXTRACT(payload, '$.orders[0].total')    AS first_total,
  JSON_EXTRACT(payload, '$.orders[*].total')    AS every_total,
  JSON_EXTRACT(payload, '$..id')                AS every_id
FROM events;

-- SQL Server — JSON_VALUE (scalar), JSON_QUERY (sub-tree / array)
SELECT
  JSON_VALUE(payload, '$.user_id')              AS user_id,
  JSON_VALUE(payload, '$.orders[0].total')      AS first_total,
  JSON_QUERY(payload, '$.orders[*].total')      AS every_total  -- array
FROM events;
-- Note: SQL Server has no ..desc recursive-descent operator.

-- BigQuery — JSON_VALUE, JSON_QUERY_ARRAY
SELECT
  JSON_VALUE(payload, '$.user_id')                        AS user_id,
  JSON_VALUE(payload, '$.orders[0].total')                AS first_total,
  JSON_QUERY_ARRAY(payload, '$.orders')                   AS every_order,
  JSON_QUERY_ARRAY(payload, '$..id')                      AS every_id
FROM events;

Step-by-step explanation.

1. $ is the root — every JSONPath starts with it. $.user_id reads "from the root, take the user_id child."
2. [i] is index access. $.orders[0] picks the first element; $.orders[-1] picks the last (dialect-dependent — Postgres and BigQuery support negative indexing, SQL Server does not).
3. [*] is the wildcard — "every element of this array." Combined with a child key, $.orders[*].total returns every order's total. The return shape is an array when the wrapper function returns arrays (JSON_QUERY), or a set of rows when the wrapper is set-returning (jsonb_path_query).
4. ..key is recursive descent — "find key anywhere in the sub-tree." Postgres, MySQL, and BigQuery support it; SQL Server does not. Great for pulling a value out of a deeply nested payload without knowing the exact path.
5. Every dialect maps the same JSONPath grammar to a different wrapper: Postgres jsonb_path_query (set-returning) or jsonb_path_query_first (scalar), MySQL JSON_EXTRACT (returns a JSON value that may be an array), SQL Server JSON_VALUE (scalar) or JSON_QUERY (sub-tree), BigQuery JSON_VALUE (scalar) or JSON_QUERY / JSON_QUERY_ARRAY (sub-tree/array).

Output (Postgres set-returning).

user_id	first_total	every_total	every_id
"u1"	10	10	1
"u1"	10	25	2

Rule of thumb. Memorise the five JSONPath constructs ($, .key, [i], [*], ..desc) once. After that, the dialect surface is just "which function wraps the path?" and "does the function return a scalar, a JSON, or a set of rows?"

Senior interview question on the JSON-in-SQL mental model

A senior interviewer often opens with: "You have a table events(id, ts, payload) where payload is a JSON column. Walk me through your decision tree for whether to keep a value inside the JSON, materialise it as a generated column, or copy it into a separate typed column. What's your rule of thumb?"

Solution Using the "hot path materialisation" decision framework

Decision framework — keep in JSON, materialise, or extract to a column

1. Is the key on the hot filter path (WHERE payload->>'key' = ...)?
   yes → materialise as a generated column + index it
   no  → keep in JSON

2. Is the key a JOIN target?
   yes → materialise as a generated column + index it
   no  → keep in JSON

3. Is the key a GROUP BY or ORDER BY key?
   yes → materialise as a generated column
   no  → keep in JSON

4. Does the key have a stable presence across all rows?
   yes → materialise (or promote to a plain column)
   no  → keep in JSON

5. Is the key part of a schema-drift-heavy sub-tree
   (rarely-queried event-type-specific fields)?
   yes → keep in JSON forever
   no  → consider promotion to a plain column

Step-by-step trace.

Key	Q1 hot filter	Q2 join	Q3 group/order	Q4 stable	Q5 drift	Verdict
user_id	yes	yes	yes	yes	no	Promote to a plain column
event_type	yes	no	yes	yes	no	Materialise + index
session_id	sometimes	no	no	yes	no	Materialise (no index)
orders[*].total (array agg)	no	no	no	no	yes	Keep in JSON, flatten at query time
custom_flags (freeform tags)	no	no	no	no	yes	Keep in JSON forever

The framework turns "should I extract this key?" from a taste question into a mechanical decision. Every key on the hot query path pays for materialisation with much better plan quality; every drifty key stays in the JSON payload where it belongs.

Output:

Strategy	When it wins
Promote to plain column	Stable + always-present + hot filter/join/group key
Materialise as generated column + index	Stable + hot filter path + still expressible as a JSON extract
Materialise as generated column (no index)	Hot projection but not filter/join key
Keep in JSON, flatten at query time	Nested arrays consumed by rare reports
Keep in JSON forever	Schema-drift heavy, low-frequency reads

Why this works — concept by concept:

• Hot path materialisation — every warehouse has a query planner that struggles with expression-based filters. WHERE payload->>'user_id' = 'u1' triggers a full scan unless the expression is indexed. Materialising the extraction into a real column exposes it to the planner and unlocks index-based filtering.
• Generated columns preserve the source of truth — a GENERATED ALWAYS AS (payload->>'user_id') STORED column stays in sync with the JSON automatically. You get the query-time win of a materialised column and the schema-drift resilience of the JSON payload in the same table.
• Schema drift is a first-class concern — the reason data engineers reach for JSON at all is that the schema changes weekly. Freezing every field into a plain column defeats the purpose. The framework keeps the drifty fields in JSON and only promotes the stable, high-value ones.
• Stable + hot signals both matter — a field that's stable but never queried doesn't need materialisation; a hot field that's rarely present doesn't fit as a plain column. The Q1×Q4 intersection is where materialisation earns its keep.
• Cost — every materialised column costs some write-time CPU (evaluate the expression on insert/update) and some storage (the extracted value stored alongside the JSON). The trade-off is query-time speedup on the hot path; if the query fires 1000 times a day and the write fires 100 times, the trade almost always pays off.

SQL
Topic — JSON
JSON extraction and reshape problems

Practice →

SQL Topic — parsing Parsing drills

Practice →

---

2. JSON_VALUE / JSON_EXTRACT — path expressions

json_value sql returns a scalar, json_extract sql returns JSON — the same JSONPath grammar wraps them, but the dialect dresses the wrapper differently

The mental model in one line: every JSON-in-SQL path expression is a JSONPath ($.a.b.c) wrapped in a dialect-specific function that returns either a scalar SQL type (JSON_VALUE, ->>) or a JSON sub-tree (JSON_QUERY, ->), and the interviewer expects you to name both halves of the split without prompting. Once you say "scalar vs sub-tree, JSONPath vs pointer, arrow vs function," the path-expression interview surface is a lookup.

JSONPath grammar refresher.

• $ — root of the JSON document.
• .key — child by name (dot notation). Fails if the key contains hyphens, spaces, or reserved words — use bracket notation instead.
• ["key-with-hyphen"] — bracket notation. Always works, always safe.
• [i] — child by array index (0-based).
• [*] — wildcard over an array (every element).
• ..key — recursive descent (find key anywhere in the sub-tree). Not supported in SQL Server.
• [?(@.total > 100)] — filter expression (SQL/JSON standard; Postgres and MySQL 8 support it).

Dot vs bracket notation.

• Use dot when the key is a plain identifier: $.user.id.
• Use bracket when the key has a hyphen, space, or reserved word: $["user-id"], $["order date"], $["$type"].
• Bracket notation works everywhere dot notation works, so a code generator that emits pure bracket notation is bulletproof.

Postgres arrow operators.

• -> — child by name, returns jsonb. payload -> 'user' returns the user sub-tree as jsonb.
• ->> — child by name, returns text. payload ->> 'user_id' returns the value as a SQL text.
• #> — path by text-array, returns jsonb. payload #> '{user, id}' navigates to user.id and returns jsonb.
• #>> — path by text-array, returns text. Same navigation but returns text.
• These are shortcuts over jsonb_extract_path and jsonb_extract_path_text and are more concise for common queries.

Postgres SQL/JSON path API (Postgres 12+).

• jsonb_path_query(payload, '$.orders[*].total') — set-returning; one row per match.
• jsonb_path_query_first(payload, '$.user_id') — scalar; first match or NULL.
• jsonb_path_exists(payload, '$.user_id') — boolean; true if any match.
• jsonb_path_match(payload, '$.total > 100') — boolean; true if the SQL/JSON predicate matches.
• These functions accept the full SQL/JSON path grammar including filter expressions and recursive descent.

MySQL 8 JSON_EXTRACT + shortcuts.

• JSON_EXTRACT(payload, '$.user.id') — returns a JSON value.
• payload -> '$.user.id' — shortcut for JSON_EXTRACT (returns JSON, keeps quotes).
• payload ->> '$.user.id' — shortcut for JSON_UNQUOTE(JSON_EXTRACT(...)) (returns text, strips quotes).
• JSON_VALUE(payload, '$.user.id') — added in MySQL 8.0.21; returns a scalar with optional type coercion (RETURNING INT).
• JSON_UNQUOTE(x) — strips the JSON string quotes; often paired with JSON_EXTRACT when you need a plain SQL string.

SQL Server JSON_VALUE vs JSON_QUERY.

• JSON_VALUE(payload, '$.user.id') — scalar. Returns NVARCHAR(4000) by default; use the optional RETURNING clause (SQL Server 2022+) for typed return.
• JSON_QUERY(payload, '$.user') — sub-tree. Returns NVARCHAR(MAX) containing valid JSON.
• Lax mode (default) — path errors return NULL. Strict mode ('strict $.user.id') — path errors throw.
• ISJSON(x) — returns 1 if x is valid JSON; use it in CHECK constraints to validate a text column stores valid JSON.
• OPENJSON(payload, '$.orders') — flattens an array to rows (see the JSON_TABLE section).

Snowflake VARIANT access.

• payload:user_id — colon-notation child access. Returns VARIANT.
• payload:user.id — chained dot after colon. Same as payload:user:id.
• payload:orders[0].total — bracket for index, dot for child.
• payload:user_id::string — cast operator forces the return type.
• GET_PATH(payload, 'user.id') — function form, useful for programmatic access.
• GET(payload, 'user_id') and TRY_PARSE_JSON(text) — helpers for defensive access and casting.

BigQuery JSON functions.

• JSON_VALUE(payload, '$.user.id') — scalar; returns STRING.
• JSON_QUERY(payload, '$.user') — sub-tree; returns STRING (JSON text) if payload is STRING, or JSON if payload is the native type.
• JSON_EXTRACT_SCALAR(payload, '$.user.id') — legacy alias for JSON_VALUE on STRING-typed columns.
• JSON_EXTRACT(payload, '$.user') — legacy alias for JSON_QUERY.
• Dot notation on native JSON: payload.user.id — reads elegantly, only works on native JSON (not STRING).

Oracle JSON access.

• JSON_VALUE(payload, '$.user.id') — scalar with RETURNING VARCHAR2(100) clause.
• JSON_QUERY(payload, '$.user') — sub-tree.
• Dot notation on JSON columns: t.payload.user.id (Oracle 12c+).
• JSON_EXISTS(payload, '$.user.id') — boolean.
• JSON_TABLE(payload, '$.orders[*]' COLUMNS ...) — the ANSI flattening operator; Oracle was the first to ship it.

When the extraction returns NULL.

• Path doesn't match — the key doesn't exist. Every dialect returns NULL in lax mode.
• Path matches but the value is null (the JSON literal null). Postgres returns SQL NULL; SQL Server returns SQL NULL; Snowflake returns SQL NULL after ::string cast; MySQL returns the JSON null which is not equal to SQL NULL (a common mysql json gotcha).
• Path matches but the value is a sub-tree (object or array) and you asked for a scalar. SQL Server returns NULL in lax mode; strict mode throws.

Type coercion after extraction.

• Every dialect returns the extracted value as text by default. To cast to a numeric type, use CAST(... AS INT) in ANSI-flavoured dialects or ::int in Postgres.
• Snowflake requires an explicit ::type cast to leave the VARIANT universe.
• BigQuery's JSON_VALUE returns STRING; use SAFE_CAST(JSON_VALUE(...) AS INT64) for safe coercion.
• MySQL 8's JSON_VALUE accepts a RETURNING clause: JSON_VALUE(payload, '$.total' RETURNING SIGNED).

Worked example — Postgres arrow chain vs SQL/JSON path

Detailed explanation. Postgres offers two paths for extraction: the arrow chain (payload->'user'->>'id') and the SQL/JSON path (jsonb_path_query_first(payload, '$.user.id')). Both produce the same result. The arrow chain is more concise; the SQL/JSON path supports filter expressions and recursive descent. Knowing when to reach for each is a senior signal.

Question. Given an events table with payload jsonb, write two equivalent Postgres queries — one using arrow chains, one using jsonb_path_query_first — that project user_id, session_id, and the total of the first order. Explain when the SQL/JSON path form wins.

Input.

id	payload
1	{"user_id": "u1", "session_id": "s1", "orders": [{"id": 1, "total": 10}]}
2	{"user_id": "u2", "session_id": "s2", "orders": [{"id": 2, "total": 30}]}

Code.

-- Arrow-chain form
SELECT
  id,
  payload ->> 'user_id'                            AS user_id,
  payload ->> 'session_id'                         AS session_id,
  (payload -> 'orders' -> 0 ->> 'total')::int      AS first_total
FROM events;

-- SQL/JSON path form
SELECT
  id,
  jsonb_path_query_first(payload, '$.user_id')       #>> '{}' AS user_id,
  jsonb_path_query_first(payload, '$.session_id')    #>> '{}' AS session_id,
  (jsonb_path_query_first(payload, '$.orders[0].total') #>> '{}')::int AS first_total
FROM events;

Step-by-step explanation.

1. payload ->> 'user_id' returns the text value of the user_id key. Concise and readable — the canonical Postgres extraction for a top-level scalar.
2. payload -> 'orders' -> 0 ->> 'total' chains: extract orders (jsonb), index into position 0 (jsonb), extract total (text). The ::int cast forces numeric.
3. jsonb_path_query_first(payload, '$.orders[0].total') returns the first match as jsonb. To convert to SQL text, chain #>> '{}' — a bit awkward but ANSI-flavoured.
4. The SQL/JSON path form wins for filter expressions: jsonb_path_query(payload, '$.orders[*] ? (@.total > 20)') returns every order with total > 20 — impossible in a pure arrow chain.
5. The SQL/JSON path form also wins for recursive descent: jsonb_path_query(payload, '$..id') returns every id anywhere in the sub-tree — again impossible in a pure arrow chain.

Output.

id	user_id	session_id	first_total
1	u1	s1	10
2	u2	s2	30

Rule of thumb. Use arrow chains for simple top-level or single-branch extractions. Reach for jsonb_path_query when you need filter predicates, recursive descent, or want the full SQL/JSON grammar (Postgres 12+).

Worked example — MySQL JSON_EXTRACT with JSON_UNQUOTE and ->>

Detailed explanation. MySQL's most-asked JSON gotcha: JSON_EXTRACT(payload, '$.user_id') returns "u1" — a JSON string with the quotes still on. To get the SQL string u1, you either wrap with JSON_UNQUOTE or use the ->> shortcut. Interviewers love this gotcha because it separates casual from careful MySQL users.

Question. Given a MySQL 8 events table with payload JSON, write two equivalent queries that project user_id as an unquoted SQL string. Show how a naive JSON_EXTRACT breaks a downstream string equality.

Input.

id	payload
1	{"user_id": "u1"}
2	{"user_id": "u2"}

Code.

-- BROKEN — quotes leak into the projected value
SELECT id, JSON_EXTRACT(payload, '$.user_id') AS user_id
FROM events;
-- Result: user_id = "u1" (a 4-char string with quotes)

-- FIXED (long form) — wrap with JSON_UNQUOTE
SELECT id, JSON_UNQUOTE(JSON_EXTRACT(payload, '$.user_id')) AS user_id
FROM events;

-- FIXED (shortcut) — the ->> operator is JSON_UNQUOTE(JSON_EXTRACT(...))
SELECT id, payload->>'$.user_id' AS user_id
FROM events;

-- ALT — MySQL 8.0.21+ ships JSON_VALUE with optional RETURNING
SELECT id, JSON_VALUE(payload, '$.user_id' RETURNING VARCHAR(64)) AS user_id
FROM events;

Step-by-step explanation.

1. JSON_EXTRACT returns a JSON value — for a string, that means the string with its quotes. Comparing JSON_EXTRACT(payload, '$.user_id') = 'u1' fails silently because "u1" (with quotes) is not equal to u1 (without).
2. JSON_UNQUOTE(JSON_EXTRACT(payload, '$.user_id')) strips the quotes and returns a plain SQL string. This is the canonical fix.
3. payload->>'$.user_id' is the syntactic shortcut for JSON_UNQUOTE(JSON_EXTRACT(payload, '$.user_id')). Reads cleaner and is the community-preferred form.
4. JSON_VALUE(payload, '$.user_id') (MySQL 8.0.21+) is a modern alternative that returns a scalar. The optional RETURNING VARCHAR(64) clause forces the return type — great when you know the target column type upfront.
5. In every case, always use the unquoted form when the extracted value is a WHERE, GROUP BY, JOIN, or ORDER BY target. The quote-leak bug is silent — the query returns 0 rows instead of erroring, which is the worst kind of bug.

Output (fixed variants).

id	user_id
1	u1
2	u2

Rule of thumb. In MySQL, always reach for ->> (or JSON_UNQUOTE(JSON_EXTRACT(...))) when the extracted value will be compared, grouped, joined, or ordered. Save JSON_EXTRACT alone for JSON sub-tree extraction where the quotes belong.

Worked example — Snowflake VARIANT chained access with casts

Detailed explanation. Snowflake's VARIANT type accepts colon notation for key access and returns another VARIANT — leaving the value untyped until you cast. The idiomatic Snowflake style is payload:user_id::string — colon for access, :: for cast. Missing the cast is one of the top three snowflake variant interview corrections.

Question. Given a Snowflake events(id, payload VARIANT) table, write a query that projects user_id as STRING, total as NUMBER, and is_paid as BOOLEAN. Explain what happens if you skip the casts.

Input.

id	payload
1	{"user_id": "u1", "total": 100, "is_paid": true}
2	{"user_id": "u2", "total": 250, "is_paid": false}

Code.

SELECT
  id,
  payload:user_id::string   AS user_id,
  payload:total::number     AS total,
  payload:is_paid::boolean  AS is_paid
FROM events;

-- Alternative — GET_PATH is the function form
SELECT
  id,
  GET_PATH(payload, 'user_id')::string  AS user_id,
  GET_PATH(payload, 'total')::number    AS total
FROM events;

Step-by-step explanation.

1. payload:user_id returns a VARIANT whose scalar content is the string "u1". Without a cast, downstream string equality (WHERE user_id = 'u1') still works because Snowflake auto-coerces, but the plan may be slower and column type semantics can surprise you.
2. payload:user_id::string casts to VARCHAR — the type shows up in the plan, downstream tools see a string column, and the plan is faster because the cast can be pushed down.
3. payload:total::number casts to NUMBER — necessary for arithmetic or numeric aggregation. Skipping the cast can produce silent JSON-to-string coercion that breaks SUM(total) in subtle ways.
4. GET_PATH(payload, 'user_id') is the function form of the colon operator. Useful for programmatic path construction; verbose for hand-written queries.
5. Every column with a materially-typed downstream consumer should be cast explicitly. The general Snowflake rule of thumb: cast at the earliest project, not the last.

Output.

id	user_id	total	is_paid
1	u1	100	true
2	u2	250	false

Rule of thumb. In Snowflake, always cast the extracted VARIANT to a concrete SQL type at the earliest projection. The ::string, ::number, ::boolean casts pay for themselves in plan quality and downstream compatibility.

Senior interview question on portable path extraction

A senior interviewer might ask: "You're building a dbt package that must run on Postgres, Snowflake, BigQuery, and SQL Server. Write a portable macro that extracts a top-level scalar key from a JSON column and returns it as a string. What's the mental split you use for the four dialects?"

Solution Using a dbt cross-dialect macro dispatch

-- macros/json_scalar.sql
{% macro json_scalar(col, key) %}
  {%- if target.type == 'postgres' or target.type == 'redshift' -%}
    ({{ col }}->>'{{ key }}')
  {%- elif target.type == 'snowflake' -%}
    ({{ col }}:{{ key }}::string)
  {%- elif target.type == 'bigquery' -%}
    (JSON_VALUE({{ col }}, '$.{{ key }}'))
  {%- elif target.type == 'sqlserver' -%}
    (JSON_VALUE({{ col }}, '$.{{ key }}'))
  {%- elif target.type == 'mysql' -%}
    (JSON_UNQUOTE(JSON_EXTRACT({{ col }}, '$.{{ key }}')))
  {%- else -%}
    {{ exceptions.raise_compiler_error('json_scalar not supported on ' ~ target.type) }}
  {%- endif -%}
{% endmacro %}

-- usage in a model
SELECT
  id,
  {{ json_scalar('payload', 'user_id') }} AS user_id,
  {{ json_scalar('payload', 'session_id') }} AS session_id
FROM {{ ref('events') }};

Step-by-step trace.

Adapter	Rendered expression
postgres	(payload->>'user_id')
snowflake	(payload:user_id::string)
bigquery	(JSON_VALUE(payload, '$.user_id'))
sqlserver	(JSON_VALUE(payload, '$.user_id'))
mysql	(JSON_UNQUOTE(JSON_EXTRACT(payload, '$.user_id')))

Once the macro is defined, every model that reads a JSON column calls {{ json_scalar('payload', 'user_id') }} and the compiled SQL is dialect-correct without manual edits. The macro is the analytics-engineer's answer to the JSON dialect fracture.

Output:

Dialect	Portability outcome
postgres	Uses the arrow shortcut; identical plan to jsonb_extract_path_text
snowflake	Uses colon notation + cast; plan-eligible for VARIANT columnar prune
bigquery	Uses JSON_VALUE; native on JSON type, works on STRING too
sqlserver	Uses JSON_VALUE; scalar-only, returns NVARCHAR(4000) default
mysql	Uses JSON_UNQUOTE(JSON_EXTRACT(...)); safe for equality and GROUP BY

Why this works — concept by concept:

• Adapter-dispatch is the portable pattern — every warehouse has a different function for the same concept, but dbt's target.type variable lets you pick the right one at compile time. The compiled SQL is dialect-native; only the source is portable.
• Compile-time dispatch beats runtime abstraction — you don't want a runtime CASE that picks the syntax; the query planner would see all branches. Compile-time dispatch produces one clean SQL statement per target.
• Scalar-vs-sub-tree matters even in the macro — the macro name json_scalar documents that the output is a scalar. A separate json_query macro handles sub-tree extraction. Never mix the two.
• Fallback error — the else branch raises a compiler error for unsupported adapters. Better to fail loudly at compile time than to silently produce wrong SQL for an unknown adapter.
• Cost — the macro is compile-time only; there is zero query-time overhead. Every dialect gets its native fast path. This is the analytics-engineer's answer to the JSON dialect fracture.

SQL
Topic — JSON
JSON path extraction problems

Practice →

SQL Topic — string parsing String parsing and extraction drills

Practice →

---

3. JSONB in Postgres — index it or regret it

jsonb postgres is the binary, deduped, indexable JSON type — pair it with a GIN index on the containment operator or every query becomes a full scan

The mental model in one line: Postgres ships two JSON types (json and jsonb) with subtly different semantics; only jsonb is indexable; the canonical index is a GIN with either jsonb_ops (universal) or jsonb_path_ops (containment-only, ~30% smaller); and the @> containment operator is the JSON equivalent of = ANY(...) that every senior data engineer knows cold. Once you say "jsonb + GIN + @> = fast JSON queries in Postgres," the interview surface collapses to a recipe list.

jsonb vs json in Postgres.

• json — raw text storage. Preserves whitespace, key order, and duplicates. No parsing on read (which is cheap on write, expensive on read). No indexing.
• jsonb — binary parsed storage. Keys are deduped, whitespace is dropped, key order is not preserved. Parsing happens once on insert; every read is cheap. Indexable via GIN.
• Storage: jsonb is typically ~5-15% smaller than the equivalent json (dedup + binary encoding beat text).
• Write cost: jsonb is ~20-40% slower on insert because parsing happens up-front. Usually a great trade if the row is read many times.
• Query cost: jsonb is dramatically faster on any read that navigates the sub-tree (arrow operators, @>, ?).

When to use json (rare).

• You need to preserve key order or whitespace (RFC 7396 patches, canonical form for signing).
• You never query on the JSON content, only pass it through.
• You measured and confirmed the write cost dominates the workload.
• For 99% of cases, jsonb is the right default.

GIN operator classes.

• jsonb_ops — DEFAULT. Indexes every key AND every value. Supports @>, ?, ?&, ?|, and the SQL/JSON path operators @?, @@.
• jsonb_path_ops — SMALLER. Indexes only @> containment. Doesn't support ? existence. ~30% smaller and faster to build; picks the right choice for 90% of production containment workloads.
• Choose jsonb_path_ops when your read pattern is 100% containment (WHERE payload @> '{"status": "active"}').
• Choose jsonb_ops when you also need key-existence queries (WHERE payload ? 'user_id').

GIN index recipe.

-- Universal — supports @>, ?, ?&, ?|
CREATE INDEX ix_events_payload_gin
  ON events USING GIN (payload);

-- Containment-only — smaller, faster to build
CREATE INDEX ix_events_payload_gin_path
  ON events USING GIN (payload jsonb_path_ops);

The @> containment operator.

• payload @> '{"status": "active"}' — true if payload contains the given JSON as a sub-object.
• Works recursively: payload @> '{"user": {"id": "u1"}}' matches any payload with a user sub-object containing id: u1.
• Works for arrays: payload @> '{"tags": ["premium"]}' matches any payload where tags contains premium (among other elements).
• GIN-indexable, unlike most JSON extraction predicates.
• The canonical WHERE clause for JSON filtering in Postgres.

Existence operators (jsonb_ops only).

• payload ? 'user_id' — true if payload has a top-level key user_id.
• payload ?& array['user_id','ts'] — true if all listed keys exist.
• payload ?| array['a','b'] — true if any listed key exists.

SQL/JSON path operators (Postgres 12+).

• payload @? '$.user.id' — true if any value matches the path (existence check).
• payload @@ '$.total > 100' — true if the SQL/JSON predicate matches.
• Both are GIN-indexable via jsonb_ops.

jsonb_path_query for extraction with predicates.

• jsonb_path_query(payload, '$.orders[*] ? (@.total > 100)') — returns every order with total > 100.
• Set-returning; useful in LATERAL joins for row expansion.
• Predicate expressions inside ? (...) — the SQL/JSON standard filter form.

jsonb_set and || for updates.

• UPDATE events SET payload = jsonb_set(payload, '{status}', '"paid"') WHERE id = 1; — set a nested key.
• UPDATE events SET payload = payload || '{"reviewed": true}'; — merge two objects (right side wins on key collision).
• UPDATE events SET payload = payload - 'temp_field'; — delete a top-level key.
• UPDATE events SET payload = payload #- '{user,temp}'; — delete a nested key.

Functional expression indexes for a single hot path.

-- Index a single extracted value — great for one hot filter
CREATE INDEX ix_events_user_id
  ON events ((payload->>'user_id'));

• Use this when you only ever filter on one key. Cheaper than GIN, faster to plan.
• The trade-off: only supports the exact expression indexed. WHERE payload->>'user_id' = 'u1' uses it; WHERE payload @> '{"user_id": "u1"}' does not.

Generated column materialisation.

-- Materialise a stable field as a plain column
ALTER TABLE events
  ADD COLUMN user_id text GENERATED ALWAYS AS (payload->>'user_id') STORED;

CREATE INDEX ix_events_user_id ON events (user_id);

• The generated column stays in sync with the JSON automatically.
• Query planner sees a plain column and picks the BTREE index without any expression magic.
• Best pattern when the extracted field is a hot join key, group-by key, or sort key.

When plain TEXT beats jsonb.

• The JSON is never queried, only passed through.
• The write throughput is critical and the parse cost matters more than the read cost.
• You need bit-identical JSON preservation (whitespace, key order, duplicates).
• All three cases are rare; jsonb is the safe default.

Common jsonb postgres interview probes.

• "When would you use json over jsonb?" — almost never; only when key order or whitespace preservation matters.
• "Which GIN operator class supports the existence operator ??" — jsonb_ops (default); jsonb_path_ops does not.
• "What's the containment operator, and why is it interesting?" — @>; the JSON equivalent of = ANY(...), GIN-indexable, the fastest filter in Postgres JSON.
• "How would you index a single hot filter key without a GIN?" — a functional expression index on (payload->>'key') or a generated column with a BTREE.
• "What does jsonb_set do that || doesn't?" — jsonb_set writes to a nested path; || only merges top-level keys.

Worked example — GIN index + containment filter for event routing

Detailed explanation. The most common Postgres JSON workload: an events table with 100M rows, filtered by an event-type-specific containment predicate (WHERE payload @> '{"kind": "purchase"}'). Without a GIN, this is a full scan. With a GIN, it's milliseconds.

Question. Given events(id, ts, payload jsonb) with 100M rows, write the DDL to add a GIN index on payload and the query that filters to purchase events. Explain the plan difference before and after the index.

Input.

id	ts	payload
1	2026-07-01	{"kind": "click", "user_id": "u1"}
2	2026-07-01	{"kind": "purchase", "user_id": "u1", "total": 100}
3	2026-07-01	{"kind": "view", "user_id": "u2"}

Code.

-- DDL — add a containment-only GIN for smaller footprint
CREATE INDEX CONCURRENTLY ix_events_payload_gin
  ON events USING GIN (payload jsonb_path_ops);

-- Query — filter to purchase events
SELECT id, ts, payload->>'user_id' AS user_id, (payload->>'total')::int AS total
FROM events
WHERE payload @> '{"kind": "purchase"}';

-- Before the index: Seq Scan on events (cost=0.00..3000000.00)
-- After the index:  Bitmap Heap Scan on events (cost=100.00..500.00)
--                     -> Bitmap Index Scan on ix_events_payload_gin (cost=0.00..100.00)

Step-by-step explanation.

1. CREATE INDEX CONCURRENTLY builds the GIN without blocking writes. On a 100M-row table, this takes minutes to hours; run it during low-load windows.
2. The jsonb_path_ops operator class is ~30% smaller than the default jsonb_ops and supports only @>. That's the right trade for this workload — we only filter with containment.
3. payload @> '{"kind": "purchase"}' is the containment predicate. The GIN maps every JSON path fragment to the row ids that contain it; the planner uses a bitmap of matching rows.
4. Plan before the index: sequential scan reads all 100M rows, evaluates the predicate on each. Cost is millions of tuple reads.
5. Plan after the index: bitmap index scan produces a set of ~100k candidate row ids (assuming 0.1% are purchases), then a bitmap heap scan visits each. Cost is 3-4 orders of magnitude lower.

Output.

id	ts	user_id	total
2	2026-07-01	u1	100

Rule of thumb. For any Postgres JSON column that will be queried by containment, add a GIN with jsonb_path_ops as the default. Upgrade to jsonb_ops only when you also need existence (?, ?&, ?|) operators.

Worked example — functional expression index for a single hot key

Detailed explanation. When the read pattern is 100% on one extracted key — say WHERE payload->>'user_id' = 'u1' — a full GIN is overkill. A functional expression index on the extraction is smaller and faster. Interviewers love this micro-optimisation because it separates "reads docs" from "reads plans."

Question. Given events(id, payload jsonb) and a workload that only filters on payload->>'user_id', write the DDL for a functional expression index and confirm it beats a GIN for this specific query.

Input.

id	payload
1	{"user_id": "u1", "kind": "click"}
2	{"user_id": "u2", "kind": "purchase"}
3	{"user_id": "u1", "kind": "view"}

Code.

-- Functional expression index on the extracted key
CREATE INDEX ix_events_user_id
  ON events ((payload->>'user_id'));

-- Query — uses the functional index
SELECT id, payload->>'kind' AS kind
FROM events
WHERE payload->>'user_id' = 'u1';

-- Plan: Index Scan using ix_events_user_id (cost=0.42..8.44 rows=2)

Step-by-step explanation.

1. The functional expression index stores payload->>'user_id' for every row and points at the row id. It's a BTREE, so range scans and equality both work at BTREE speed.
2. The index is much smaller than a GIN because it stores exactly one key per row (versus GIN's many entries per row for every JSON path fragment).
3. The query planner recognises the exact expression payload->>'user_id' in the WHERE clause and picks the functional index automatically. If you write the WHERE clause differently (e.g., payload @> '{"user_id": "u1"}'), the functional index does NOT match — a common gotcha.
4. Trade-off: this index only supports the one hot expression. If your workload later adds a second predicate (WHERE payload->>'session_id' = 's1'), you'd need a second functional index, or fall back to a GIN.
5. In practice, the choice comes down to workload cardinality. If you have 1-2 hot keys, use functional indexes. If you have 5+ query patterns, use a GIN.

Output.

id	kind
1	click
3	view

Rule of thumb. For a workload with one or two hot extraction keys, functional expression indexes beat GIN on size, build time, and plan quality. For a workload with many extraction patterns or containment queries, GIN wins.

Worked example — generated column + BTREE for a stable join key

Detailed explanation. When a JSON key is a stable join target (present in every row, drives the query planner's join order), materialising it as a generated column with a BTREE is the fastest option. This is Postgres 12+ and is the analytics-engineer's canonical answer for hot join keys.

Question. Given events(id, payload jsonb) where payload->>'user_id' is a hot join key against users(id), write the DDL to materialise user_id as a generated column with a BTREE. Show a JOIN query that benefits.

Input.

id	payload
1	{"user_id": "u1", "kind": "click"}
2	{"user_id": "u2", "kind": "purchase"}
3	{"user_id": "u1", "kind": "view"}

Code.

-- Materialise the join key
ALTER TABLE events
  ADD COLUMN user_id text GENERATED ALWAYS AS (payload->>'user_id') STORED;

CREATE INDEX ix_events_user_id ON events (user_id);

-- JOIN — planner sees a plain column
SELECT e.id, u.name, e.payload->>'kind' AS kind
FROM events e
JOIN users u ON u.id = e.user_id;

Step-by-step explanation.

1. GENERATED ALWAYS AS (payload->>'user_id') STORED creates a physical column that materialises the extraction. Every insert or update to payload re-evaluates the expression and stores the result.
2. The STORED keyword is required (Postgres only supports STORED, not VIRTUAL). It pays a small write cost and gives you a full-fidelity column at query time.
3. A plain BTREE index on user_id behaves exactly like any BTREE. The planner sees a normal column and picks the index for equality, range, and sort operations without any expression magic.
4. The JOIN ON u.id = e.user_id is a plain equi-join on two indexed columns. The planner picks a hash join or nested-loop join depending on cardinality — either way, it's optimal.
5. The trade-off is disk usage: the extracted value is stored twice (once in JSON, once in the generated column). For hot join keys, this is almost always worth it.

Output.

id	name	kind
1	Ada	click
2	Bo	purchase
3	Ada	view

Rule of thumb. For any JSON key that's stable, always present, and on a hot join / group-by / sort path, materialise it as a GENERATED ALWAYS AS (...) STORED column with a plain BTREE. This is the fastest pattern in Postgres 12+ and the analytics-engineer's default answer.

Senior interview question on Postgres JSONB tuning

A senior interviewer might ask: "You've inherited a Postgres table events(id, ts, payload jsonb) with 500M rows. The team has one query pattern: WHERE payload @> '{"kind": "..."}' AND ts > NOW() - INTERVAL '7 days'. Walk me through your indexing plan and why."

Solution Using a composite plan of BTREE + GIN

-- Step 1 — BTREE on the time dimension (partition-eligible)
CREATE INDEX ix_events_ts
  ON events (ts DESC);

-- Step 2 — GIN on the JSON payload for containment
CREATE INDEX CONCURRENTLY ix_events_payload_gin
  ON events USING GIN (payload jsonb_path_ops);

-- Step 3 — optional partial index for the hot event types
CREATE INDEX ix_events_purchase
  ON events (ts DESC)
  WHERE payload @> '{"kind": "purchase"}';

-- Step 4 — analyze so the planner has fresh stats
ANALYZE events;

Step-by-step trace.

Step	What it does	Why it matters
1	BTREE on ts DESC	Fast time-window pruning; enables merge/append/index scans
2	GIN(jsonb_path_ops) on payload	Fast containment; planner combines with BTREE via BitmapAnd
3	Partial BTREE on ts DESC WHERE payload @> ...	Ultra-fast hot-event scans; skips containment entirely
4	ANALYZE	Refreshes stats so the planner picks the right combination

The composite plan uses BTREE for time and GIN for containment; the planner combines the two via a BitmapAnd. For the ultra-hot event kinds where 90% of queries hit, the partial BTREE eliminates the containment step entirely.

Output:

Query pattern	Plan the planner picks
ts > NOW() - 7 days	BitmapAnd(BTREE(ts), GIN(payload)) — fastest general case
payload @> '{"kind":"purchase"}' AND ts > ...	Partial BTREE — skips containment
payload @> '{"kind":"rare"}' AND ts > ...	BitmapAnd — general case

Why this works — concept by concept:

• BTREE on the time dimension — every OLTP + analytics workload with a ts column benefits from a descending BTREE. The DESC ordering matches the common "latest N days" pattern; the planner picks it for range scans and merge joins.
• GIN on the JSON payload — the jsonb_path_ops operator class supports only @>, but that's the entire containment surface. Storing exactly the containment fragments makes the index ~30% smaller and faster than the default.
• Partial indexes for hot filters — when 90% of queries hit one event kind, a partial index that indexes only those rows is dramatically smaller than a full index. The planner picks it automatically when the WHERE clause matches the partial's predicate.
• BitmapAnd combines multiple indexes — Postgres's planner can intersect BTREE and GIN bitmaps to find rows that satisfy both predicates. This is why you don't need one giant multi-column index; two smaller indexes plus BitmapAnd is often faster.
• Cost — the composite plan is O(log N) for the BTREE + O(log N) for the GIN. On a 500M row table, that's tens of thousands of index reads instead of 500M sequential scans — a 4-5 order-of-magnitude speedup.

SQL
Topic — JSON
JSONB indexing and containment drills

Practice →

SQL Topic — data manipulation Data manipulation problems

Practice →

---

4. JSON_TABLE — flatten JSON to rows

json_table sql is the ANSI SQL/JSON operator that turns a nested array into rows — Snowflake reaches for LATERAL FLATTEN, BigQuery for UNNEST(JSON_QUERY_ARRAY(...)), Postgres for jsonb_to_recordset

The mental model in one line: a nested JSON array cannot be joined against a relational query without a flattening operator that emits one row per array element, and every warehouse ships a different keyword for the same operation. Once you say "ANSI = JSON_TABLE, Snowflake = LATERAL FLATTEN, BigQuery = UNNEST(JSON_QUERY_ARRAY), Postgres = jsonb_array_elements / jsonb_to_recordset," the flattening interview surface reduces to a syntax lookup.

ANSI JSON_TABLE — the standard.

• Shipped by Oracle (first, 12c), MySQL 8.0.4+, SQL Server 2022+, IBM Db2, and PostgreSQL 17+.
• Syntax: JSON_TABLE(payload, '$.orders[*]' COLUMNS (id INT PATH '$.id', total DECIMAL(10,2) PATH '$.total')) AS orders.
• Reads: "for each element of $.orders[*], emit a row whose id column comes from $.id and total column comes from $.total (relative to the array element)."
• Used in a FROM clause with a LATERAL semantic (implicit) — the flattening is per-row.
• Supports NESTED PATH sub-blocks for deeper flattening (see below).
• Supports ERROR / NULL / DEFAULT ... ON EMPTY and ERROR / NULL / DEFAULT ... ON ERROR for missing/invalid values.

Snowflake LATERAL FLATTEN — the flagship for VARIANT.

• Syntax: SELECT e.id, o.value:id::int AS order_id, o.value:total::number AS total FROM events e, LATERAL FLATTEN(input => e.payload:orders) o.
• FLATTEN returns a table with columns SEQ, KEY, PATH, INDEX, VALUE, THIS. The interesting one is VALUE (the element).
• Add OUTER => true to preserve rows where the flattened array is empty or NULL (like a LEFT JOIN).
• Add RECURSIVE => true to recurse into nested objects/arrays automatically.
• Great for semi-structured Snowflake VARIANT columns; the canonical Snowflake pattern.

BigQuery UNNEST(JSON_QUERY_ARRAY(...)).

• Syntax: SELECT e.id, JSON_VALUE(o, '$.id') AS order_id, JSON_VALUE(o, '$.total') AS total FROM events e, UNNEST(JSON_QUERY_ARRAY(e.payload, '$.orders')) AS o.
• JSON_QUERY_ARRAY returns an ARRAY<STRING> (or ARRAY<JSON> for native JSON) of the array elements.
• UNNEST flattens the array to rows in a FROM clause.
• Per-element extraction still uses JSON_VALUE / JSON_QUERY on the unnested element.
• BigQuery also supports JSON_EXTRACT_ARRAY as a legacy alias for JSON_QUERY_ARRAY.

Postgres flattening options.

• jsonb_array_elements(payload -> 'orders') — set-returning; emits one row per array element, each as jsonb. Used in a LATERAL join.
• jsonb_to_recordset(payload -> 'orders') — set-returning; emits typed rows given a column list. SELECT * FROM jsonb_to_recordset(payload -> 'orders') AS o(id int, total numeric).
• jsonb_array_elements_text(payload -> 'tags') — for string arrays; emits text.
• Postgres 17+ ships full ANSI JSON_TABLE on jsonb columns.

Nested arrays — NESTED PATH.

• ANSI JSON_TABLE supports a NESTED PATH '$.sub_array[*]' COLUMNS (...) sub-block for deeper flattening.
• Reads: "for each element of the outer array, also flatten the inner array named sub_array, and emit one row per (outer, inner) pair."
• Great for hierarchical payloads like {"user": "u1", "sessions": [{"session_id": "s1", "events": [{"kind": "click"}, {"kind": "view"}]}]}.

Null handling clauses.

• NULL ON EMPTY — the default; missing paths produce NULL values in the output.
• ERROR ON EMPTY — throw an error when the path doesn't match. Useful in strict-mode ETLs where a missing key is a bug.
• DEFAULT 'x' ON EMPTY — substitute a default value.
• ERROR ON ERROR — throw if the extracted value can't be coerced to the declared column type. Default is NULL ON ERROR.

When to flatten vs when to keep in JSON.

• Flatten when the array elements feed downstream joins, aggregates, or filters that would benefit from a relational shape.
• Keep in JSON when the array is a read-only projection that ships back to the client.
• Never flatten a large array eagerly if the query only needs a few elements — use JSON_EXTRACT or jsonb_path_query with a predicate to pull only what you need.

Common json_table sql interview probes.

• "Which dialects ship ANSI JSON_TABLE?" — Oracle, MySQL 8.0.4+, SQL Server 2022+, PostgreSQL 17+.
• "What's the Snowflake equivalent?" — LATERAL FLATTEN(input => col).
• "What's the BigQuery equivalent?" — UNNEST(JSON_QUERY_ARRAY(col, '$.path')).
• "How would you handle a doubly-nested array?" — NESTED PATH sub-block in ANSI; chained LATERAL FLATTEN in Snowflake; chained UNNEST in BigQuery; chained LATERAL jsonb_array_elements in Postgres.
• "What's the difference between JSON_TABLE and OPENJSON?" — SQL Server ships both; OPENJSON is Microsoft's older non-standard flattener, JSON_TABLE is the ANSI one (2022+). Prefer JSON_TABLE on new work.

Worked example — SQL Server JSON_TABLE for a nested orders array

Detailed explanation. The canonical ANSI flattening ask: given an events table where each row has a payload with a nested orders array, produce one row per (event, order) pair with typed columns. SQL Server 2022+ ships JSON_TABLE; older versions use OPENJSON.

Question. Given events(id, payload NVARCHAR(MAX)) where payload contains {"user": "u1", "orders": [{"id": 1, "total": 10}, {"id": 2, "total": 25}]}, write a SQL Server 2022 JSON_TABLE that produces (event_id, user, order_id, total). Explain the PATH clause and the COLUMNS list.

Input.

id	payload
100	{"user": "u1", "orders": [{"id": 1, "total": 10}, {"id": 2, "total": 25}]}
101	{"user": "u2", "orders": [{"id": 3, "total": 40}]}

Code.

SELECT
  e.id      AS event_id,
  JSON_VALUE(e.payload, '$.user') AS user,
  o.order_id,
  o.total
FROM events e
CROSS APPLY JSON_TABLE(
  e.payload,
  '$.orders[*]'
  COLUMNS (
    order_id INT           PATH '$.id',
    total    DECIMAL(10,2) PATH '$.total'
  )
) AS o;

Step-by-step explanation.

1. JSON_TABLE(e.payload, '$.orders[*]' COLUMNS (...)) reads: "for each element of $.orders[*] inside e.payload, emit a row with the declared columns."
2. The row-generating path $.orders[*] selects every element of the orders array. Each element becomes one output row.
3. Inside COLUMNS (...), each column has a name, a SQL type, and a PATH relative to the current array element. order_id INT PATH '$.id' pulls .id from the element, coerces to INT.
4. CROSS APPLY (SQL Server keyword) makes JSON_TABLE behave like a per-row function — for each event, the flattener runs against that event's payload.
5. The outer SELECT projects both the event columns (via e. alias) and the flattened columns (via o. alias). The result is one row per (event, order) pair.

Output.

event_id	user	order_id	total
100	u1	1	10.00
100	u1	2	25.00
101	u2	3	40.00

Rule of thumb. For any SQL Server 2022+ flattening task, prefer JSON_TABLE over OPENJSON — it's ANSI, portable to Oracle / MySQL / PostgreSQL 17+, and reads more clearly. Keep OPENJSON for legacy SQL Server 2017-2019 codebases.

Worked example — Snowflake LATERAL FLATTEN for VARIANT arrays

Detailed explanation. The canonical Snowflake flattening ask: given an events table with a VARIANT payload containing a nested orders array, produce one row per (event, order) pair. LATERAL FLATTEN is the flagship Snowflake pattern.

Question. Given a Snowflake events(id, payload VARIANT) table where each payload has an orders array, write a query using LATERAL FLATTEN that produces (event_id, user, order_id, total). Explain the input => argument and the OUTER => true option.

Input.

id	payload
100	{"user": "u1", "orders": [{"id": 1, "total": 10}, {"id": 2, "total": 25}]}
101	{"user": "u2", "orders": []}

Code.

-- INNER — drops rows where the array is empty or NULL
SELECT
  e.id                    AS event_id,
  e.payload:user::string  AS user,
  o.value:id::int         AS order_id,
  o.value:total::number   AS total
FROM events e,
     LATERAL FLATTEN(input => e.payload:orders) o;

-- OUTER — preserves rows with empty arrays (order_id / total NULL)
SELECT
  e.id                    AS event_id,
  e.payload:user::string  AS user,
  o.value:id::int         AS order_id,
  o.value:total::number   AS total
FROM events e,
     LATERAL FLATTEN(input => e.payload:orders, outer => true) o;

Step-by-step explanation.

1. LATERAL FLATTEN(input => e.payload:orders) reads: "for each event, flatten the orders array into rows." The input => argument is the array to flatten.
2. Each output row from FLATTEN has columns SEQ, KEY, PATH, INDEX, VALUE, THIS. VALUE is the array element as VARIANT; that's what you extract from.
3. o.value:id::int navigates into the element with colon-notation and casts to INT. Same colon syntax as any Snowflake VARIANT access.
4. The default is an inner join — events with empty orders arrays disappear from the output. To preserve them (like a LEFT JOIN), add outer => true.
5. LATERAL before FLATTEN is Snowflake syntax for "correlate this table function with the current outer row." Without LATERAL, FLATTEN can't reference e.payload.

Output (INNER variant).

event_id	user	order_id	total
100	u1	1	10
100	u1	2	25

Output (OUTER variant).

event_id	user	order_id	total
100	u1	1	10
100	u1	2	25
101	u2	NULL	NULL

Rule of thumb. In Snowflake, LATERAL FLATTEN is the default answer for any VARIANT flattening. Use outer => true when the outer row should survive empty arrays; use recursive => true when you want to explode nested sub-objects automatically.

Worked example — BigQuery UNNEST(JSON_QUERY_ARRAY) with per-element extraction

Detailed explanation. BigQuery's flattening pattern: use JSON_QUERY_ARRAY to get the array as an ARRAY<STRING> (or ARRAY<JSON> for native JSON), then UNNEST it in the FROM clause. Per-element extraction uses JSON_VALUE on the unnested element.

Question. Given a BigQuery events(id STRING, payload JSON) table where each payload has an orders array, write a query that produces (event_id, user, order_id, total). Show both the native-JSON form and the STRING-of-JSON form.

Input.

id	payload
100	{"user": "u1", "orders": [{"id": 1, "total": 10}, {"id": 2, "total": 25}]}
101	{"user": "u2", "orders": [{"id": 3, "total": 40}]}

Code.

-- Native JSON type
SELECT
  e.id                          AS event_id,
  JSON_VALUE(e.payload, '$.user') AS user,
  JSON_VALUE(o, '$.id')         AS order_id,
  JSON_VALUE(o, '$.total')      AS total
FROM events e,
     UNNEST(JSON_QUERY_ARRAY(e.payload, '$.orders')) AS o;

-- STRING-of-JSON — same shape, legacy alias
SELECT
  e.id                                 AS event_id,
  JSON_EXTRACT_SCALAR(e.payload, '$.user') AS user,
  JSON_EXTRACT_SCALAR(o, '$.id')       AS order_id,
  JSON_EXTRACT_SCALAR(o, '$.total')    AS total
FROM events e,
     UNNEST(JSON_EXTRACT_ARRAY(e.payload, '$.orders')) AS o;

Step-by-step explanation.

1. JSON_QUERY_ARRAY(e.payload, '$.orders') returns an ARRAY<JSON> (native) or ARRAY<STRING> (legacy) containing the array elements.
2. UNNEST(array) flattens the array to rows in a FROM clause. Each row is one element, aliased as o.
3. The implicit correlation between events e and UNNEST is what BigQuery calls a "correlated cross join" — it runs the UNNEST per outer row automatically. No LATERAL keyword needed.
4. Per-element extraction uses JSON_VALUE(o, '$.id') on the unnested element. Because the element is native JSON, we still need to path into it.
5. The STRING-of-JSON path (JSON_EXTRACT_ARRAY + JSON_EXTRACT_SCALAR) is the legacy form for STRING-typed columns. Use the native JSON path for new work; it has better plan characteristics on BigQuery's columnar engine.

Output.

event_id	user	order_id	total
100	u1	1	10
100	u1	2	25
101	u2	3	40

Rule of thumb. For BigQuery, always prefer native JSON columns + JSON_VALUE / JSON_QUERY_ARRAY on new work. Only use STRING-of-JSON + JSON_EXTRACT_SCALAR when you're stuck with a legacy schema — the migration cost is often worth the query-plan improvement.

Worked example — Postgres jsonb_to_recordset for typed flattening

Detailed explanation. Postgres's jsonb_to_recordset is the concise cousin of ANSI JSON_TABLE — it emits typed rows given a column list, without the PATH clauses. Great when the array elements are flat objects with a known schema.

Question. Given a Postgres events(id int, payload jsonb) table with a nested orders array of flat objects, write a query using jsonb_to_recordset that produces (event_id, user, order_id, total).

Input.

id	payload
100	{"user": "u1", "orders": [{"id": 1, "total": 10}, {"id": 2, "total": 25}]}
101	{"user": "u2", "orders": [{"id": 3, "total": 40}]}

Code.

-- jsonb_to_recordset — typed rows, no PATH clauses
SELECT
  e.id                      AS event_id,
  e.payload ->> 'user'      AS user,
  o.id                      AS order_id,
  o.total
FROM events e,
     LATERAL jsonb_to_recordset(e.payload -> 'orders') AS o(id int, total numeric);

Step-by-step explanation.

1. jsonb_to_recordset(jsonb_array) is a set-returning function that emits one row per array element. Each element must be a flat object whose keys match the requested column names.
2. The AS o(id int, total numeric) clause names the columns and their types. Postgres reads the JSON object per element and maps id → id int, total → total numeric.
3. LATERAL allows the function to reference e.payload — the outer row. Without LATERAL, Postgres can't correlate.
4. The outer SELECT projects both event columns (e.) and per-order columns (o.). The result is one row per (event, order) pair.
5. jsonb_to_recordset requires flat objects — nested keys don't work. For nested arrays or nested objects, fall back to jsonb_array_elements + LATERAL or PostgreSQL 17's ANSI JSON_TABLE.

Output.

event_id	user	order_id	total
100	u1	1	10
100	u1	2	25
101	u2	3	40

Rule of thumb. For Postgres < 17, jsonb_to_recordset is the concise pattern when the array elements are flat. For nested arrays, chain LATERAL jsonb_array_elements. For Postgres 17+, JSON_TABLE is the ANSI answer and the future default.

Senior interview question on nested-array flattening

A senior interviewer might ask: "You have events with a doubly-nested payload: each session has a list of events, and each event has a list of tags. Write a portable flattener across ANSI dialects that produces one row per (session, event, tag) — and explain why the flat product matters."

Solution Using NESTED PATH with JSON_TABLE

-- ANSI JSON_TABLE with NESTED PATH — Oracle, MySQL 8, SQL Server 2022, Postgres 17
SELECT
  s.id            AS session_id,
  e.event_id,
  e.event_kind,
  t.tag
FROM sessions s
CROSS APPLY JSON_TABLE(
  s.payload,
  '$.events[*]'
  COLUMNS (
    event_id   INT       PATH '$.id',
    event_kind VARCHAR(32) PATH '$.kind',
    NESTED PATH '$.tags[*]'
      COLUMNS (tag VARCHAR(64) PATH '$')
  )
) AS e
JOIN tags_registry t ON t.tag = e.tag;
-- CROSS APPLY == LATERAL in ANSI dialects; syntax varies per engine.

Step-by-step trace.

Step	What runs	Cardinality
1	Outer sessions scan	N sessions
2	Row-generator path $.events[*] fires per session	N × avg(events/session)
3	NESTED PATH '$.tags[*]' fires per event	N × avg(events) × avg(tags)
4	Each (event, tag) pair emits a row	Cartesian per level
5	Outer join to tags_registry filters/joins tags	Final result set

The nested-path form emits one row per leaf (session, event, tag) tuple. Every nesting level multiplies the cardinality — this is the "flat product" and is exactly what a relational join wants.

Output:

session_id	event_id	event_kind	tag
s1	1	click	premium
s1	1	click	mobile
s1	2	view	premium
s2	3	purchase	premium
s2	3	purchase	high-value

Why this works — concept by concept:

• NESTED PATH nests inside COLUMNS — the outer PATH '$.events[*]' generates one row per event; the inner NESTED PATH '$.tags[*]' generates one row per (event, tag) pair. The two combine into the Cartesian product per session.
• PATH '$' refers to the current scalar — inside NESTED PATH, the column mapping tag VARCHAR(64) PATH '$' reads: "the current tag element is a scalar; take its value as-is." Useful for arrays of strings, not objects.
• Cartesian expansion is desired — a row per leaf tuple is what enables relational joins downstream. Every session, every event, every tag becomes one row that can join, filter, aggregate.
• NULL handling on empty nested arrays — the default is NULL ON EMPTY; sessions with no tags still emit event rows with tag = NULL. Add ERROR ON EMPTY if you want strict schemas.
• Cost — cost is O(sessions × avg(events) × avg(tags)). For big multipliers, this can be a lot of rows — the interviewer expects you to recognise the cardinality risk and consider filtering before flattening.

SQL
Topic — data transformation
Data transformation problems

Practice →

SQL Topic — JSON JSON flattening drills

Practice →

---

5. Dialect matrix + performance

The five-engine JSON matrix: bigquery json vs snowflake variant vs Postgres jsonb vs SQL Server JSON vs MySQL JSON — storage, index, and pruning tell the whole story

The mental model in one line: every engine has a JSON storage type, an index shape, and a pruning story — Postgres pairs jsonb with GIN, MySQL pairs JSON with a functional index on a generated column, SQL Server pairs NVARCHAR(MAX) with a computed column + index, Snowflake pairs VARIANT with automatic clustering + micro-partition prune, BigQuery pairs the native JSON type with partitioning + clustering on a materialised column, and the interviewer wants to see you name the pair for each dialect. Once you say the pair out loud, the performance interview surface reduces to a lookup.

Storage cost across five engines.

• Postgres jsonb — binary encoded, deduped keys. Typically 5-15% smaller than raw JSON. Fixed per-row overhead of ~4 bytes; body scales with content.
• Postgres json — raw text. Ordered, no dedup. ~10-15% larger than jsonb.
• MySQL JSON — binary encoded, similar dedup story to jsonb. Slightly larger than jsonb due to different binary format.
• SQL Server — no dedicated JSON type; stored as NVARCHAR(MAX). Raw text. ~2× larger than jsonb (Unicode) unless compressed.
• Snowflake VARIANT — columnar-encoded semi-structured storage. Each JSON path becomes a virtual sub-column; storage is per-path columnar. Typically 20-50% smaller than raw JSON due to columnar compression.
• BigQuery native JSON — columnar storage similar to Snowflake VARIANT. Per-path pruning at query time. Much smaller than STRING-of-JSON.

Index strategies.

• Postgres jsonb + GIN — the canonical pair. jsonb_path_ops for containment-only; jsonb_ops for the full operator set.
• Postgres jsonb + functional expression index — for one hot path: CREATE INDEX ON events ((payload->>'user_id')).
• Postgres jsonb + generated column + BTREE — the analytics-engineer's pattern; materialise the extraction, index the column.
• MySQL JSON + generated column + BTREE — MySQL doesn't support functional indexes on JSON expressions; the workaround is a GENERATED ... VIRTUAL / STORED column + a normal BTREE.
• SQL Server + computed column + index — ALTER TABLE events ADD user_id AS JSON_VALUE(payload, '$.user_id') PERSISTED; CREATE INDEX ix_user_id ON events(user_id);.
• Snowflake VARIANT + automatic clustering — Snowflake clusters micro-partitions on any JSON path automatically; no explicit index required. ALTER TABLE events CLUSTER BY (payload:user_id::string); for explicit clustering.
• BigQuery + partitioning + clustering — partition by ts, cluster by a materialised JSON extraction. Native JSON columns support direct clustering on JSON_VALUE(payload, '$.user_id').

Pruning stories per engine.

• Postgres — GIN doesn't prune whole pages; it produces a bitmap of candidate rows. BTREE indexes on materialised columns prune at the page level.
• MySQL — BTREE on a generated column prunes pages at BTREE speed. No JSON-native pruning.
• SQL Server — computed column index prunes pages; JSON functions themselves don't prune.
• Snowflake VARIANT — micro-partition prune on any clustered JSON path. If a query filters on payload:user_id::string = 'u1' and the table is clustered on that expression, Snowflake reads only the micro-partitions containing u1. Massive win for large tables.
• BigQuery native JSON — partition prune on ts; cluster prune on any clustered materialised JSON extraction. Columnar storage means only the relevant JSON path is read — often 90% cheaper than STRING-of-JSON.

When a materialised column beats JSON extraction.

• Hot filter path. WHERE JSON_VALUE(payload, '$.status') = 'active' — every planner struggles with this. Materialise status as a column, index the column, done.
• Join key. JOIN users ON JSON_VALUE(payload, '$.user_id') = users.id — never faster than a plain column join. Materialise.
• Aggregate key. GROUP BY JSON_VALUE(payload, '$.plan') — materialise for planner-friendly hash grouping.
• Sort key. ORDER BY JSON_VALUE(payload, '$.created_at') — materialise + index.
• Anything hit by dashboards. BI tools generate queries that assume plain columns; materialise every column a dashboard filters on.

When to keep the field inside JSON.

• Rare projections. A field only surfaced in one report per month; the JSON extraction overhead is negligible.
• Schema drift. Fields that change across event types (event-specific payloads); materialising each variant creates schema explosion.
• Deep sub-trees. Objects that are always consumed as sub-trees (never as scalars) — extract the sub-tree at query time.

Snowflake VARIANT + AUTOMATIC_CLUSTERING deep dive.

• Snowflake's storage engine indexes JSON paths automatically as it writes. Each micro-partition tracks min/max stats per JSON path.
• Query planner uses these stats to skip micro-partitions that can't match the predicate.
• For a query like WHERE payload:user_id::string = 'u1', Snowflake reads only the micro-partitions where user_id includes u1 in its min/max range.
• Explicit CLUSTER BY (payload:user_id::string) forces the physical layout to concentrate matching rows in fewer micro-partitions — accelerates automatic clustering.
• Great for wide tables with many JSON paths and workloads that filter on many different paths across queries.

BigQuery native JSON vs STRING-of-JSON.

• Native JSON type — columnar per-path storage. Only the queried paths are read from disk. Pruning is native.
• STRING-of-JSON — the whole column is read as text, then parsed on the fly per row. Massively more expensive.
• BigQuery bills by bytes read; native JSON bills only for the paths you extract. A WHERE JSON_VALUE(payload, '$.status') query on a 10TB table might read 100GB (native) vs 10TB (STRING). 100× cost difference.
• Migration path: CREATE TABLE events_native AS SELECT id, ts, PARSE_JSON(payload) AS payload FROM events_string; DROP TABLE events_string; (with appropriate care for pipelines).

Common bigquery json performance probes.

• "How would you migrate a 10TB STRING-of-JSON table to native JSON?" — PARSE_JSON in a CREATE TABLE AS SELECT, verify with a dry-run cost estimate, cut over with a view rename.
• "What's the cost difference between JSON_VALUE on native vs STRING?" — often 10-100× because native only reads the queried path.
• "How do you cluster a BigQuery JSON table?" — cluster on a materialised JSON_VALUE(payload, '$.hot_key') column; the JSON column itself is not clusterable.

Common snowflake variant performance probes.

• "What is AUTOMATIC_CLUSTERING?" — Snowflake's background service that reorganises micro-partitions to keep them well-clustered; runs continuously.
• "How does micro-partition prune work on a JSON path?" — Snowflake tracks min/max per path per micro-partition; the planner skips partitions whose stats can't match the predicate.
• "When would you ALTER TABLE CLUSTER BY a VARIANT column?" — when queries filter heavily on one path and automatic clustering isn't converging fast enough.

Worked example — Postgres jsonb vs generated column + BTREE benchmarking

Detailed explanation. The most common tuning debate: "should I index the JSON column with GIN, or materialise the hot path as a generated column with a BTREE?" The answer depends on query patterns. Show the interviewer a concrete comparison.

Question. Given a Postgres events(id, ts, payload jsonb) table with 100M rows, compare the plan and speed of three options for WHERE payload->>'user_id' = 'u1': (a) no index, (b) GIN on payload, (c) generated column + BTREE.

Input.

id	ts	payload
1	2026-07-01	{"user_id": "u1", "kind": "click"}
...	...	...

Code.

-- Option A — no index
-- Plan: Seq Scan on events (cost=0.00..3000000.00 rows=100k)

-- Option B — GIN on payload (jsonb_ops for @>)
CREATE INDEX ix_events_gin ON events USING GIN (payload jsonb_path_ops);

-- Query rewrite for GIN to be used
SELECT * FROM events WHERE payload @> '{"user_id": "u1"}';
-- Plan: Bitmap Heap Scan on events (cost=100..500 rows=100k)

-- Option C — generated column + BTREE
ALTER TABLE events
  ADD COLUMN user_id text GENERATED ALWAYS AS (payload->>'user_id') STORED;

CREATE INDEX ix_events_user_id ON events (user_id);

SELECT * FROM events WHERE user_id = 'u1';
-- Plan: Index Scan using ix_events_user_id (cost=0.42..12.44 rows=100k)

Step-by-step explanation.

1. Option A (no index): a full sequential scan on 100M rows. Runtime scales linearly with table size — unacceptable for interactive queries.
2. Option B (GIN + rewrite to @>): the GIN indexes containment fragments; the query planner picks a bitmap heap scan. Fast, but requires rewriting the query from ->> to @>.
3. Option C (generated column + BTREE): the plain BTREE is the fastest option for equality. The generated column stays in sync automatically. Query is unchanged from the "plain column" mental model.
4. Trade-offs: GIN supports many query patterns (containment, existence) with one index. Generated column + BTREE supports one pattern per column but is faster and smaller.
5. Rule of thumb: for 1-2 hot keys, generated column + BTREE wins. For many query patterns or deep containment, GIN wins.

Output (plan-and-timing comparison).

Option	Plan	Runtime (100M rows, sel=0.1%)
No index	Seq Scan	30-60s
GIN + @> rewrite	Bitmap Heap Scan	50-200ms
Generated column + BTREE	Index Scan	5-20ms

Rule of thumb. When the workload is dominated by 1-2 hot extractions with plain equality, always materialise as generated columns with BTREEs. Fall back to GIN when you need containment (@>) or existence (?) semantics.

Worked example — Snowflake VARIANT clustering for a hot user_id filter

Detailed explanation. Snowflake's flagship performance pattern: cluster a VARIANT column on a hot JSON path. Micro-partition prune reads only the relevant partitions, cutting query cost dramatically.

Question. Given a Snowflake events(id, ts, payload VARIANT) table with 10TB and a hot query pattern WHERE payload:user_id::string = 'u1', write the DDL to cluster on the user_id path and estimate the pruning gain.

Input.

id	ts	payload
1	2026-07-01	{"user_id": "u1", "kind": "click"}
...	...	...

Code.

-- Cluster the table on the hot JSON path
ALTER TABLE events CLUSTER BY (payload:user_id::string);

-- Run the query
SELECT id, ts, payload:kind::string AS kind
FROM events
WHERE payload:user_id::string = 'u1'
  AND ts > CURRENT_TIMESTAMP - INTERVAL '7 days';

-- Snowflake will automatically re-cluster in the background;
-- explicit CLUSTER BY declaration accelerates convergence.

Step-by-step explanation.

1. ALTER TABLE events CLUSTER BY (payload:user_id::string) declares the clustering key. Snowflake's AUTOMATIC_CLUSTERING service will start reorganising micro-partitions in the background.
2. Once well-clustered, matching user_id values are concentrated in a small subset of micro-partitions. Snowflake's per-partition min/max stats let the planner skip the rest.
3. For a table with 10TB in 100k micro-partitions and a hot user with 0.01% of rows, well-clustered reads scan ~10-100 micro-partitions (1-10GB), not the full 10TB.
4. The query also filters on ts > NOW() - 7 days. Snowflake tables are implicitly partitioned by insert order, so time-based filters also prune. Combining both cuts cost by 5-6 orders of magnitude.
5. Cost model: Snowflake bills by bytes scanned. A well-clustered query on this pattern is often <$0.01; the same query on an unclustered table is $10-100.

Output.

id	ts	kind
1	2026-07-01	click
...	...	...

Rule of thumb. For any Snowflake VARIANT table with a hot JSON-path filter, declare CLUSTER BY (payload:path::type). Automatic clustering handles the rest. The pruning gain is often 100-10000× on large tables.

Worked example — BigQuery native JSON migration cost analysis

Detailed explanation. Migrating a 10TB STRING-of-JSON table to native JSON is often a 10-100× cost win. The math is simple: BigQuery only reads the queried JSON paths on native columns; on STRING-of-JSON, it reads the whole column and parses on the fly.

Question. Given a BigQuery events_string(id, ts, payload STRING) table with 10TB and daily queries that extract 3 out of 20 payload keys, estimate the cost saving of migrating to a native JSON column and show the migration DDL.

Input.

id	ts	payload (STRING)
1	2026-07-01	{"user_id":"u1","kind":"click","other_18_keys":"..."}

Code.

-- Step 1 — create the native JSON table via CTAS
CREATE TABLE `project.dataset.events_native`
PARTITION BY DATE(ts)
CLUSTER BY user_id_mat  -- optional materialised column
AS
SELECT
  id,
  ts,
  PARSE_JSON(payload) AS payload,
  JSON_VALUE(payload, '$.user_id') AS user_id_mat
FROM `project.dataset.events_string`;

-- Step 2 — verify with a dry-run query
SELECT COUNT(*) FROM `project.dataset.events_native`
WHERE JSON_VALUE(payload, '$.user_id') = 'u1';
-- Dry run reports bytes processed — expect ~5% of the original.

-- Step 3 — swap via view
CREATE OR REPLACE VIEW `project.dataset.events` AS
SELECT * FROM `project.dataset.events_native`;

Step-by-step explanation.

1. PARSE_JSON(payload) converts the STRING column into native JSON. BigQuery stores the parsed representation columnar — one virtual column per JSON path.
2. The CTAS also creates a materialised user_id_mat column that's cluster-eligible. Native JSON columns can't be clustered directly; the workaround is a materialised extraction.
3. Cost model: STRING-of-JSON queries scan the entire payload column (say 8TB out of 10TB). Native JSON queries scan only the queried paths (say 400GB — 5% of total). 20× cost saving.
4. Migration cost: one-time CTAS scans 10TB (~$50 at $5/TB), the ongoing query cost drops 20×.
5. Downstream compatibility: existing queries that use JSON_EXTRACT_SCALAR on a STRING column need to be rewritten to JSON_VALUE on the native column. Use a view during the transition to minimise breakage.

Output (cost estimate).

Query pattern	STRING-of-JSON cost	Native JSON cost	Saving
Filter on 1 key, project 3 keys	10TB scan	500GB scan	95%
Filter on 5 keys	10TB scan	2TB scan	80%
Aggregate over the full payload	10TB scan	10TB scan	~0%

Rule of thumb. For any BigQuery table above 100GB where JSON queries touch a small subset of keys, migrate to the native JSON type. The cost saving pays for the migration in weeks or days, and the query plans improve dramatically.

Senior interview question on choosing the right JSON stack

A senior interviewer might ask: "You're greenfielding a data platform that will ingest 100M events/day, each with a 2KB JSON payload. You need to serve ad-hoc analytics with sub-10s latency. Which warehouse do you pick, which storage type, which index / cluster strategy, and why?"

Solution Using a 5-question warehouse-picking framework

Warehouse + JSON storage picker — 5 questions

1. Read pattern — ad-hoc analytics or point reads?
   ad-hoc     → columnar warehouse (Snowflake / BigQuery / Redshift)
   point read → OLTP (Postgres + jsonb)

2. Volume — is it above 10TB in the JSON column?
   yes → Snowflake VARIANT or BigQuery native JSON (columnar per-path)
   no  → Postgres jsonb (GIN + generated cols is enough)

3. Filter selectivity — do most queries filter on 1-2 hot keys?
   yes → cluster / partition on materialised extractions
   no  → let automatic clustering handle it (Snowflake) or add many gen cols

4. Schema drift — how fast do new keys arrive?
   fast (weekly) → VARIANT / native JSON — schema-on-read
   slow (monthly) → promote hot keys to plain columns

5. Portability — one warehouse or multi-cloud?
   one warehouse → pick the native stack (VARIANT / native JSON / jsonb)
   multi         → dbt macro dispatch, minimise dialect-specific SQL

Step-by-step trace.

Requirement	Q1	Q2	Q3	Q4	Q5	Pick
100M events/day, ad-hoc, sub-10s	ad-hoc	20TB/mo	2 hot	fast	one	Snowflake VARIANT + cluster on user_id
100M events/day, OLTP + BI	mixed	20TB/mo	5 hot	fast	one	BigQuery native JSON + clustering
1M rows/day, transactional	point	1GB	1 hot	slow	one	Postgres jsonb + generated col + BTREE
Multi-cloud, portable analytics	ad-hoc	5TB/mo	3 hot	fast	multi	Postgres jsonb (self-hosted) + dbt macros

The framework turns "which warehouse and how do I store JSON?" from a taste question into a mechanical decision. The answers pop out from the volume + read pattern + schema drift axes.

Output:

Pick	Storage	Index / cluster	Why
Snowflake VARIANT	VARIANT columnar	cluster on hot path	best columnar + auto-clustering
BigQuery native JSON	native JSON	partition + cluster on materialised col	best cost model for path-heavy queries
Postgres jsonb	jsonb binary	GIN + generated col + BTREE	best OLTP + operational simplicity
SQL Server JSON	NVARCHAR(MAX)	computed col + BTREE	best on Windows/enterprise stacks
MySQL JSON	JSON binary	generated col + BTREE	best on MySQL-only shops

Why this works — concept by concept:

• Read pattern drives the warehouse choice — point reads are OLTP (Postgres); ad-hoc analytics are OLAP (Snowflake/BigQuery). Everything else follows.
• Volume drives the storage type — above 10TB, columnar per-path storage wins on cost by orders of magnitude. Below 10TB, jsonb is fine and often simpler.
• Hot keys drive the index strategy — one hot key wants a plain generated column + BTREE; five wants clustering; many wants a GIN.
• Schema drift decides what to materialise — fast-drifting fields stay in the JSON payload forever; slow-drifting fields eventually get promoted to plain columns.
• Cost — every path has a query-plan cost and a storage cost that scale differently. The framework picks the stack where the cost curve matches the workload — the definition of a good design decision.

SQL
Topic — SQL
SQL interview problem library

Practice →

SQL
Topic — JSON
JSON storage and indexing drills

Practice →

---

Cheat sheet — SQL JSON recipe list

• Path extraction across five dialects. Postgres: payload->>'key' or jsonb_path_query_first(payload, '$.key'). MySQL 8: payload->>'$.key' or JSON_VALUE(payload, '$.key' RETURNING VARCHAR(64)). SQL Server: JSON_VALUE(payload, '$.key'). Snowflake: payload:key::string. BigQuery: JSON_VALUE(payload, '$.key').
• Scalar vs sub-tree split. JSON_VALUE / ->> returns a scalar SQL type; JSON_QUERY / -> returns a JSON sub-tree. Never mix them — the interviewer will catch it.
• JSONPath primitives. $ (root), .key (child), [i] (index), [*] (wildcard), ..key (recursive descent — not SQL Server), [?(@.total > 100)] (filter — Postgres 12+ and MySQL 8).
• Postgres jsonb GIN index recipe. CREATE INDEX ix ON t USING GIN (payload jsonb_path_ops); for containment-only (smaller, faster). Use jsonb_ops when you also need ? existence.
• Postgres containment operator. WHERE payload @> '{"kind":"purchase"}' — GIN-indexable, the fastest JSON filter in Postgres.
• Postgres functional expression index. CREATE INDEX ON t ((payload->>'user_id')); for one hot key. Query must use the exact same expression.
• Postgres generated column + BTREE. ADD COLUMN user_id text GENERATED ALWAYS AS (payload->>'user_id') STORED; + CREATE INDEX ON t (user_id);. Best for hot join / group-by keys.
• JSON_TABLE flatten recipe. SELECT ... FROM t CROSS APPLY JSON_TABLE(payload, '$.orders[*]' COLUMNS (id INT PATH '$.id', total DECIMAL PATH '$.total')) AS o;. Oracle, MySQL 8, SQL Server 2022+, Postgres 17+.
• Snowflake VARIANT + FLATTEN recipe. SELECT o.value:id::int, o.value:total::number FROM t, LATERAL FLATTEN(input => t.payload:orders) o;. Add OUTER => true for LEFT-JOIN semantics.
• BigQuery UNNEST(JSON_QUERY_ARRAY) recipe. SELECT JSON_VALUE(o, '$.id'), JSON_VALUE(o, '$.total') FROM t, UNNEST(JSON_QUERY_ARRAY(payload, '$.orders')) AS o;.
• Postgres jsonb_to_recordset recipe. SELECT o.id, o.total FROM t, LATERAL jsonb_to_recordset(t.payload -> 'orders') AS o(id int, total numeric);. Flat objects only.
• NESTED PATH. ANSI JSON_TABLE sub-block that flattens a second-level array: NESTED PATH '$.tags[*]' COLUMNS (tag VARCHAR PATH '$'). Emits one row per (outer, inner) pair.
• Null-safe extraction pattern. COALESCE(payload->>'key', 'default') in Postgres; IFNULL(JSON_VALUE(payload, '$.key'), 'default') in MySQL / Snowflake; ISNULL(JSON_VALUE(payload, '$.key'), 'default') in SQL Server; IFNULL(JSON_VALUE(payload, '$.key'), 'default') in BigQuery.
• Materialised column indexing recipe. Any hot JSON key that participates in filter, join, group-by, or sort should be materialised as a generated / computed column and indexed with a BTREE. Universal across dialects.
• Snowflake clustering recipe. ALTER TABLE t CLUSTER BY (payload:hot_key::string);. Automatic clustering handles the rest; monitor with SYSTEM$CLUSTERING_INFORMATION.
• BigQuery native JSON migration recipe. CREATE TABLE new AS SELECT id, ts, PARSE_JSON(payload) AS payload FROM old;. Then swap via view for zero-downtime cutover.
• Containment vs equality. @> (Postgres) tests JSON sub-object containment; = '{"key":"val"}' tests full-object equality including key order (broken by jsonb dedup). Use @> for filtering, never =.
• When to use json over jsonb in Postgres. Almost never — only when key order or whitespace preservation matters (RFC 7396 patches, signature contexts).
• dbt cross-dialect dispatch. Write a json_scalar(col, key) macro that dispatches on target.type to emit Postgres ->> vs Snowflake ::string vs BigQuery JSON_VALUE. One source, dialect-native compiled SQL.
• MySQL JSON_UNQUOTE gotcha. JSON_EXTRACT returns quoted strings; always wrap with JSON_UNQUOTE or use ->> when the value feeds a WHERE, GROUP BY, or JOIN.

Frequently asked questions

What is the difference between JSON_VALUE and JSON_EXTRACT?

JSON_VALUE returns a scalar SQL value (usually VARCHAR or a typed value via RETURNING) — designed for equality, aggregation, and typed downstream use; it errors or returns NULL when the target is a JSON object or array. JSON_EXTRACT is dialect-specific — MySQL and SQLite ship it, and it returns a JSON value that may be a scalar, an object, or an array with quotes preserved on strings. In MySQL 8, JSON_EXTRACT(payload, '$.name') returns "Ada" (with quotes) but JSON_VALUE(payload, '$.name') returns Ada (unquoted). The interviewer expects you to name the split — scalar vs sub-tree, quoted vs unquoted — in the first sentence. In SQL Server, BigQuery, and Oracle, the standard split is JSON_VALUE (scalar) vs JSON_QUERY (sub-tree); in Postgres, the arrow operators ->> (scalar text) vs -> (JSON child) do the same job.

When should I use jsonb over json in Postgres?

Almost always use jsonb. The json type stores raw text — preserving whitespace, key order, and duplicate keys — but is not indexable and requires re-parsing on every read. The jsonb type stores a binary parsed representation — deduped keys, sorted internally, indexable with GIN — and is ~5-15% smaller. Write cost is ~20-40% higher for jsonb (parse on insert), but read cost is dramatically lower because navigation is O(1) per key rather than O(n) text-parse. Reach for json only in narrow cases: preserving RFC 7396 patch documents, canonical signing forms, or when the JSON is never queried and only passed through. For every OLTP or analytics workload where the JSON is read more than once, jsonb is the correct default.

How do I flatten a JSON array into rows across dialects?

Each engine ships a different keyword for the same operation. ANSI SQL/JSON JSON_TABLE — supported by Oracle 12c+, MySQL 8.0.4+, SQL Server 2022+, and Postgres 17+ — is the standard: JSON_TABLE(payload, '$.orders[*]' COLUMNS (id INT PATH '$.id', total DECIMAL PATH '$.total')). Snowflake uses LATERAL FLATTEN(input => payload:orders) and per-element extraction via value:field::type. BigQuery pairs UNNEST(JSON_QUERY_ARRAY(payload, '$.orders')) with per-element JSON_VALUE. Older Postgres (< 17) uses jsonb_array_elements or jsonb_to_recordset in a LATERAL join. For doubly-nested arrays, ANSI JSON_TABLE supports a NESTED PATH '$.sub[*]' COLUMNS (...) sub-block; Snowflake chains LATERAL FLATTEN; BigQuery chains UNNEST; Postgres chains LATERAL jsonb_array_elements. The output shape is always the same — one row per leaf-level tuple — because that's what a relational query wants.

Does BigQuery support the native JSON type or do I use STRING?

BigQuery ships a native JSON type since 2022, and it's the strongly preferred storage for JSON payloads on new tables. Native JSON stores data columnar per JSON path — meaning a query that extracts one key only reads the bytes for that path, not the whole payload. On a 10TB bigquery json table, a hot-key filter query typically bills 100-500GB scanned (native) versus 10TB scanned (STRING-of-JSON) — a 20-100× cost improvement plus a proportional speed improvement. The migration path is a CREATE TABLE ... AS SELECT id, ts, PARSE_JSON(payload) AS payload FROM old_table;, followed by a view rename for zero-downtime cutover. Legacy STRING-of-JSON columns with JSON_EXTRACT_SCALAR still work, but every new table and every migration budget should target the native type.

What is Snowflake VARIANT and how does it index JSON paths?

snowflake variant is Snowflake's self-describing semi-structured type — a single column that holds JSON, Avro, or Parquet-like nested data. Storage is columnar per JSON path (a "virtual sub-column" per path), which means selective extraction only reads the queried paths from disk, not the whole payload. Snowflake's AUTOMATIC_CLUSTERING service tracks min/max stats per JSON path per micro-partition; when a query filters on payload:user_id::string = 'u1', the planner skips micro-partitions whose stats can't match. For hot filter paths, declare an explicit ALTER TABLE t CLUSTER BY (payload:hot_key::string) to accelerate physical layout convergence — automatic clustering will do it eventually, but explicit declarations converge faster. The typical result on a 10TB table with a well-clustered VARIANT column: a hot-user filter reads 1-10GB instead of 10TB, a 3-4 order-of-magnitude speedup.

Is JSON in SQL fast enough to replace a document store?

For read-heavy analytics workloads with warehouse-scale volumes, the modern answer is yes — Snowflake VARIANT, BigQuery native JSON, and Postgres jsonb all outperform document stores on ad-hoc analytical queries, largely because columnar storage and GIN-indexed containment beat MongoDB's default point-read profile at scale. Where document stores still win: sub-10ms OLTP point reads at high QPS (DynamoDB, MongoDB), heavy in-place sub-document updates (MongoDB's atomic operators), and write throughput above 100k QPS. The right question isn't "warehouse vs document store" but "what's the read/write ratio and latency budget?" For 90% of data-engineering workloads — event log analytics, CDC pipelines, feature stores, config tables — JSON-in-SQL is the correct default because it collocates the semi-structured data with the relational world, reducing your operational surface to one system, one backup, one auth model. Document stores earn their keep in the remaining 10% of point-read-heavy OLTP.

Practice on PipeCode

• Drill the JSON practice library → for path extraction, containment, and JSON_TABLE flattening reps across all five dialects.
• Rehearse on parsing problems → when the interviewer wants a string-to-structure conversion.
• Sharpen data-transformation drills → for wide-to-long, long-to-wide, and JSON-to-relational reshapes.
• Layer the string-parsing library → for the tokenisation primitives that underpin every JSON path.
• Stack the data manipulation library → for UPDATE, MERGE, and jsonb_set interview probes.
• For general SQL sharpening, work through the SQL problem library → which contains 450+ DE-focused questions.
• For the broader SQL interview surface, take the SQL for Data Engineering course →.

Pipecode.ai is Leetcode for Data Engineering — every `sql json` recipe above ships with hands-on practice rooms where you write the Postgres `jsonb` GIN filter, wire the Snowflake `LATERAL FLATTEN`, chase the BigQuery native-JSON migration, and rehearse the `JSON_TABLE` interview walk-through against real graded inputs. PipeCode pairs every reading with 450+ DE-focused problems and a real-time scoring engine, so you never have to wonder whether your `json_value sql` answer holds up under a senior interviewer's depth probes.

Practice JSON patterns now →
Data transformation drills →