How to extract JSON array stored as string in BigQuery

Question

I have a JSON array that looks similar to this

[{"key":"Email","slug":"customer-email","value":"[email protected]"},{"key":"Phone Number","slug":"mobile-phone-number","value":"123456789"},{"key":"First Name","slug":"first-name","value":"abc"},{"key":"Last Name","slug":"last-name","value":"xyz"},{"key":"Date of birth","slug":"date-of-birth","value":"01/01/1990"}]

But the tricky part is, this array is stored as string. So I am thinking that the first step would be to convert the string into array then unnest it then follow the method in here

I wonder if this method is doable, if so I guess the challenge that I am having is to convert string into array. If not, or if you have more efficient method please help. Thanks

Yun Zhang · Accepted Answer · 2020-10-02 06:56:25Z

3

Have you tried json_extract_array

select json_extract_array(
  """[{"key":"Email","slug":"customer-email","value":"[email protected]"},{"key":"Phone Number","slug":"mobile-phone-number","value":"123456789"},{"key":"First Name","slug":"first-  name","value":"abc"},{"key":"Last Name","slug":"last-name","value":"xyz"},{"key":"Date of birth","slug":"date-of-birth","value":"01/01/1990"}]""");

answered Oct 2, 2020 at 6:56

Yun Zhang

5,6032 gold badges14 silver badges30 bronze badges

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

Comments

Mikhail Berlyant · Accepted Answer · 2020-10-02 06:56:56Z

1

Below is for BigQuery Standard SQL

#standardSQL
SELECT 
  id, 
  JSON_EXTRACT_ARRAY(json_string) AS json_array
FROM `project.dataset.table`

if to apply to sample data from your question as in below

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, '[{"key":"Email","slug":"customer-email","value":"[email protected]"},{"key":"Phone Number","slug":"mobile-phone-number","value":"123456789"},{"key":"First Name","slug":"first-name","value":"abc"},{"key":"Last Name","slug":"last-name","value":"xyz"},{"key":"Date of birth","slug":"date-of-birth","value":"01/01/1990"}]' json_string
)
SELECT 
  id, 
  JSON_EXTRACT_ARRAY(json_string) AS json_array
FROM `project.dataset.table`

output is

Row id  json_array 
1   1   {"key":"Email","slug":"customer-email","value":"[email protected]"}  
        {"key":"Phone Number","slug":"mobile-phone-number","value":"123456789"}  
        {"key":"First Name","slug":"first-name","value":"abc"}   
        {"key":"Last Name","slug":"last-name","value":"xyz"}     
        {"key":"Date of birth","slug":"date-of-birth","value":"01/01/1990"}

From this point - you can use solution in How do I parse value from JSON array into columns in BigQuery that you referenced in your question

answered Oct 2, 2020 at 6:56

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

1 Comment

j ' Over a year ago

I have same JSON array stored as STRING in one of my column. I need to extract value of "value" i.e just email_id as result of query for entire column. How can i do it in single select query ? i.e string is '[{"key":"Email","slug":"customer-email","value":"[email protected]"}' How do i extract '[email protected]' as query result.

Collectives™ on Stack Overflow

How to extract JSON array stored as string in BigQuery

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related