google-bigquery - 如何 UNNEST 并将所有重复的行写入单行

Question

目前，我有一个包含产品名称和成分的嵌套表。但是需要 UNNEST() 它并将所有成分写在一行中。

SELECT productTitle, ingridientTitle  FROM `TABLE`, UNNEST(ingredients)

由此 ：

productTitle             ingridientTitle
     A                         A
                               C
                               D
                               E

     B
                               A
                               B

     C
                               A
                               C

对此：

productTitle             ingridientTitle
     A                        A,C,D,E
     B                        A,B
     c                        A,C

标签： google-bigquery

Answer 1

无需取消嵌套，您可以使用ARRAY_TO_STRING()：

with a as (
select 'A' as productTitle
, ['A', 'B' , 'C' , 'D', 'E'] as ingridientTitle 
union all 
select 'B' as productTitle
, ['A', 'B'] as ingridientTitle 
union all 
select 'C' as productTitle
, ['A',  'E'] as ingridientTitle 
)
select productTitle , ARRAY_TO_STRING(ingridientTitle, ",")
from a