How do I parse value from JSON array into columns in BigQuery

Question

I have a JSON array that is 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"}

I am hoping to turn the array into columns like this

email|          phoneNumber |  firstName |  lastName |  dob
[email protected]   123456789      abc          xyz         01/01/1990

Any guides or inputs would be truly appreciated.

is it in BigQuery Table? if so - is this a repeated string or actually one string that represent json array? please clarify — Mikhail Berlyant
– Mikhail Berlyant, Commented Sep 4, 2020 at 3:57
Sorry for the lack of clarity. Yes, this is in BigQuery and this is a repeated string. — nasiputih
– nasiputih, Commented Sep 4, 2020 at 4:05

Mikhail Berlyant · Accepted Answer · 2020-09-04 04:09:57Z

Below is for BigQuery Standard SQL

#standardSQL
SELECT id, 
  MAX(IF(key = 'Email', value, NULL)) AS Email,
  MAX(IF(key = 'PhoneNumber', value, NULL)) AS PhoneNumber,
  MAX(IF(key = 'FirstName', value, NULL)) AS FirstName,
  MAX(IF(key = 'LastName', value, NULL)) AS LastName,
  MAX(IF(key = 'Dateofbirth', value, NULL)) AS Dateofbirth
FROM `project.dataset.table`,
UNNEST(ARRAY(
    SELECT AS STRUCT 
      REPLACE(JSON_EXTRACT_SCALAR(json, '$.key'), ' ', '') AS key,
      JSON_EXTRACT_SCALAR(json, '$.value') AS value
    FROM UNNEST(json_array) json
))
GROUP BY id

You can test, play with above using sample data from your question as in below example

#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_array
)
SELECT id, 
  MAX(IF(key = 'Email', value, NULL)) AS Email,
  MAX(IF(key = 'PhoneNumber', value, NULL)) AS PhoneNumber,
  MAX(IF(key = 'FirstName', value, NULL)) AS FirstName,
  MAX(IF(key = 'LastName', value, NULL)) AS LastName,
  MAX(IF(key = 'Dateofbirth', value, NULL)) AS Dateofbirth
FROM `project.dataset.table`,
UNNEST(ARRAY(
    SELECT AS STRUCT 
      REPLACE(JSON_EXTRACT_SCALAR(json, '$.key'), ' ', '') AS key,
      JSON_EXTRACT_SCALAR(json, '$.value') AS value
    FROM UNNEST(json_array) json
))
GROUP BY id

with output

Row id  Email           PhoneNumber     FirstName   LastName    Dateofbirth  
1   1   [email protected]   123456789       abc         xyz         01/01/1990

Collectives™ on Stack Overflow

How do I parse value from JSON array into columns in BigQuery

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related