Posted on May 26

Converting JSON Data to Tabular in Snowflake — From SQL to SPL #32

Problem Description & Analysis:

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

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

Code Comparisons:

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: SPL supports multi-layer data and allows direct access to multi-layer structures in an object-oriented manner:

✅ Try.DEMO