Feature: add list function like duckdb

[l1t1]

Summary
list(a)
fill rows of column a into a list/array
also support group by and order by
eg.

D select list(i)from range(10)t(i);
┌────────────────────────────────┐
│            list(i)             │
│            int64[]             │
├────────────────────────────────┤
│ [0, 1, 2, 3, 4, 5, 6, 7, 8, 9] │
└────────────────────────────────┘
D select i%3,list(i)from range(10)t(i)group by i%3;
┌─────────┬──────────────┐
│ (i % 3) │   list(i)    │
│  int64  │   int64[]    │
├─────────┼──────────────┤
│       0 │ [0, 3, 6, 9] │
│       1 │ [1, 4, 7]    │
│       2 │ [2, 5, 8]    │
└─────────┴──────────────┘

[l1t1]

D select i%3,list(i order by i desc)from range(10)t(i)group by i%3;
┌─────────┬─────────────────────────┐
│ (i % 3) │ list(i ORDER BY i DESC) │
│  int64  │         int64[]         │
├─────────┼─────────────────────────┤
│       0 │ [9, 6, 3, 0]            │
│       1 │ [7, 4, 1]               │
│       2 │ [8, 5, 2]               │
└─────────┴─────────────────────────┘

[sundy-li]

This is a kind of aggregate function, it's an easy task I think.

[wangjili8417]

/assignme

[l1t1]

WITH SAMPLE_DATA AS (
    SELECT 'A' as var1, 'X' as var2
    UNION ALL
    SELECT 'A' as var1, NULL as var2
    UNION ALL
    SELECT 'B' as var1, 'X' as var2
    UNION ALL
    SELECT 'B' as var1, 'Y' as var2
    UNION ALL
    SELECT 'B' as var1, 'X' as var2
)
SELECT
    var1
    , list(var2) as agg_var2_1
    , list(var2 ORDER BY var2 nulls first) as agg_var2_2
    , list(DISTINCT var2) as agg_var2_3
    , list(DISTINCT var2 ORDER BY var2 nulls last) as agg_var2_4
FROM SAMPLE_DATA
GROUP BY ALL
ORDER BY var1
;

┌─────────┬────────────┬────────────┬────────────┬────────────┐
│  var1   │ agg_var2_1 │ agg_var2_2 │ agg_var2_3 │ agg_var2_4 │
│ varchar │ varchar[]  │ varchar[]  │ varchar[]  │ varchar[]  │
├─────────┼────────────┼────────────┼────────────┼────────────┤
│ A       │ [X, NULL]  │ [NULL, X]  │ [X, NULL]  │ [X, NULL]  │
│ B       │ [X, Y, X]  │ [X, X, Y]  │ [X, Y]     │ [X, Y]     │
└─────────┴────────────┴────────────┴────────────┴────────────┘

[b41sh]

@wangjili8417 Since we need to use it, I implemented the list function first, and you can continue to do the list with the order by inner values.

[kesavkolla]

Can we also add lambda functions to list? That way we can do filters on lists. I think this lambda type thing will probably can help in both filter and map.

functions like list_filter, array_filter, list_any_value from duckdb.

[b41sh]

Can we also add lambda functions to list? That way we can do filters on lists. I think this lambda type thing will probably can help in both filter and map.

functions like list_filter, array_filter, list_any_value from duckdb.

Yes, we plan to add lambda expressions to the array function in issue #7931, but the priority is low and we will add them in the future.