[
https://issues.apache.org/jira/browse/BEAM-9777?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17085981#comment-17085981
]
Kenneth Knowles commented on BEAM-9777:
---------------------------------------
I believe legacy BQ SQL and standard BQ SQL differ on the output type. Since
ZetaSQL corresponds to BQ standard SQL the expected output here is a single
column containing structs. Some references:
*Standard SQL*
https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays#querying_struct_elements_in_an_array
The schema of the output is a single column with type STRUCT. It is always
precisely the transpose of the array regardless of what type is in the array.
Generic UNNEST logic that is agnostic to the array type should work.
*Legacy SQL*
https://cloud.google.com/bigquery/docs/legacy-nested-repeated#flatten
The FLATTEN operator makes each field of the struct into a column of the
output. This makes arrays of non-structs and arrays of structs operate in
different ways.
> Support UNNEST(ARRAY[STRUCT()])
> -------------------------------
>
> Key: BEAM-9777
> URL: https://issues.apache.org/jira/browse/BEAM-9777
> Project: Beam
> Issue Type: New Feature
> Components: dsl-sql-zetasql
> Reporter: Andrew Pilloud
> Priority: Major
>
> It would be nice to be able to UNNEST an array of structs. For example:
> {code:sql}
> WITH pcol AS
> (SELECT 1 AS key, ARRAY[STRUCT("abc" AS name, "rst" AS slot),STRUCT("def"
> AS name, "uvw" AS slot)] AS promo
> UNION ALL
> SELECT 2 AS key, ARRAY[STRUCT("ghi" AS name, "xyz" AS slot)] AS promo)
> SELECT * FROM pcol, UNNEST(pcol.promo);
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
