SQL JSON — Applied Recordsets, Path Queries, Rebuilding

SELECT
  p.playlist_id,
  t.pos,
  t.title
FROM   playlists p
CROSS JOIN LATERAL jsonb_array_elements_text(p.tracks)
     WITH ORDINALITY AS t(title, pos)  -- the track name and its 1-based position
WHERE  t.pos <= 3                    -- keep only the first three tracks
ORDER BY p.playlist_id, t.pos;

/*
  Execution order (logical evaluation order):
  1. FROM playlists                     → Read 3 rows
  2. CROSS JOIN LATERAL jsonb_array_elements_text → Expand tracks into rows (9 rows)
  3. WITH ORDINALITY                    → Attach a 1-based pos in expansion order
  4. WHERE t.pos <= 3                   → Narrow to 8 rows
  5. SELECT playlist_id, pos, title     → Choose 3 columns
  6. ORDER BY playlist_id, pos          → Playlist order, then track order
  */

SELECT
  p.product_id,
  v.elem ->> 'color'        AS color,
  (v.elem ->> 'stock')::int AS stock  -- to a number by way of text
FROM   products p
CROSS JOIN LATERAL
     jsonb_path_query(p.spec, '$.variants[*] ? (@.stock > 0)') AS v(elem)
ORDER BY p.product_id, color;

/*
  Execution order (logical evaluation order):
  1. FROM products                  → Read 4 rows
  2. CROSS JOIN LATERAL jsonb_path_query → Walk every element via $.variants[*]
  3. ? (@.stock > 0)                → Return only elements that have stock (3 rows)
  4. SELECT color, stock            → Take two keys out of the element
  5. ORDER BY product_id, color     → Product order, then color name
  */

SELECT
  device_id,
  jsonb_object_agg(attr_key, attr_value ORDER BY recorded_at) AS attrs  -- last wins, so the newest survives
FROM     device_attrs
GROUP BY device_id
ORDER BY device_id;

/*
  Execution order (logical evaluation order):
  1. FROM device_attrs              → Read 6 rows
  2. GROUP BY device_id             → Split into 3 groups
  3. ORDER BY recorded_at (in the aggregate) → Fix the apply order inside a group, oldest first
  4. SELECT jsonb_object_agg(...)   → Fold the key-value pairs into one JSON
  5. ORDER BY device_id             → device_id ascending
  */

SELECT
  c.cart_id,
  COALESCE(
    jsonb_agg(e.elem ORDER BY e.pos) FILTER (WHERE (e.elem ->> 'qty')::int > 0),
    '[]'::jsonb                                -- a wiped-out cart becomes an empty array
  ) AS items
FROM   carts c
LEFT JOIN LATERAL jsonb_array_elements(c.items)
     WITH ORDINALITY AS e(elem, pos) ON TRUE  -- keep the original position in pos
GROUP BY c.cart_id
ORDER BY c.cart_id;

/*
  Execution order (logical evaluation order):
  1. FROM carts                     → Read 4 rows
  2. LEFT JOIN LATERAL … WITH ORDINALITY → Expand line items into rows with a position (6 rows)
  3. GROUP BY c.cart_id             → Fold back into 4 groups, one per cart
  4. FILTER (WHERE qty > 0)         → Drop qty 0 line items from what is aggregated
  5. SELECT jsonb_agg(... ORDER BY pos) → Rebuild the array in the original order
  6. COALESCE(..., '[]')            → Turn a cart with nothing left into an empty array
  7. ORDER BY c.cart_id             → cart_id ascending
  */