From SQL to SPL：Converting JSON data to Tabular in Snowflake

The Snowflake database has a multi-layered JSON string:

{
  "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"
    }
  ]
}

Now we need to find the first layer field specificTrap as the grouping field; Find the first layer array variables, extract the oid and value of each member as details.

specificTrap	oid	value
1	column_a	vala
1	column_b	valb

SQL：

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 does not support multiple layers of data and requires indirect implementation through nested queries and grouping aggregation, making the code difficult to understand. SPL supports multi-layer data and allows direct access to multi-layer structures in an object-oriented manner:

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: Automatically parse built-in data type JSON, which can come from JDBC or parameters.

A2: Create a new two-dimensional table using the variables field values from A1, with OID and value retained, and specificTrap taken from A1.

Question source：https://stackoverflow.com/questions/78078625/converting-json-data-to-tabular-in-snowflake