Array column and aggregation in BigQuery SQL: Why the values are not all aggregated?

user_5715610 0
Viewed 1695

I've executed the code below in BigQuery 

SELECT ( --inner query
         SELECT STRING_AGG(c) FROM t1.array_column c
       ) 
FROM (
        select 1 as f1, ['1','2','3'] as array_column
        union all
        select 2 as f1, ['5','6','7'] as array_column
) t1;

I expected something like 

Row|f0_
1  | 1,2,3,4,5,6,7

because there is no GROUP BY in the inner query. So, I'm expecting STRING_AGG to be evaluated on all the lines.

SELECT STRING_AGG(c) FROM t1.array_column c

Instead I'm getting something like this:

Row|f0_
1  |1,2,3
2  |5,6,7

I'm having troubles understand why I have this result

google-bigquery sql
2 Answers

This is your query:

SELECT (SELECT STRING_AGG(c) FROM t1.array_column c
       ) 
FROM (select 1 as f1, ['1', '2', '3'] as array_column
      union all
      select 2 as f1, ['5', '6', '7'] as array_column
     ) t1;

First, I'm surprised it works. I thought you needed unnest():

SELECT (SELECT STRING_AGG(c) FROM UNNEST(t1.array_column) c
       )

What is happening? Well, this would be more obvious if you selected f1. Then you would get:

1     1,2,3
2     5,6,7

This should make it more clear. For each row in t1 (and there are two rows), your code is:

  • unnesting the array into rows with a column called c.
  • reaggregating those rows into a string (with no name)

If you want to combine the elements in the arrays, use array_concat_agg():

SELECT array_concat_agg(array_column)
FROM (select 1 as f1, ['1','2','3'] as array_column
      union all
      select 2 as f1, ['5','6','7'] as array_column
     ) t1;

If you want this represented as a string instead of an array, use array_to_string():

SELECT array_to_string(array_concat_agg(array_column), ',')
FROM (select 1 as f1, ['1','2','3'] as array_column
      union all
      select 2 as f1, ['5','6','7'] as array_column
     ) t1;
user_11440350

Below is for BigQuery Standard SQL 

#standardSQL
SELECT STRING_AGG((SELECT STRING_AGG(c) FROM t1.array_column c)) 
FROM (
  SELECT 1 AS f1, ['1','2','3'] AS array_column UNION ALL
  SELECT 2 AS f1, ['5','6','7'] AS array_column
) t1

and produces 

Row f0_  
1   1,2,3,5,6,7

Note 1: you were almost there - you were just missing extra STRING_AGG that does final grouping of strings created off of respective array in each row

Note 2: because array_column is of ARRAY type it is treated as inner table referenced as t1.array_column as as such - FROM t1.array_column c is equivalent to FROM UNNEST(array_column) c - very cool hidden feature :o)

user_52219440
Related