CODEWITHSUNDEEP
sqlgoogle-bigquery
Gusti Bimo Marlawanto
Gusti Bimo Marlawanto

Reputation: 71

Can I convert row value contains of stringified array to array in BigQuery

I have a row which the value is the string of array. I need to convert it to the array.

enter image description here

I tried to convert it by strip the quote using SUBSTR function.

WITH k AS (SELECT 1 as id, (SELECT after.ClientIds   
FROM `kube-playground.events.login_v2_users`) AS c)

Select id, ARRAY(SELECT * FROM UNNEST(SPLIT(SUBSTR(c, 2 , LENGTH(c) - 2)))) AS x from k

It is failed because Scalar subquery produced more than one element. Any tips or ideas for this problem? Thank you

Upvotes: 0

Views: 304

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173038

Below is for BigQuery Standard SQL

You can simply use JSON_EXTRACT_ARRAY as in below example

#standardSQL
SELECT JSON_EXTRACT_ARRAY(ClientIds)
FROM `kube-playground.events.login_v2_users`

You can test it as in below

#standardSQL
WITH `kube-playground.events.login_v2_users` AS (
  SELECT '[{"time":"asd", "id":"qwe"},{"time":"asd2", "id":"qwe2"},{"time":"asd3", "id":"qwe3"}]' ClientIds UNION ALL
  SELECT '[{"time":"aseft", "id":"sdf"},{"time":"aseft2", "id":"sdf2"}]'
)
SELECT JSON_EXTRACT_ARRAY(ClientIds)
FROM `kube-playground.events.login_v2_users`

with result

Row f0_  
1   {"time":"asd","id":"qwe"}    
    {"time":"asd2","id":"qwe2"}  
    {"time":"asd3","id":"qwe3"}  
2   {"time":"aseft","id":"sdf"}  
    {"time":"aseft2","id":"sdf2"}

Upvotes: 1

Related Questions