Functions/Files & tables/unnest
Files & tables Table

unnest()

Arguments & returns

unnest(col0)

Table
Overload link col0 Returns
#01 ANY TABLE

In other engines

Apache Sparkexplode()

EXPLODE(x)
In DuckDB
UNNEST(x)

SQLGlot example ↗

Used in larger rewrites 4

These examples use unnest as one part of a larger SQL translation.

PostgreSQL

WITH dates AS (SELECT GENERATE_SERIES('2020-01-01'::DATE, '2024-01-01'::DATE, '1 day'::INTERVAL) AS date), date_table AS (SELECT DISTINCT DATE_TRUNC('MONTH', date) AS date FROM dates) SELECT * FROM date_table
In DuckDB
WITH dates AS (SELECT UNNEST(GENERATE_SERIES(CAST('2020-01-01' AS DATE), CAST('2024-01-01' AS DATE), CAST('1 day' AS INTERVAL))) AS date), date_table AS (SELECT DISTINCT DATE_TRUNC('MONTH', date) AS date FROM dates) SELECT * FROM date_table

SQLGlot example ↗

Apache Hiveexplode()

SELECT a FROM x LATERAL VIEW EXPLODE(y) t AS a
In DuckDB
SELECT a FROM x CROSS JOIN UNNEST(y) AS t(a)

SQLGlot example ↗

BigQueryunnest()

SELECT * FROM UNNEST(ARRAY<STRUCT<x INT64>>[])
In DuckDB
SELECT * FROM (SELECT UNNEST(CAST([] AS STRUCT(x BIGINT)[]), max_depth => 2))

SQLGlot example ↗

Snowflakeminhash_combine()

MINHASH_COMBINE(sig_col)
In DuckDB
(SELECT JSON_OBJECT('state', LIST(min_h ORDER BY idx NULLS FIRST), 'type', 'minhash', 'version', 1) FROM (SELECT pos AS idx, MIN(val) AS min_h FROM UNNEST(LIST(sig_col)) AS _(sig) JOIN UNNEST(CAST(sig -> '$.state' AS UBIGINT[])) WITH ORDINALITY AS t(val, pos) ON TRUE GROUP BY pos))

SQLGlot example ↗