support for array subquery expression?

[James Taylor <ja...@apache.org>]

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

Re: support for array subquery expression?

[Julian Hyde <jh...@apache.org>]

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 <ja...@apache.org> 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