I haven't tried it, but I'd guess that you join to each separately,
i.e. take the cartesian product:
SELECT numbers, animals, n, a
FROM (
VALUES
(ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']),
(ARRAY[7, 8, 9], ARRAY['cow', 'pig'])
) AS x (numbers, animals)
CROSS JOIN UNNEST (numbers) AS t1 (n)
CROSS JOIN UNNEST (animals) AS t2 (a)
This doesn't require an extension to standard SQL.
On Tue, Feb 11, 2020 at 11:07 PM Miao Yu <[email protected]> wrote:
>
> Hi Calcite experts:
>
> In Presto, multiple array columns could be flattened "together", as Presto
> docs <https://prestodb.io/docs/current/sql/select.html> shows:
>
> SELECT numbers, animals, n, aFROM (
> > VALUES
> > (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']),
> > (ARRAY[7, 8, 9], ARRAY['cow', 'pig'])) AS x (numbers, animals)CROSS
> > JOIN UNNEST(numbers, animals) AS t (n, a)
> >
> > yields:
>
> numbers | animals | n | a
> -----------+------------------+------+------
> [2, 5] | [dog, cat, bird] | 2 | dog
> [2, 5] | [dog, cat, bird] | 5 | cat
> [2, 5] | [dog, cat, bird] | NULL | bird
> [7, 8, 9] | [cow, pig] | 7 | cow
> [7, 8, 9] | [cow, pig] | 8 | pig
> [7, 8, 9] | [cow, pig] | 9 | NULL
>
>
> May I know what is the equivalent in Calcite SQL?
>
> Thanks!
> Will
