How to create an array from flattened data in BigQuery

Question

There is a lot of information online to go from flattened data to arrays or structs, but I need to do the opposite and I am having a hard time archiving it. I am using Google BigQuery.

I have something like:

| Id | Value1 | Value2 |
|  1 |    1   |    2   |
|  1 |    3   |    4   |
|  2 |    5   |    6   |
|  2 |    7   |    8   |

I would like to get for the example above:

1, [(1, 2), (3, 4)]
2, [(5, 6), (7, 8)]

If I try to put an array in the select with a group by it is not a valid statement

For example:

SELECT Id, [ STRUCT(Value1, Value2) ] as Value
FROM `table.dataset`
GROUP BY Id

Which returns:

1, (1, 2)
1, (3, 4)
2, (5, 6)
2, (7, 8)

Which is not what I am looking for. The structure I got is: Id, Value.Value1, Value.Value2 and I want Id, [ Value(V1, V2), Value(V1, V2), ... ]

Martin Weitzmann · Accepted Answer · 2020-11-04 14:32:04Z

1

You can do that with SELECT Id, ARRAY_AGG(STRUCT(Value1, Value2)) ... GROUP BY Id

answered Nov 4, 2020 at 14:32

Martin Weitzmann

4,75612 silver badges22 bronze badges

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

Comments

Mikhail Berlyant · Accepted Answer · 2020-11-04 15:30:15Z

1

Below is for BigQuery Standard SQL

#standardSQL
select id, array_agg((select as struct t.* except(id))) as `value`
from `project.dataset.table` t
group by id

If to apply to sample data in your question - output is

answered Nov 4, 2020 at 15:30

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Collectives™ on Stack Overflow

How to create an array from flattened data in BigQuery

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related