SQL & DatamediumSign in to track progress

Extract Fields From a JSON Column

events(id NUMBER, received_at TIMESTAMP, payload VARIANT) stores one JSON document per event, shaped like:

{ "type": "purchase", "user": { "id": "u-17", "plan": "pro" },
  "items": [ { "sku": "A1", "qty": 2, "price": 9.5 }, { "sku": "B7", "qty": 1, "price": 20 } ] }

Return, for purchase events only, the user id, the plan, the number of items and the order total (sum of qty × price), one row per event. Then write the query that returns one row per item instead.

Hints

Hint 1

Path syntax: payload:type, payload:user.id, payload:items[0].sku; cast scalars with ::string or ::number before comparing or summing.

Hint 2

ARRAY_SIZE(payload:items) counts items; the total needs the array exploded with LATERAL FLATTEN(input => payload:items) and a GROUP BY back to the event.

Hint 3

Postgres: payload->>'type', payload->'user'->>'id', jsonb_array_length, and jsonb_array_elements in a lateral join.

Solution

Show the approach

Approach

Scalars come straight out of the document by path, cast to the type you need. Anything inside an array needs the array turned into rows first: flatten, compute per item, and aggregate back to the event.

SELECT e.id,
       e.payload:user.id::string   AS user_id,
       e.payload:user.plan::string AS plan,
       ARRAY_SIZE(e.payload:items) AS item_count,
       SUM(i.value:qty::number * i.value:price::number) AS order_total
FROM events e,
     LATERAL FLATTEN(input => e.payload:items) i
WHERE e.payload:type::string = 'purchase'
GROUP BY e.id, user_id, plan, item_count;

One row per item is the same query without the aggregate:

SELECT e.id, e.payload:user.id::string AS user_id,
       i.index AS position, i.value:sku::string AS sku,
       i.value:qty::number AS qty, i.value:price::number AS price
FROM events e, LATERAL FLATTEN(input => e.payload:items) i
WHERE e.payload:type::string = 'purchase';

Postgres equivalents: payload->>'type' = 'purchase', payload->'user'->>'id', jsonb_array_length(payload->'items'), and CROSS JOIN LATERAL jsonb_array_elements(payload->'items') AS i(value) with (i.value->>'qty')::numeric.

Worth saying out loud

Casting is not optional: a VARIANT compared with a string literal compares a JSON value, and "purchase" with quotes is not 'purchase'.
An event with an empty items array disappears from the flattened join; use OUTER => TRUE (or a LEFT JOIN LATERAL) if those events must still appear with a total of zero.
Extracting the same paths in every query is the signal to materialise them as columns, which is also what makes them prunable.

Asked at

Snowflake