从 SQL 到 SPL：Converting JSON data to Tabular in Snowflake
Snowflake 数据库有一个多层的 Json 串：

{
  "enterprise": "xx",
  "genericTrap": "1",
  "pduBerEncoded": "xxx",
  "pduRawBytes": "xxxx",
  "peerAddress": "xx",
  "peerPort": "xx",
  "securityName": "xxx",
  "specificTrap": "1",
  "sysUpTime": "xxxx",
  "variables": [
    {
      "oid": "column_a",
      "type": "octetString",
      "value": "vala"
    },
    {
      "oid": "column_b",
      "type": "integer",
      "value": "valb"
    }
  ]
}

现在要找出第 1 层字段 specificTrap，作为分组字段；找出第 1 层数组 variables，取出每个成员的 oid 和 value, 作为明细。

with table_a(col) as (
    select
        parse_json(
            '{
  "enterprise": "xx",
  "genericTrap": "1",
  "pduBerEncoded": "xxx",
  "pduRawBytes": "xxxx",
  "peerAddress": "xx",
  "peerPort": "xx",
  "securityName": "xxx",
  "specificTrap": "1",
  "sysUpTime": "xxxx",
  "variables": [
    {
      "oid": "column_a",
      "type": "octetString",
      "value": "vala"
    },
    {
      "oid": "column_b",
      "type": "integer",
      "value": "valb"
    }
  ]
}'
        ) as variant
)
select
    any_value(specifictrap) specifictrap,
    max(case oid when 'column_a' then oid_val else null end)  column_a,
    max(case oid when 'column_b' then oid_val else null end)  column_b
    
from
    (
        select
            f.seq seq,
            col:specificTrap::VARCHAR specifictrap,
            f.value:oid::VARCHAR oid,
            f.value:value::VARCHAR oid_val
        from
            table_a,
            lateral FLATTEN(input => table_a.col:variables::ARRAY) f
    ) t
group by
    seq;

SQL 不支持多层数据，要用嵌套查询和分组汇总间接实现，代码难懂。SPL 支持多层数据，可以用对象方式直接访问多层结构：https://try.esproc.com/splx?3hm

	A
1	{ "enterprise": "xx", "genericTrap": "1", "pduBerEncoded": "xxx", "pduRawBytes": "xxxx", "peerAddress": "xx", "peerPort": "xx", "securityName": "xxx", "specificTrap": "1", "sysUpTime": "xxxx", "variables": [ { "oid": "column_a", "type": "octetString", "value": "vala" }, { "oid": "column_b", "type": "integer", "value": "valb" } ] }
2	=A1.variables.new(A1.specificTrap, oid, value)

A1: 自动解析内置数据类型 json，可以来自 JDBC 或参数。

A2: 用 A1 的 variables 字段值新建二维表，oid 和 value 保留，specificTrap 取自从 A1。

问题来源：https://stackoverflow.com/questions/78078625/converting-json-data-to-tabular-in-snowflake