Repository navigation
Implement unnest function #6555
Description
Is your feature request related to a problem or challenge?
Follow on to #6384
It would be nice to implement unnest function (with the properties like the analog in PostgreSQL) in arrow-datafusion.
Describe the solution you'd like
Main benefits for adding this feature:
- With
unnestfunction we can use aggregate functions for arrays:
SELECT sum(a) AS total FROM (SELECT unnest(make_array(3, 5, 6) AS a) AS b;
----
14
unnestfunction serves as an exchange between arrays and columns, we have 2 cases of behavior:
unnestwith single argumentunnestwith multiple argument (more than 1) (this form is only allowed in a query's FROM clause)
Examples:
unnest(make_array(1, 2))
----
1
2
select * from unnest(make_array(1, 2, 3), make_array('h', 'e', 'l', 'l', 'o')
----
1 h
2 e
3 l
l
o
Describe alternatives you've considered
For aggregate functions, we can create a lot of individual functions for aggregate functions (like array_sum), but I think this implementation would be too redundant.
Additional context
Similar Issues:
#6119
Links to sources:
https://www.postgresql.org/docs/current/functions-array.html
Activity
parkma99 commentedon Jun 9, 2023
I'd like to work on this . Is anyone already preparing a PR?I find it's hard to me.🥲
jayzhan211 commentedon Jun 17, 2023
The return type of Unnest might be Vec<ArrayRef> or Vec<ColumnValue>, a set of rows (Vec) with an array of columns (ArrayRef). https://github.com/apache/arrow-datafusion/blob/9b419b19a66bdd35e9e5c0bca259786f8f3c3965/datafusion/expr/src/columnar_value.rs#L32-L38
Maybe we can add another type like RowsOfArray(Vec<ArrayRef>) for the rows-based return type? Are there other alternative return types of Unnest?
I think the data layout is quite similar to ValuesExec, so we might need Vec of some things as the sets of rows.
https://github.com/apache/arrow-datafusion/blob/9b419b19a66bdd35e9e5c0bca259786f8f3c3965/datafusion/core/src/physical_plan/values.rs#L46-L88
jayzhan211 commentedon Jun 21, 2023
I found that we can apply
LogicalPlan::Unnestinstead of implementingUnnestin ScalarFunction.izveigor commentedon Jun 21, 2023
Yes @jayzhan211, I know about this feature: (See PR: #5106).
It only works in Rust implementation (the function
unnest_columns) but it doesn't in SQL. I only found the expressionInUnnestinsqlparser-rs: https://docs.rs/sqlparser/latest/sqlparser/ast/enum.Expr.html#variant.InUnnest.Therefore, I wonder how we will recognize this token 🤔
Also If we want to stick to the standard implementation of the function (MySQL, PostgreSQL and other),I think we should implement the scalar function as well.
@jackwener @alamb @tustvold What do you think about it?
alamb commentedon Jun 21, 2023
I don't understand what a
scalar functionwould do -- isn'tunnesteffectively a table function (in the sense that it outputs a "table" (in DataFusion terms, a stream ofRecordBatches)Thus I wonder if the SQL planner (or maybe some optimizer pass) could replace all instances of
Expr::Unnestwith a subquery (which had the relevantLogialPlan::Unnest) 🤔jayzhan211 commentedon Jun 24, 2023
I have question to unnest multi-columns.
I am considering applying unnest for each array and
jointhem together, is there an existing plan for this joining?For example, unnest([1,2], [3,4,5]) is simplified to unnest_plan([1,2]), unnest_plan([3,4,5])
Expected result
For those Unnest, apply some kind of
Joinfor them. I did not find any JoinType can concat rows side by side. Are there any existing ways to achieve this?alamb commentedon Jun 24, 2023
Maybe you could use
row_number()window function to generate a number for each subquery and then use that row number as the join key?jonahgao commentedon Apr 9, 2024
take
jonahgao commentedon Apr 9, 2024
I plan to implement this feature.