snowflake-cloud-data-platform - 使用 SnowFlake 查询多级数组

Question

我正在使用 SNOW_FLAKE 并尝试查询以列名multi_array下的多级元素数组形式存储的数据，例如：

多数组

[
  {
    "attribute_1": "hello",
    "attribute_2": "hello1",
    "group_attrbutes": [
      {
        "grp_attr1": "tst_val",
        "grp_attr2": "test_val2"
      }
    ]
  }
]

展平的输出将是：

attribute_1 attribute_2 grp_attr1 grp_attr2
hello     hello1        tst_val  tast_val2

谁能告诉我如何展平 group_attrbutes 数组，以便它以表格形式出现

标签： snowflake-cloud-data-platformsnowflake-schema

Answer 1

SELECT d.json
    ,f.value:attribute_1 as attribute_1
    ,f.value:attribute_2 as attribute_2
    ,g.value:grp_attr1 as grp_attr1
    ,g.value:grp_attr2 as grp_attr2
FROM (
    SELECT parse_json('[
    {
      "attribute_1": "hello",
      "attribute_2": "hello1",
      "group_attrbutes": [
        {
          "grp_attr1": "tst_val",
          "grp_attr2": "test_val2"
        }
      ]
    }
    ]') as json
) AS d,
table(flatten(input => d.json)) f,
table(flatten(input => f.value:group_attrbutes)) g
  ;

给出（去掉了 JSON）：

ATTRIBUTE_1 ATTRIBUTE_2  GRP_ATTR1  GRP_ATTR2
"hello"     "hello1"     "tst_val"  "test_val2"