Concatenate Repeated Record in Google Big Query

user_11923598 0
Viewed 379

I have data that exists in the following JSON structure

{"person": "John", "children": [{"name":"Tim", "age":9},{"name":"Suszan", "age":12},{"name":"Karl", "age":14}]}

and in Big Query, it has the schmea

children        RECORD  REPEATED    
children. age   INTEGER NULLABLE    
children. name  STRING  NULLABLE    
person          STRING  NULLABLE

When we look at the actual table, we have this

enter image description here

but I really would like to have the data in the following format

enter image description here

I'm stuck because I know I can use unnest(children) as children to have access to the record, but it creates an new row entirerly, but I can't use ARRAY_TO_STRING(children.name) because it's not purely an array. I'm kind of caught between the two.

Thank you.

google-bigquery sql
2 Answers

You should be able to access the elements and aggregate that:

select t.person, string_agg(child, ',')
from t cross join
     unnest(children) child
group by t.person;
user_11440350

I would rather suggest below version 

#standardSQL
SELECT person, 
  (SELECT STRING_AGG(name) FROM t.children) AS children
FROM `project.dataset.table` t
user_52219440
Related