CODEWITHSUNDEEP
google-bigquery
Shahin Ghannadian
Shahin Ghannadian

Reputation: 295

nest multiple columns into an array in Big Query

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

Upvotes: 5

Views: 10420

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173191

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

Upvotes: 11

Related Questions