如何将JSON数组中的值解析为BigQuery中的列 [英] How do I parse value from JSON array into columns in BigQuery

问题描述

我有一个与此相似的JSON数组

I have a JSON array that is similar to this

{"key":"Email","slug":"customer-email","value":"[e-mail]"}
{"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
[e-mail]   123456789      abc          xyz         01/01/1990

任何指南或建议都将不胜感激.

Any guides or inputs would be truly appreciated.

推荐答案

下面是BigQuery标准SQL

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":"[e-mail]"}',
    '{"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   [e-mail]   123456789       abc         xyz         01/01/1990  

这篇关于如何将JSON数组中的值解析为BigQuery中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！