Turn nested order items into rows with SQL

An order often contains a list of items. Reports usually need one row per item, with the order number repeated beside it. UNNEST performs that expansion.

You can build this with the Expand a list into rows and Extract a JSON value cards using the form-card tutorial. This post shows the SQL equivalent for workflows that need a custom query. SQL requires an enabled paid plan; the form cards work on every AltDat plan. Public connector certification is pending; private testers can use their imported connector.

Prepare the sample

Download nested-orders.json and put it in OneDrive or SharePoint. Read it with Get file content, then pass File Content to Prepare a table with Source Format json.

Order 1001 has two items. Order 1002 has an empty list. Order 1003 has a null list. JSON input preserves the list and object types inside the prepared table.

Add Query a table with SQL. Set AltDat Table Content to Prepare’s Body and paste this into SQL Query:

SELECT
    order_id,
    customer,
    item.sku AS sku,
    item.qty AS quantity
FROM data
CROSS JOIN UNNEST(items) AS expanded(item)
ORDER BY order_id, sku

Your uploaded table is named data. UNNEST(items) emits each item; item.sku and item.qty read fields from that object. Explicit ordering makes the result predictable.

order_id	customer	sku	quantity
1001	Ada	SKU-101	2
1001	Ada	SKU-202	1

Empty and null lists produce no rows in this query. That is useful for a line-item report, but it can hide orders that have no items.

Keep empty orders

Use a left join to retain them:

SELECT order_id, customer, item.sku AS sku, item.qty AS quantity
FROM data
LEFT JOIN UNNEST(items) AS expanded(item) ON TRUE
ORDER BY order_id, sku

This returns four rows. The two additional rows belong to orders 1002 and 1003, with null SKU and quantity. The card equivalent is Keep Empty Lists: Yes. A null list item is retained as an item even when Keep Empty Lists is off.

Read JSON text and convert extracted numbers

Some exports store objects as JSON text instead of native object columns. json_extract_string reads a path from JSON text and returns text. TRY_CAST converts a valid number and returns null if that conversion fails:

SELECT
    order_id,
    json_extract_string(to_json(item), '$.sku') AS sku,
    TRY_CAST(json_extract_string(to_json(item), '$.qty') AS INTEGER) AS quantity
FROM data
CROSS JOIN UNNEST(items) AS expanded(item)
ORDER BY order_id, sku

This produces the same two item rows. to_json(item) adapts this sample’s native objects to JSON text; omit it when the source column already contains JSON text. Paths use zero-based array indexes, such as $.items[0].sku. Invalid JSON still fails extraction: TRY_CAST handles the numeric conversion, not JSON parsing errors.

If the entire list is a text column, use UNNEST(CAST(items AS JSON[])) and extract each resulting item’s fields with json_extract_string. The form card accepts either native lists or JSON-array text without a query.

Use the output

SQL returns a JSON result with rows in data; it does not return an AltDat Table Body. Use Parse JSON to expose named row fields in Power Automate, then consume them with the next action. Use the form-card workflow if you want to pass a table straight into Export a table.

Expansion increases row count. Output must stay within the plan’s row limit and the query output limit. Work from the small sample first, then size your batches using your own list lengths.

The SQL behavior is documented in DuckDB’s UNNEST reference and JSON functions.