时间戳

首页 > 解决方案 > 使用 Snowflake SQL 进行嵌套 JSON 解析

sql - 使用 Snowflake SQL 进行嵌套 JSON 解析

问题描述

我在解析 Snowflake 中的某个嵌套 JSON 结构时遇到问题。这里的问题是对某些元素使用了特殊字符，例如 @ 和 #。这些字符使我无法在尝试访问某些元素时使用简单的点表示法，而无需使用连接和结构扁平部分上的 where 子句进行非常复杂的查询。下面是 JSON 文件的示例：

{
  "ContractTerm": [
    {
      "@ID": 123123,
      "CodeTermTypeID": {
        "#text": "some_text 123123"
      },
      "ContractID": {
        "#text": "other_text 123123",
        "@href": "/businessObject/123123",
        "@ID": 123123
      },
      "ContractTermID": 123123
    },
    {
      "@ID": 234234,
      "CodeTermStatusID": {
        "#text": "some_text_again 234234"
      },
      "CodeTermTypeID": {
        "#text": "some_text 234234"
      },
      "ContractID": {
        "#text": "some_other_text 234234",
        "@href": "/businessObject/234234",
        "@lxID": 234234
      },
      "ContractTermID": 234234
    },
    {
      "@lD": 345345,
      "CodeTermTypeID": {
        "#text": "another_text 345345"
      },
      "ContractID": {
        "#text": "another_text 345345",
        "@href": "/businessObject/345345",
        "@lxID": 345345
      },
      "ContractTermID": 345345
    }
  ]
}

是否可以让这些元素以 @ 和 # 开头，比如在 SQL 代码中使用一些转义字符或类似的东西？

标签： sqljsonsnowflake-cloud-data-platform

解决方案

在带有特殊字符的属性周围使用引号。例如：

WITH x as (
SELECT parse_json('{
  "ContractTerm": [
    {
      "@ID": 123123,
      "CodeTermTypeID": {
        "#text": "some_text 123123"
      },
      "ContractID": {
        "#text": "other_text 123123",
        "@href": "/businessObject/123123",
        "@ID": 123123
      },
      "ContractTermID": 123123
    },
    {
      "@ID": 234234,
      "CodeTermStatusID": {
        "#text": "some_text_again 234234"
      },
      "CodeTermTypeID": {
        "#text": "some_text 234234"
      },
      "ContractID": {
        "#text": "some_other_text 234234",
        "@href": "/businessObject/234234",
        "@lxID": 234234
      },
      "ContractTermID": 234234
    },
    {
      "@lD": 345345,
      "CodeTermTypeID": {
        "#text": "another_text 345345"
      },
      "ContractID": {
        "#text": "another_text 345345",
        "@href": "/businessObject/345345",
        "@lxID": 345345
      },
      "ContractTermID": 345345
    }
  ]
}') as var)
SELECT y.value:"@ID"
FROM x,
LATERAL FLATTEN(input=>x.var:ContractTerm) y

推荐阅读