nest multiple columns into an array in Big Query

Question

Given this BQ Table

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

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

Mikhail Berlyant · Accepted Answer · 2018-09-15 23:30:18Z

11

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

answered Sep 15, 2018 at 23:30

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

nest multiple columns into an array in Big Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related