nest multiple columns into an array in Big Query

user_9059135 0
Viewed 5810

Given this BQ Table BQ Table enter image description here

there are 1.026 rows with 944 unique modemio_cat_ids

how can I return a query that nests all non null columns into 1 single array called "parents" for each modemio_cat_id ?

example: for modemio_cat_id = 1111118

nested catx_id into parent

and then finally group by modemio_cat_id + cumulate all array in case of duplicates

wrong approach: this query returns always the same arrays for each modemutti_cat_id:

SELECT modemio_cat_id, ARRAY (
  SELECT AS STRUCT cat1_id, cat2_id FROM `modemutti-8d8a6.categorization.test`
  ) as parent
FROM `modemutti-8d8a6.categorization.test` 
group by modemio_cat_id
google-bigquery
1 Answers

Below example for BigQuery Standard SQL 

#standardSQL
SELECT modemio_cat_id, 
  ARRAY_AGG(DISTINCT cat_id IGNORE NULLS) parents
FROM `modemutti-8d8a6.categorization.test`,
  UNNEST([cat1_id, cat2_id, cat3_id, cat4_id, cat5_id, cat6_id]) cat_id
GROUP BY modemio_cat_id
user_52219440
Related