Repository navigation
Expose inner field of struct within list-array #11419
Description
Is your feature request related to a problem or challenge?
We frequently work with tables made up of "batch" data, which is in turn represented via structs.
For example:
+--------------------------------------+
| position2d |
+--------------------------------------+
| [{x: 1.0, y: 2.0}, {x: 3.0, y: 4.0}] |
| [{x: 5.0, y: 6.0}] |
+--------------------------------------+
I want to be able to restructure this so the inner fields of the struct array become their own columns:
+------------+------------+
| X | Y |
+------------+------------+
| [1.0, 3.0] | [2.0, 4.0] |
| [5.0] | [6.0] |
+------------+------------+
Describe the solution you'd like
I would like to be able to do this from SQL.
For example:
SELECT array_field(position2d, "x"), array_field(position2d, "Y") FROM example;
Describe alternatives you've considered
This can be achieved via unnest and array_agg, but is somewhat painful to do so, requires the existence of a preserved row_id for group_by operation, and introduces uncertainty as to preservation of ordering. It does not appear that datafusion supports WITH ORDINALITY which would be used to orderwise guarantee ordering is maintained.
Example:
CREATE TABLE example AS
SELECT * FROM (
VALUES
(1, ARRAY[NAMED_STRUCT('x', 1.0, 'y', 2.0),
NAMED_STRUCT('x', 3.0, 'y', 4.0)]),
(2, ARRAY[NAMED_STRUCT('x', 5.0, 'y', 6.0)])
) as example(id, position2d);
select array_agg(p.x) as x, array_agg(p.y) as y
FROM (select id, unnest(position2d) as p from example)
GROUP BY id ORDER BY id;
Additional context
Structurally, the appropriate child array of the struct should be able to be used with the offset array from the list-array and I believe the "right thing" should happen. As such I believe this should generally be able to be implemented as a cheap operation along the lines of a cast.
Activity
jleibs commentedon Jul 12, 2024
Implementing a proof-of-concept
array_fieldfor a single nested struct with a known type as my own UDF wasn't too horrible.Would be nice to be able to do this in a generic and recursive fashion though.
alamb commentedon Jul 12, 2024
DYI @duongcongtoai and @jayzhan211 who might have some pointers / suggestions
alamb commentedon Jul 12, 2024
https://github.com/alamb/datafusion/blob/ea92ae72f7ec2e941d35aa077c6a39f74523ab63/datafusion/functions/src/core/getfield.rs#L141-L214 is how the current field access code works
jleibs commentedon Jul 12, 2024
Here's the proof-of-concept I wrote to handle this for one level of struct field extraction:
https://gist.github.com/jleibs/853a8f2eae2445d5bcdf9198e08ea6a0
jayzhan211 commentedon Jul 13, 2024
I think it would be a plus If it could be extended from existing
get_fieldfunction.alamb commentedon Jul 14, 2024
I think some of the json operators in https://github.com/datafusion-contrib/datafusion-functions-json might allow similar access patterns and could serve as inpiration
feat(udf): chunk_parts + json_pack — runtime functions for generated …