Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Converting JSON data to Tabular in Snowflake

I have this JSON raw data that is injected to Snowflake via Kafka from devices. I am trying to convert some of the values to a table using Snowflake’s Dynamic Table (so basiacally I just need to fix the SELECT statement).

This is the snapshot of the JSON data, the column name is RECORD_CONTENT.

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

the expected output is this:

column_a | column_b | specific_trap

xxx | xxx | xx

For other devices which I only need to parse Variables, I have been able to do with this basic query:

SELECT 
  MAX(CASE WHEN f.value:oid::STRING = 'column_a' THEN f.value:value::STRING END) AS column_a,
  MAX(CASE WHEN f.value:oid::STRING = 'column_b' THEN f.value:value::STRING END) AS column_b
FROM table_a,
    LATERAL FLATTEN(input => RECORD_CONTENT:variables) f
GROUP BY
    f.seq

However, introducing the specific trap column proves to be a bit challenging for me. Can you please help me?

Thank you in advanced.

Edit: specific trap only has 2 values, either 1 or 2. Hence, groupping by specific trap is not possible.

Tags:

2

Answers


  1. 0

    select
    max(case when rc.value:oid::string=’column_a’ then rc.value:value::string end) column_a
    ,max(case when rc.value:oid::string=’column_b’ then rc.value:value::string end) column_b
    ,record_content:specificTrap::string as specific_trap

    from json_data a,
    lateral flatten(input=>record_content:variables) rc
    group by specific_trap

    Login or Signup to reply.
  2. 0

    You probably need to address one task at a time. First, extract the fields a table with the array variables into rows, and then transpose them, for example:

    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
    --seq,
    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;

    On the first step you produce an intermediate table like this:

    SEQ SPECIFICTRAP OID OID_VAL
    1 1 column_a vala
    1 1 column_b valb

    Then, it’s easy enough to transpose the table to get the desired result.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search