How do you aggregate two columns into arrays in BigQuery?

Question

Say I have a table with 4 columns, a of type string, b of type integer, c of type integer, and d of type integer. How would I go ahead and use something like ARRAY_AGG on a STRUCT(b, c) and d separately (in other words, have two separate columns that would be arrays)?

The Query I have so far:

SELECT table1.a, table1.x, table2.y
FROM (
  SELECT a, ARRAY_AGG(STRUCT(b, c)) AS x
  FROM project.table
  GROUP BY a
  ORDER BY a
) AS table1
LEFT OUTER JOIN (
  SELECT a, ARRAY_AGG(d) AS y
  FROM project.table
  GROUP BY a, d
  ORDER BY a, d
) table2 ON table1.a = table2.a
GROUP BY table1.a
ORDER BY table1.a;

This gives me the error: SELECT list expression references table1.x which is neither grouped nor aggregated at [1:20]

But if I try to add table1.x to the GROUP BY clause at the end, I get a new error: Grouping by expressions of type ARRAY is not allowed at [14:22]

Mikhail Berlyant · Accepted Answer · 2020-01-15 20:52:37Z

2

Below is for BigQuery Standard SQL

#standardSQL
SELECT a, 
  ARRAY(
    SELECT AS STRUCT b, c FROM t.x GROUP BY b, c
  ) AS x, y
FROM (
  SELECT a,  
    ARRAY_AGG(STRUCT(b, c)) AS x, 
    ARRAY_AGG(DISTINCT d) AS y
  FROM `project.dataset.table`
  GROUP BY a
) t

answered Jan 15, 2020 at 20:52

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

1 Comment

markzz Over a year ago

Works beautifully!

Gordon Linoff · Accepted Answer · 2020-01-15 14:20:04Z

0

Why not just do this in one query?

SELECT a, ARRAY_AGG(STRUCT(b, c)) AS x, ARRAY_AGG(DISTINCT d) AS y
FROM project.table
GROUP BY a
ORDER BY a;

Your version will work without the outer GROUP BY. But it is needlessly compicated.

edited Jan 15, 2020 at 14:20

answered Jan 15, 2020 at 13:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

4 Comments

markzz Over a year ago

This doesn't actually work for me as x and y end up getting values repeated. Since you cannot just throw a DISTINCT to solve this problem, I figured a bit of complexity was needed to try and make two tables with what I wanted, then join them together.

Gordon Linoff Over a year ago

array_agg() supports distinct.

markzz Over a year ago

Not when STRUCT is used.

Gordon Linoff Over a year ago

@markzz . . . There is not need on the version with struct. Your query does not have it. And your question is about how to fix your query to run, not about producing a query to get a particular result. If you have a question about what logic to use, ask a NEW question with sample data, desired results, and an explanation of the logic.

Collectives™ on Stack Overflow

How do you aggregate two columns into arrays in BigQuery?

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related