i believe that "ARRAY(subquery)” is standard SQL. My friend John Sichi wrote a very readable guide to standard SQL collection types[1] a few years ago (when the ancestral code of Calcite was part of the Farrago project), and ARRAY(subquery) is in there.
Julian [1] http://farrago.sourceforge.net/design/CollectionTypes.html <http://farrago.sourceforge.net/design/CollectionTypes.html> > On Apr 16, 2019, at 5:08 PM, James Taylor <[email protected]> wrote: > > Hello SQL experts, > Does Calcite support the ARRAY subquery expression[1]? It looks liked based > on SqlArrayQueryConstructor, it does [2]? Do folks know if this is standard > SQL and supported in very many databases? > > The use case is to simplify queries that process arrays. Instead of doing a > cross join, you can use the ARRAY function on a subquery. For example, you > can do this: > > select d.id, > ARRAY(SELECT STRUCT( > date_trunc('day',s.timestamp) as occurred_at, > s.user_id, > s.path_id, > s.accepted_source > ) > FROM UNNEST(ARRAY_CONCAT( > previous_states, > current_state) s > ) AS all_states > FROM my_data d > > instead of this: > > WITH arrayed_data AS ( > SELECT d.created_at, d.id, > ARRAY_CONCAT(d.previous_states, > current_state) as accepted > FROM my_data d > ) > select all_a.id, > ARRAY_AGG(CAST(ROW( > date_trunc('day',a.timestamp), > a.user_id, > a.path_id, > a.accept_source > ) as ROW(occurred_at TIMESTAMP, > id varchar, > path_id varchar, > accept_source varchar))) > FROM arrayed_data as all_a > CROSS JOIN UNNEST(all_a.accepted) AS a > GROUP BY all_a.id > > Thanks, > James > > [1] > https://cloud.google.com/bigquery/docs/reference/standard-sql/array_functions > [2] > https://calcite.apache.org/apidocs/org/apache/calcite/sql/fun/SqlArrayQueryConstructor.html
