Convert SQL Data in columns into an array

Question

Imagine you have a simple table

  Key c1 c2 c3
A id1  x  y  z
B id2  q  r  s

what I would like is a query that gives me the result as 2 arrays so something like

Select
id1,
ARRAY_MAGIC_CREATOR(c1, c2, c3)
from Table

With the result being

id1, <x,y,z>
id2, <q,r,s>

Almost everything I have searched for end up converting rows to arrays or other similar sounding but very different requests.

Does something like this even exist in SQL? Please note that the data type is NOT a string so we can't use string concat. They are all going to be treated as floats.

Gordon Linoff · Accepted Answer · 2021-03-03 21:04:24Z

3

It is called ARRAY:

Select id1, ARRAY[c1, c2, c3] as c_array
from Table

answered Mar 3, 2021 at 21:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Mikhail Berlyant · Accepted Answer · 2021-03-03 21:12:31Z

1

This will also work :o)

select key, [c1, c2, c3] c
from `project.dataset.table`

answered Mar 3, 2021 at 21:12

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Comments

Mikhail Berlyant · Accepted Answer · 2021-03-03 21:26:56Z

1

Consider below generic option which does not require you to type all columns names or even to know them in advance - more BigQuery'ish way of doing business :o)

select key, 
  regexp_extract_all(
    to_json_string((select as struct * except(key) from unnest([t]))),
    r'"[^":,]+":([^":,]+)(?:,|})'
  ) c
from `project.dataset.table` t

If applied to sample data in your question - output is

answered Mar 3, 2021 at 21:26

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Collectives™ on Stack Overflow

Convert SQL Data in columns into an array

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related