sql - BigQuery：最好地使用 UNNEST 数组

Question

我真的需要一些帮助，我有一个大文件 JSON 被提取到 BigQuery 中，我想编写一个使用 UNNEST 两次的查询，即我有这样的：

{
  "categories": [
  {
    "id": 1,
    "name" : "C0",
    "properties": [
      {
        "name": "Property_1",
        "value": {
          "type": "String",
          "value": "11111"
        }
      },
      {
        "name": "Property_2",
        "value": {
          "type": "String",
          "value": "22222"
        }
      }
    ]}
]}

我想做一个查询，给我这样的结果

---------------------------------------------------------------------
|     Category_ID  |  Name_ID   |   Property_1      |    Property_2 |
------------------------------------------------------------------
|          1       |   C0       |        11111      |        22222  |
---------------------------------------------------------------------

我已经做了类似的东西，但它不起作用：

SELECT 
   c.id as Category_ID,
   c.name as Name_ID,
   p.value.value as p.name
From `DataBase-xxxxxx` CROSS JOIN
UNNEST(categories) AS c, 
UNNEST(c.properties) AS p;

更谢谢你

标签： sqldatabaseselectgoogle-bigqueryunnest