Grouping and generating an array of arrays with Google SQL

Question

I was using Google SQL and I ran into the following challenge. For practical issues, let's assume we have the following table:

TABLE_1

Status (string)	Values (array)
Status 1	`[a,b]`
Status 1	`[c,d]`
Status 1	`[e,f]`
Status 2	`[g,h]`
Status 2	`[i,j]`

And the desired outcome is the table_2, which should be structured as follows:

TABLE_2

Status (string)	Values (array of arrays)
Status 1	`[[a,b],[c,d],[e,f]]`
Status 2	`[[g,h],[i,j]]`

I have tried using array grouping functions but I didn't manage to get SQL to build arrays of arrays in the process of grouping by status. What am I missing?

Nested arrays are not supported in BigQuery. So, you need to revisit your expected output — Mikhail Berlyant
– Mikhail Berlyant, Commented Feb 2, 2022 at 17:56

John Shepard · Accepted Answer · 2022-02-02 18:49:04Z

1

According to Mikhail, this is not possible with BigQuery, so I am closing this thread.

answered Feb 2, 2022 at 18:49

John Shepard

9471 gold badge9 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

Grouping and generating an array of arrays with Google SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related