问如何将JSON数组中的值解析为BigQuery中的列

EN

#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   

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, [
    '{"key":"Email","slug":"customer-email","value":"abc@gmail.com"}',
    '{"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   

Row id  Email           PhoneNumber     FirstName   LastName    Dateofbirth  
1   1   abc@gmail.com   123456789       abc         xyz         01/01/1990