Hi
We are trying to generate relational algebra for SQL that uses UNNEST of
arrays in BigQuery. Here are three examples of SQL (in Big Query standard
query syntax) that we want to be able to handle and generate from
relational algebra. Any tips on how you would do that in Calcite?
SELECT id,
(SELECT h.is_active FROM UNNEST(history.all_of_history) h
WHERE start_date <= "2021-06-01" AND (end_date >= "2021-06-01" OR
end_date IS NULL))
FROM `table`;
SELECT id, h.is_active
FROM `table`
CROSS JOIN UNNEST(history.all_of_history) h;
SELECT id, count(h) as h
from `table`
CROSS JOIN UNNEST(history.all_of_history) h
GROUP BY id;
The table "table" has two columns: "id" and "history". history is a record
and "history.all_of_history"
is a repeated record with three fields ("is_active", "start_date" and
"end_date").
Thanks,
Daniel Ståhl
