BigQuery, Converting string into an ARRAY

Question

I have the following table in BigQuery:

A	B
First	[joe, becky, smith]
Second	[joe, matthew]

Column B has type 'STRING'

I want to convert Column B into a big query array of ARRAY

I attempted to use JSON_EXTRACT_ARRAY but this does not work as the elements inside the arrays of B are not enclosed within double quotes (") (i.e. they are not of the form, ["joe", "becky", "smith"])

Mikhail Berlyant · Accepted Answer · 2021-03-03 19:33:54Z

3

Consider below

select a, 
  array(select trim(val) from unnest(split(trim(b, '[]'))) val) b
from `project.dataset.table`

when applied to sample data in your question - output is

answered Mar 3, 2021 at 19:33

Mikhail Berlyant

174k10 gold badges172 silver badges250 bronze badges

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

Collectives™ on Stack Overflow

BigQuery, Converting string into an ARRAY

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