BigQuery: How to group similar records of an array together into comma separated field?-CodePudding

I have a text field that is a comma separated file. The records look like this

SKU                  Info
123                Common:  tshirt
345                common:  jeans, color: green
567                common:  tshirt, common: jeans, color: blue

I'm trying to get these into arrays but combining where the first word is the same. IE: for record 3 above (567) the array field would be

  common:  tshirt, jeans
  color: blue

my current query:

 select   array(select trim(val) from unnest(split(trim(infos, ''))) val) as testing

returns

567    common: jeans
567    common: tshirt
567    color:  blue

Any insight or help would be appreciated. Thanks!

CodePudding user response：

Consider below approach

select sku, array(
    select any_value(split(trim(val), ':')[offset(0)]) || ': ' ||
      string_agg(split(trim(val), ':')[offset(1)], ', ') 
    from unnest(split(trim(info, ''))) val
    group by split(trim(val), ':')[offset(0)]
  ) as testing
from your_table

if applied to sample data in your question - output is