UNNEST in Optic and SQL
MarkLogic 11 adds the ability to "unnest" rows from within rows in both SQL and the Optic API.
SQL
Support for the following SQL syntax is now available in MarkLogic 11:
CROSS|LEFT|INNER JOIN UNNEST(iterableExpression) [WITHORDINALITY] [as<rename>]
CROSS JOIN UNNEST and INNER JOIN UNNEST behave the same and produce new rows for each of the nested values, but if the UNNEST input is NULL, no row will be output.
LEFT JOIN UNNEST produces new rows for each of the nested values, including a single row if the UNNEST input is NULL.
Optic API
The unnest capability is exposed in the Optic API via the two new Operators below:
Function |
Description |
|---|---|
Flattens an array into multiple rows and performs an inner join against the rest of the rows |
|
Flattens an array into multiple rows and performs a left outer join against the rest of the rows |