From the spec: <div class="snippet-clipboard-content notranslate position-relative

This functionality is supported in BigQuery as described <a href="https://cloud.google

Support "array by table subquery" constructor about trino HOT 2 OPEN

trinodb commented on May 10, 2024 2

Support "array by table subquery" constructor

from trino.

Comments (2)

JamesRTaylor commented on May 10, 2024

This functionality is supported in BigQuery as described here.

One 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 CAST(ROW(
          date_trunc('day',s.timestamp),
          s.user_id,
          s.path_id,
          s.accept_source
         ) as ROW(occurred_at TIMESTAMP, 
                  id varchar, 
                  path_id varchar, 
                  accept_source varchar))
         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

from trino.

JamesRTaylor commented on May 10, 2024

Below are a few standalone tests that show the current and desired behavior.

Example of query we'd like to support:

SELECT a,
    ARRAY(SELECT cast(row(d.x, d.y) as row(e VARCHAR, f VARCHAR)) 
                 FROM UNNEST(c) as d)
FROM (VALUES(1,0,
         ARRAY[cast(row('a','1') as row(x varchar, y varchar)),
               cast(row('b','2') as row(x varchar, y varchar)),
               cast(row('c','3') as row(x varchar, y varchar))])) t(a,b,c)

Desired output:

Row #	a	d
1	1	a, 1
		b, 2
		c, 3

Example of query that produces the same output today, but requires an aggregation:

SELECT a,array_agg(cast(row(d.x, d.y) as row(e VARCHAR, f VARCHAR))) 
FROM (VALUES(1,0,
         ARRAY[cast(row('a','1') as row(x varchar, y varchar)),
               cast(row('b','2') as row(x varchar, y varchar)),
               cast(row('c','3') as row(x varchar, y varchar))])) t(a,b,c) 
CROSS JOIN UNNEST(c) as d
GROUP BY a

Example of query that produces one row per array element:

SELECT a,cast(row(d.x, d.y) as row(e VARCHAR, f VARCHAR)) 
FROM (VALUES(1,0,
         ARRAY[cast(row('a','1') as row(x varchar, y varchar)),
               cast(row('b','2') as row(x varchar, y varchar)),
               cast(row('c','3') as row(x varchar, y varchar))])) t(a,b,c) 
CROSS JOIN UNNEST(c) as d

Output: