You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "jleibs (via GitHub)" <gi...@apache.org> on 2024/07/11 19:38:25 UTC
[I] Expose inner field of struct within list-array [datafusion]
jleibs opened a new issue, #11419:
URL: https://github.com/apache/datafusion/issues/11419
### 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:
```
+--------------------------------------+
| example.MyPoints |
+--------------------------------------+
| [{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(\"example.MyPoint\", "X") as X, array_field(\"example.MyPoint\", "Y") FROM custom_table
```
### Describe alternatives you've considered
I've tried to come up with some way to use `unnest` to achieve this but can't figure out how to preserve the list.
Example:
```
> CREATE TABLE example_table AS
SELECT * FROM (
VALUES
(ARRAY[NAMED_STRUCT('x', 1.0, 'y', 2.0), NAMED_STRUCT('x', 3.0, 'y', 4.0)]),
(ARRAY[NAMED_STRUCT('x', 5.0, 'y', 6.0)])
) as example_table(mypoints);
> select make_array(px) from (select p.x as px from (select unnest(mypoints) as p from example_table));
+----------------+
| make_array(px) |
+----------------+
| [1.0] |
| [3.0] |
| [5.0] |
+----------------+
```
### Additional context
_No response_
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Expose inner field of struct within list-array [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #11419:
URL: https://github.com/apache/datafusion/issues/11419#issuecomment-2227388687
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
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Expose inner field of struct within list-array [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #11419:
URL: https://github.com/apache/datafusion/issues/11419#issuecomment-2226339500
DYI @duongcongtoai and @jayzhan211 who might have some pointers / suggestions
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Expose inner field of struct within list-array [datafusion]
Posted by "jleibs (via GitHub)" <gi...@apache.org>.
jleibs commented on issue #11419:
URL: https://github.com/apache/datafusion/issues/11419#issuecomment-2225944990
Implementing `array_field` for a single nested struct array as my own UDF wasn't too horrible.
Would be nice to be able to do this in a generic and recursive fashion though.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Expose inner field of struct within list-array [datafusion]
Posted by "jayzhan211 (via GitHub)" <gi...@apache.org>.
jayzhan211 commented on issue #11419:
URL: https://github.com/apache/datafusion/issues/11419#issuecomment-2226873634
I think it would be a plus If it could be extended from existing `get_field` function.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Expose inner field of struct within list-array [datafusion]
Posted by "jleibs (via GitHub)" <gi...@apache.org>.
jleibs commented on issue #11419:
URL: https://github.com/apache/datafusion/issues/11419#issuecomment-2226357791
Here's the proof-of-concept I wrote to handle this for one level of struct field extraction:
https://gist.github.com/jleibs/853a8f2eae2445d5bcdf9198e08ea6a0
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Expose inner field of struct within list-array [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #11419:
URL: https://github.com/apache/datafusion/issues/11419#issuecomment-2226339750
https://github.com/alamb/datafusion/blob/ea92ae72f7ec2e941d35aa077c6a39f74523ab63/datafusion/functions/src/core/getfield.rs#L141-L214 is how the current field access code works
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org