BigQuery: Aggregate multiple fields into array

Question

I have some data where for each ID I want to aggregate two or more fields into an array, and I want them to match in terms of order.

So for example if I have the following data:

I want to turn it into this:

Alternatively, something like this would also be fine:

So firstly, if I were to use a query like this, would it do what I want or does it not guarantee that the two fields pull through in the same order (i.e. that the corresponding values in Value_1 and Value_2 may not match)?

SELECT
  ID,
  ARRAY_AGG (
    Value_1
  ) AS Value_1,
  ARRAY_AGG (
    Value_2
  ) AS Value_2

FROM
  table

GROUP BY
  ID

If not, how can I go about doing this?

New Alexandria · Accepted Answer · 2022-05-04 21:58:30Z

62

Use ARRAY_AGG with STRUCT if you want to pair the values together. For example,

SELECT
  ID,
  ARRAY_AGG (
    STRUCT(Value_1, Value_2)
  ) AS Values
FROM
  table
GROUP BY
  ID;

edited May 4, 2022 at 21:58

New Alexandria

7,3366 gold badges62 silver badges83 bronze badges

answered Jul 21, 2017 at 17:30

Elliott Brossard

34k2 gold badges75 silver badges105 bronze badges

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

1 Comment

Hadi Ismanto Over a year ago

This is awesome...exactly what i need for a truly denormalized table

Mikhail Berlyant · Accepted Answer · 2017-07-21 19:18:54Z

7

... and for your alternative ask:

SELECT  
  id,
  ARRAY_AGG(CONCAT('[', Value_1, ',', Value_2, ']')) AS Values
FROM `yourTable`
GROUP BY id

answered Jul 21, 2017 at 19:18

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

2 Comments

stvnwrgs Over a year ago

This generates a string instead of a list as concat returns a string. Is this also possible as list in list?

Endrju Over a year ago

This is susceptible to SQL injection security vulnerability... Are we in '90s again?

Muhammad · Accepted Answer · 2018-10-19 16:07:48Z

-1

Working solution of Array_Agg () with Python BigQuery:

!pip install -U google-cloud-bigquery
import pandas as pd

from google.cloud import bigquery

strvalue = """SELECT users ARRAY_AGG(STRUCT(session, page )) as hasComp FROM <datasetname>.<tableName> WHERE Group by users order by users limit 100 """

bigquery_client = bigquery.Client(project="")

dataset = bigquery_client.dataset("")

table = dataset.table('')

table.view_query_legacy_sql = False

query_job = bigquery_client.query(str_value)

df = query_job.to_dataframe()

print(df)

edited Oct 19, 2018 at 16:07

Muhammad

3,2606 gold badges47 silver badges72 bronze badges

answered Oct 11, 2018 at 20:16

Kuldeep Singh

1

Collectives™ on Stack Overflow

BigQuery: Aggregate multiple fields into array

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related