Converting JSON Data to Tabular in Snowflake — From SQL to SPL #32
#esprocspl#sql#snowflke#tabular
Judith-Data-Processing-Hacks

Judith-Data-Processing-Hacks @judith677

About: esProc SPL is a JVM-based programming language designed for structured data computation, serving as both a data analysis tool and an embedded computing engine. https://www.esproc.com/download-esproc

Joined:
Apr 20, 2022

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

Publish Date: May 26
6 1

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"
    }
  ]
}
Enter fullscreen mode Exit fullscreen mode

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.

expected results

Code Comparisons:
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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

esProc SPL code

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.

Experience esProc SPL FREE Download — Free Trial, No Hassle!

Comments 1 total

Add comment