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