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
