BigQuery query which returns distinct array union of all arrays where the object ID is the same

Question

I would like to query BigQuery where two columns are returned, a distinct object ID column, and a distinct combination of array values based on values of the arrays where the object IDs were the same.

For example, say I have this table in BigQuery:

+-----------+-----------+
| object_id |  array    |
+-----------+-----------+
| a         | 1,2,3,4,5 |
| a         | 2,3,4,5,6 |
| b         | 9,8,7,6   |
+-----------+-----------+

I would like to run a query that returns the following data:

+-----------+-------------+
| object_id |  array      |
+-----------+-------------+
| a         | 1,2,3,4,5,6 |
| b         | 9,8,7,6     |
+-----------+-------------+

I think the query should be something similar to this but am a little unclear on how to get the distinct object ID with the distinct combination of array elements.

Mikhail Berlyant · Accepted Answer · 2021-04-13 18:25:16Z

2

Consider below [slim] solution

select id, array_agg(distinct el) as arr
from `project.dataset.table`,
unnest(arr) el
group by id

answered Apr 13, 2021 at 18:25

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

Comments

Sergey Geron · Accepted Answer · 2021-04-13 16:13:08Z

0

Use array_concat_agg:

WITH `project.dataset.table` AS (
  SELECT 'a' id, [1,2,3,4,5] arr UNION ALL
  SELECT 'a', [2,3,4,5,6] UNION ALL
  SELECT 'b', [9,8,7,6]
)
SELECT
    id,
    ARRAY(SELECT DISTINCT x FROM UNNEST(concatenated) as x) as a
FROM (
  SELECT
    id,
    ARRAY_CONCAT_AGG(arr) as concatenated
  FROM `project.dataset.table`
  GROUP BY id
)

answered Apr 13, 2021 at 16:13

Sergey Geron

10.5k4 gold badges28 silver badges38 bronze badges

1 Comment

walshbm15 Over a year ago

Works perfectly! Thanks.

Collectives™ on Stack Overflow

BigQuery query which returns distinct array union of all arrays where the object ID is the same

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related