时间戳

首页 > 解决方案 > 标准 SQL - STRUCT 数组中的多行

sql - 标准 SQL - STRUCT 数组中的多行

问题描述

我有一个类似于以下的 JSON 结构：

{"Name": "Value 1",
 "Colors": {
   {"Basic colors": {
      "Primary colors": {
         [{"First Color":"Blue", "Second Color": "Red"}]
      }
    }
  }
}

使用JSON_EXTRACT(Name.Colors, '$.Basic_colors[0].Primary_colors)I 可以返回结构的数组，然后从结构中提取值。但是，“原色”中可以有多个项目，例如：

[{"First Color":"Blue", "Second Color": "Red"},{"First Color":"Green", "Second Color": "Orange"}]

理想的解决方案：

Name    |   First Color  |  Second Color  

Value 1 |   Blue         |  Red

Value 1 |   Green        |  Orange

我遇到的问题是，当使用 JSON_EXTRACT 时，“Primary Colors”不被识别为数组或结构。它被视为一个字符串（有道理，但也不能是 CAST() - 所以不能是 UNNEST()。

我遇到的第二个问题是，虽然我可以在使用 JSON_extract 时索引到数组中的 [0] 元素，但我不能为每个元素循环生成包含所有子元素的新数据行。

我不能使用 FNSPLIT，因为标准 SQL 不支持它。我相信我想 UNNEST()，但我不知道如何将 STRUCT 转换为被识别为数组中的第一个元素。我的 python 饱和大脑想要循环：for item in range(len(json_array)):

我不能为此使用 Python（在生产中），并且必须有比在 SQL 宏中编写循环更简单的方法？

标签： sqlgoogle-bigquery

解决方案

2020 年 5 月 1 日更新

一个新函数 JSON_EXTRACT_ARRAY 刚刚添加到 JSON 函数列表中。此函数允许您将 JSON 文档的内容提取为字符串数组。

所以在下面你可以用json2array内置函数替换 UDF 的使用JSON_EXTRACT_ARRAY，如下例所示 

#standardSQL
SELECT 
  JSON_EXTRACT_SCALAR(json,'$.Name') Name, 
  JSON_EXTRACT_SCALAR(item, '$.First_Color') First_Color,
  JSON_EXTRACT_SCALAR(item, '$.Second_Color') Second_Color
FROM `project.dataset.table`, 
UNNEST(JSON_EXTRACT_ARRAY(json,'$.Colors.Basic_colors.Primary_colors')) item

===============

下面是 BigQuery 标准 SQL 的示例 

#standardSQL
CREATE TEMP FUNCTION JsonToArray(input STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
  return JSON.parse(input).map(x=>JSON.stringify(x));
"""; 
WITH `project.dataset.table` AS (
  SELECT '''{
  "Name": "Value 1",
  "Colors": {
    "Basic_colors": {"Primary_colors": [
      {"First_Color":"Blue", "Second_Color": "Red"},
      {"First_Color":"Green", "Second_Color": "Orange"}
     ]
   }
  }}''' json
)
SELECT 
  JSON_EXTRACT_SCALAR(json,'$.Name') Name, 
  JSON_EXTRACT_SCALAR(item, '$.First_Color') First_Color,
  JSON_EXTRACT_SCALAR(item, '$.Second_Color') Second_Color
FROM `project.dataset.table`, 
UNNEST(JsonToArray(JSON_EXTRACT(json,'$.Colors.Basic_colors.Primary_colors'))) item

带输出 

Row Name    First_Color Second_Color     
1   Value 1 Blue        Red  
2   Value 1 Green       Orange

推荐阅读