CODEWITHSUNDEEP
jsongoogle-bigquery
nasiputih
nasiputih

Reputation: 83

How to extract JSON array stored as string in BigQuery

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

Upvotes: 2

Views: 9668

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

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

Upvotes: 1

Yun Zhang
Yun Zhang

Reputation: 5503

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"}]""");

Upvotes: 3

Related Questions