Concatenating arrays in bigquery with empty arrays

Question

I have a table that looks like this:

Then I try to aggregate the label1, label2, label3 into one array for each type of label and then, finally I want to put all the non null labels into one combined array. So my query looks like this

#standardSQL
WITH
table AS (
SELECT 'abc' id, 1 label1, 12 label2, 122 label3 UNION ALL
SELECT 'abc', 1, 12, 129 UNION ALL
SELECT 'xyz', 2, 23, NULL UNION ALL
SELECT 'xyz', 2, 24, NULL
),

each_label_agg AS (
 SELECT
 id,
 ARRAY_AGG(label1 IGNORE NULLS) AS label1_agg,
 ARRAY_AGG(label2 IGNORE NULLS) AS label2_agg,
 ARRAY_AGG(label3 IGNORE NULLS) AS label3_agg
FROM
table
GROUP BY
 id)
SELECT
 each_label_agg.*,
 ARRAY_CONCAT(each_label_agg.label1_agg, each_label_agg.label2_agg, 
 each_label_agg.label3_agg) AS combined_labels
FROM
 each_label_agg

And the output looks like this:

But in the output I was expecting the combined_labels to be [2,2,23,24] for id xyz.

The ignore nulls parameter doesn't work in array_concat. I am guessing somehow the combined_labels become malformed because of an empty array at label3. How can I get the expected combined_labels for xyz to be [2,2,23,24]?

Mikhail Berlyant · Accepted Answer · 2018-12-10 23:11:45Z

5

#standardSQL
WITH table AS (
  SELECT 'abc' id, 1 label1, 12 label2, 122 label3 UNION ALL
  SELECT 'abc', 1, 12, 129 UNION ALL
  SELECT 'xyz', 2, 23, NULL UNION ALL
  SELECT 'xyz', 2, 24, NULL
), each_label_agg AS (
 SELECT
   id,
   ARRAY_AGG(label1 IGNORE NULLS) AS label1_agg,
   ARRAY_AGG(label2 IGNORE NULLS) AS label2_agg,
   ARRAY_AGG(label3 IGNORE NULLS) AS label3_agg
  FROM table
  GROUP BY id
)
SELECT
  each_label_agg.*,
  ARRAY_CONCAT(
   IFNULL(each_label_agg.label1_agg, []), 
   IFNULL(each_label_agg.label2_agg, []),
   IFNULL(each_label_agg.label3_agg, [])
  ) AS combined_labels
FROM each_label_agg

answered Dec 10, 2018 at 23:11

Mikhail Berlyant

174k10 gold badges172 silver badges250 bronze badges

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

1 Comment

Syed Arefinul Haque Over a year ago

Works perfectly as I wanted!! Thanks again. But the way it had to be handled seemed to me a bit counter-intuitive.

enle lin · Accepted Answer · 2018-12-12 09:45:59Z

1

The cause of this issue is because BigQuery has limitations respect to NULL values and the array generation will return NULL if any argument is NULL as documented here. Therefore, it is convenient to substitute them for empty arrays (since NULLs and empty arrays are two distinct values in BigQuery)

answered Dec 12, 2018 at 9:45

enle lin

1,71410 silver badges15 bronze badges

Collectives™ on Stack Overflow

Concatenating arrays in bigquery with empty arrays

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related