Hello, I don't understand what you want to do here. There's an UNNEST operator in Calcite, see the comment at the bottom of this section: https://calcite.apache.org/docs/reference.html#collection-functions.
Cordialement / Best Regards, *Thomas Rebele, PhD* | R&D Developer | Germany | www.tibco.com On Tue, Nov 16, 2021 at 2:30 PM Daniel (Spotify) Ståhl <[email protected]> wrote: > 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 >
