Question

A data engineer has a table EVENTS with a VARIANT column PAYLOAD. Each row's PAYLOAD contains a JSON object with a top-level array field named 'items', where each element has 'sku' and 'qty' attributes. The engineer needs a result set with one row per item, exposing the SKU and quantity as separate columns alongside the event's ID. Which query construct correctly produces this output?

Accepted Answer

SELECT e.id, f.value:sku::string AS sku, f.value:qty::number AS qty FROM events e, LATERAL FLATTEN(input => e.payload:items) f

Answer

SELECT e.id, e.payload:items:sku::string AS sku, e.payload:items:qty::number AS qty FROM events e

Answer

SELECT e.id, PARSE_JSON(e.payload:items):sku AS sku, PARSE_JSON(e.payload:items):qty AS qty FROM events e

Answer

SELECT e.id, f.sku, f.qty FROM events e GROUP BY e.id, FLATTEN(e.payload:items)