Semi-structured & modern warehouse SQL

JSON extraction, LATERAL/FLATTEN, QUALIFY idiom, array contains/overlap predicates.

• ★JSON extraction

  JSON path syntax, casting from variant, and the NULL-vs-missing-key distinction all bite when extracting semi-structured data.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE prizes(payload JSON); INSERT INTO prizes VALUES ('{"score":85}'),('{"score":9}');

  The trap

  SELECT json_extract_string(payload, '$.score') AS score FROM prizes ORDER BY json_extract_string(payload, '$.score');

  85

  9

  The fix

  SELECT CAST(json_extract_string(payload, '$.score') AS INT) AS score FROM prizes ORDER BY CAST(json_extract_string(payload, '$.score') AS INT);

  9

  85

  The leaderboard put '85' before '9' — values extracted from JSON are text until you type them, so they compare alphabetically; casting to INT after extraction orders them 9 then 85.

• ★JSON null vs missing key

  A key present-with-null and a key absent are different facts — and your extraction function decides whether you can still tell them apart.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE orders(oid INT, payload JSON); INSERT INTO orders VALUES (1,'{"tip":5}'),(2,'{"tip":null}'),(3,'{}');

  The trap

  SELECT COUNT(*) AS declined FROM orders WHERE json_extract_string(payload,'$.tip') IS NULL;

  2

  The fix

  SELECT COUNT(*) AS declined FROM orders WHERE json_type(json_extract(payload,'$.tip')) = 'NULL';

  1

  The declined-tip count read 2, not 1 — json_extract_string collapsed the kiosk that reported tip:null and the kiosk that never asked into one NULL, so a 'said no' and a 'wasn't asked' became the same fact.

• LATERAL / FLATTEN

  LATERAL/FLATTEN explodes arrays — re-introducing fan-out deliberately, closed by re-aggregation (P06 inverted).

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE shops(s VARCHAR); INSERT INTO shops VALUES ('north'),('south'); CREATE TABLE sales(s VARCHAR, item VARCHAR, n INT); INSERT INTO sales VALUES ('north','a',9),('north','b',5),('north','c',1),('south','x',7),('south','y',3);

  The trap

  SELECT s, item FROM sales ORDER BY n DESC LIMIT 2;

  north	a
  south	x

  The fix

  SELECT sh.s, t.item FROM shops sh, LATERAL (SELECT item, n FROM sales sa WHERE sa.s = sh.s ORDER BY n DESC LIMIT 2) t ORDER BY sh.s, t.n DESC;

  north	a
  north	b
  south	x
  south	y

  A global LIMIT 2 returned the two overall top sellers, not each shop's — a LATERAL subquery in FROM sees the current row, so 'for each shop, its top 2' is one statement of intent; a sealed subquery can't.

• QUALIFY idiom

  QUALIFY filters window results inline (dialect); GROUP BY ALL and friends are conveniences, not concepts.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE carts(cart VARCHAR, twist VARCHAR, n INT); INSERT INTO carts VALUES ('e','plain',18),('e','salt',5),('w','plain',10);

  The trap

  SELECT cart, twist FROM carts QUALIFY ROW_NUMBER() OVER (PARTITION BY cart ORDER BY n DESC) = 1 ORDER BY cart;

  e	plain
  w	plain

  The fix

  SELECT cart, twist FROM (SELECT cart, twist, ROW_NUMBER() OVER (PARTITION BY cart ORDER BY n DESC) AS rn FROM carts) WHERE rn = 1 ORDER BY cart;

  e	plain
  w	plain

  Both return the best twist per cart, but QUALIFY is engine sugar — it filters the window inline where DuckDB/Snowflake/BigQuery allow it, and code that ships to Postgres must expand it back to the subquery-then-filter.

• Array predicates

  Array contains/overlap predicates vs join-through-unnest differ in cost and NULL semantics.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE boxes(box VARCHAR, ings VARCHAR[]); INSERT INTO boxes VALUES ('b1',['rice','nut']),('b2',['rice',NULL]);

  The trap

  SELECT box FROM boxes WHERE NOT list_contains(ings, 'nut') ORDER BY box;

  b2

  The fix

  SELECT box FROM boxes WHERE NOT list_contains(ings, 'nut') AND NOT list_contains(ings, NULL) ORDER BY box;

  0 rows

  The nut-safe filter called b2 safe, but its array has a NULL (unlabeled) ingredient that could be a nut — array predicates treat a NULL element as unknowable-therefore-no-match, so a NULL-aware check is needed before declaring a box safe.

• UNNEST re-introduces fan-out

  UNNEST is a deliberate one-to-many — parent-level metrics summed after exploding are multiplied by list length.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE panels(panel VARCHAR, price INT, colors VARCHAR[]); INSERT INTO panels VALUES ('P1',30,['red','blue']),('P2',18,['green']);

  The trap

  SELECT SUM(price) AS revenue FROM (SELECT panel, price, UNNEST(colors) AS color FROM panels);

  78

  The fix

  SELECT SUM(price) AS revenue FROM panels;

  48

  The panel revenue summed to 78, not 48 — after UNNEST exploded each panel's color array, its price rode along once per color, so SUM multiplied it by the list length.

• Array membership vs unnest-join

  Membership predicates (list_contains/list_has_any-class) test without exploding — an unnest-join answers the same question at a different grain and must be re-deduped.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE jars(jar VARCHAR, ingredients VARCHAR[]); INSERT INTO jars VALUES ('stew',['nut','nut','salt']),('jam',['fruit']);

  The trap

  SELECT COUNT(*) AS jars FROM (SELECT jar, UNNEST(ingredients) AS ing FROM jars) WHERE ing = 'nut';

  2

  The fix

  SELECT COUNT(*) AS jars FROM jars WHERE list_contains(ingredients, 'nut');

  1

  The unnest-count read 2 jars for 'nut', not 1 — stew's array lists the element 'nut' twice, so exploding the duplicate double-counted the jar; a membership predicate stays at jar grain and answers once.

• Struct vs JSON on a missing field

  Structs are typed nesting — a missing field is a compile-time binder error; JSON is schemaless — a missing key is a runtime NULL.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE ping(id INT); INSERT INTO ping VALUES (1);

  The trap

  SELECT id, {'depth': 4}.temp AS temp FROM ping;

  0 rows

  The fix

  SELECT id, {'depth': 4}.depth AS depth, json_extract('{"depth":4}', '$.temp') AS json_temp FROM ping;

  1

  4

  NULL

  Accessing a missing struct field raised a binder error before any data ran — a struct is typed nesting, so a typo fails at compile time; the same missing key in JSON returns a runtime NULL, silent forever.