BigQuery JSON Field Extraction

Question

我有以下JSON有效载荷存储在BQ表中的单字符串列中。

{
"customer" : "ABC Ltd",
"custom_fields" : [
     {
      "name" : "DOB",
      "value" : "2000-01-01"
     },
     {
      "name" : "Account_Open_Date",
      "value" : "2019-01-01"
     }
]
}

我试图弄清楚如何将custom_fields name value对作为列提取？

类似以下的东西。

| Customer.name    | Customer.DOB    | Customer.Account_Open_Date |
| ABC Ltd          | 2000-01-01      | 2019-01-01                 |

Answer 1

您可以使用JSON功能，例如

JSON_EXTRACT(json_string_expr, json_path_string_literal)

在您的情况下将是

SELECT 
JSON_EXTRACT(json_text, '$.customer') as Customer.Name,
JSON_EXTRACT(json_text, '$.custom_fields[0].value') as Customer.DOB,
JSON_EXTRACT(json_text, '$.custom_fields[1].value') as Customer.Account_Open_Date