UNNEST

Keyword UNNEST

AlaSQL lacked a way to flatten nested arrays within SQL queries without using SEARCH. Users couldn't "broadcast" nested object properties like converting [{name: "a", entries: [{id: 1}, {id: 2}]}] into separate rows.

Added UNNEST() function as a FROM source that works with CROSS/OUTER APPLY to flatten nested arrays:

-- Flatten nested arrays
SELECT b.name, e.id, e.value
FROM data AS b
CROSS APPLY (SELECT * FROM UNNEST(b.entries)) AS e

-- With OUTER APPLY to preserve rows with empty arrays
SELECT b.name, e.id, e.value
FROM A.B AS b
OUTER APPLY (SELECT * FROM UNNEST(b.entries)) AS e

-- Join flattened data with other tables
SELECT b.name, e.id, lookup.label
FROM data AS b
CROSS APPLY (SELECT * FROM UNNEST(b.entries)) AS e
JOIN lookup ON lookup.id = e.id

Implementation notes:

• Used UNNEST (SQL standard) instead of ARRAY which is a reserved keyword
• Leverages existing CROSS/OUTER APPLY infrastructure
• Returns empty array for non-array inputs
• Works with database.table format (e.g., A.B)