Array, map, struct

DataFusion’s datafusion-functions-nested crate ships ~40 array and map helpers. SQE adds Trino-named aggregate constructors (map_agg, histogram, multimap_agg).

Array construction

Array inspection

Array transformation

Array set operations

Array reductions

Array unnesting (lateral)

-- One row per (order, item) pair
SELECT order_id, item
FROM orders, UNNEST(items) AS t(item);

-- Numbered
SELECT order_id, item, idx
FROM orders, UNNEST(items) WITH ORDINALITY AS t(item, idx);

Map functions

Aggregates that build maps / arrays

See Aggregate functions for array_agg, map_agg, histogram, multimap_agg, map_union. The names differ slightly across engines:

Struct / row

SELECT named_struct('host', host, 'port', port) AS endpoint
FROM servers;

SELECT endpoint.host, endpoint.port FROM ...;

Examples

Tag-set membership

-- Find products with both 'sale' and 'new' tags
SELECT * FROM products
WHERE array_has_all(tags, ARRAY['sale', 'new']);

-- Find products with any of the listed tags
SELECT * FROM products
WHERE array_has_any(tags, ARRAY['sale', 'clearance']);

Top-K frequencies via histogram

SELECT k, v
FROM events, UNNEST(map_keys(histogram(event_type)), map_values(histogram(event_type))) AS t(k, v)
ORDER BY v DESC
LIMIT 10;

Build a map from joined tables

SELECT
    user_id,
    map_agg(setting_key, setting_value) AS preferences
FROM user_settings
GROUP BY user_id;

map_agg errors on duplicate keys. For multimap-style behaviour use multimap_agg.

Lateral pattern: filter then unnest

SELECT order_id, tag
FROM orders, UNNEST(tags) AS t(tag)
WHERE order_id > 100 AND tag LIKE 'priority_%';

Lambda functions

SQE parses SQL with the DuckDB dialect, so lambda syntax (x -> expr) is accepted. All six Trino higher-order array functions work:

• filter(array, x -> pred). Keeps the elements where the predicate holds.
• transform(array, x -> expr). Applies the expression to each element.
• any_match(array, x -> pred). True if any element matches.
• all_match(array, x -> pred). True if every element matches (empty array is true).
• none_match(array, x -> pred). True if no element matches.
• reduce(array, init, (s, x) -> combine, s -> finish). Left fold: threads an accumulator through the elements, then maps it to the result.

filter and transform alias DataFusion 54’s array_filter and array_transform; any_match is DataFusion’s array_any_match. all_match, none_match, and reduce are SQE UDFs built on the same lambda machinery. Argument order, 1-based element binding, and NULL/empty-array semantics match Trino.

SELECT filter(ARRAY[1, 2, 3, 4], x -> x > 2);                     -- [3, 4]
SELECT transform(ARRAY[1, 2, 3], x -> x * 10);                    -- [10, 20, 30]
SELECT all_match(ARRAY[2, 4, 6], x -> x % 2 = 0);                 -- true
SELECT none_match(ARRAY[1, 3, 5], x -> x % 2 = 0);                -- true
SELECT reduce(ARRAY[1, 2, 3, 4], 0, (s, x) -> s + x, s -> s);     -- 10

What is NOT registered

• zip(a, b) (parallel-iterate two arrays). Use unnest against an indexed pair instead.
• Snowflake flatten table function (with PATH and OUTER options). Use UNNEST directly.