Question

A Fabric Warehouse table named RawOrders has a column OrderPayload of type VARCHAR(MAX) containing JSON documents. Each document has a top-level 'items' array where each element includes 'sku' and 'qty' properties. You must produce one row per item (sku, qty) for each order to load into a fact table. Which T-SQL approach correctly expands the array into rows?

Accepted Answer

Use CROSS APPLY OPENJSON(OrderPayload, '$.items') WITH (sku VARCHAR(50) '$.sku', qty INT '$.qty')

Answer

Use JSON_VALUE(OrderPayload, '$.items') and cast the result to a table type

Answer

Use PIVOT on the OrderPayload column grouped by sku and qty

Answer

Use STRING_SPLIT(OrderPayload, ',') and parse each element with SUBSTRING