Nested JSON parsing using Snowflake SQL

Question

I have a problem parsing a certain nested JSON structure in Snowflake. The problem here is the usage of special characters like @ and # for example for some of the elements. Those characters prevents me from using simple dot notation when trying to access some of the elements without resourcing to a really complicated queries utilizing joins and where clauses on the flattened parts of the structure. Here's an example of how the JSON file looks like:

{
  "ContractTerm": [
    {
      "@ID": 123123,
      "CodeTermTypeID": {
        "#text": "some_text 123123"
      },
      "ContractID": {
        "#text": "other_text 123123",
        "@href": "/businessObject/123123",
        "@ID": 123123
      },
      "ContractTermID": 123123
    },
    {
      "@ID": 234234,
      "CodeTermStatusID": {
        "#text": "some_text_again 234234"
      },
      "CodeTermTypeID": {
        "#text": "some_text 234234"
      },
      "ContractID": {
        "#text": "some_other_text 234234",
        "@href": "/businessObject/234234",
        "@lxID": 234234
      },
      "ContractTermID": 234234
    },
    {
      "@lD": 345345,
      "CodeTermTypeID": {
        "#text": "another_text 345345"
      },
      "ContractID": {
        "#text": "another_text 345345",
        "@href": "/businessObject/345345",
        "@lxID": 345345
      },
      "ContractTermID": 345345
    }
  ]
}

Is it possible to get those elements starting with @ and # like using some escape characters or something similar in SQL code?

Please show us your current query.

GMB
– GMB

2020-10-28 15:30:40 +00:00
Commented Oct 28, 2020 at 15:30 — GMB
– GMB, Commented Oct 28, 2020 at 15:30

Mike Walton · Accepted Answer · 2020-10-28 15:36:58Z

1

Use quotes around the attributes with special characters. For example:

WITH x as (
SELECT parse_json('{
  "ContractTerm": [
    {
      "@ID": 123123,
      "CodeTermTypeID": {
        "#text": "some_text 123123"
      },
      "ContractID": {
        "#text": "other_text 123123",
        "@href": "/businessObject/123123",
        "@ID": 123123
      },
      "ContractTermID": 123123
    },
    {
      "@ID": 234234,
      "CodeTermStatusID": {
        "#text": "some_text_again 234234"
      },
      "CodeTermTypeID": {
        "#text": "some_text 234234"
      },
      "ContractID": {
        "#text": "some_other_text 234234",
        "@href": "/businessObject/234234",
        "@lxID": 234234
      },
      "ContractTermID": 234234
    },
    {
      "@lD": 345345,
      "CodeTermTypeID": {
        "#text": "another_text 345345"
      },
      "ContractID": {
        "#text": "another_text 345345",
        "@href": "/businessObject/345345",
        "@lxID": 345345
      },
      "ContractTermID": 345345
    }
  ]
}') as var)
SELECT y.value:"@ID"
FROM x,
LATERAL FLATTEN(input=>x.var:ContractTerm) y

answered Oct 28, 2020 at 15:36

Mike Walton

7,4592 gold badges14 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Martek Over a year ago

Thanks for help! It works, however when I'm trying to use the below query all I get are NULL value. It seems like I can't go down a level below using the "@ID"? SELECT y.value:"@ID" ,z.value:"@ID" FROM x, LATERAL FLATTEN(input=>x.var:ContractTerm) y, lateral flatten(input=>y.value:ContractID) z;

Mike Walton Over a year ago

Under ContractID is "@lxID" not "@ID". Could that be the issue? When you make reference to an attribute that isn't there, you'll always get a NULL.

Martek Over a year ago

OK - thank you again! I corrected it and it works perfectly now! Too bad it doesn't work with COPY INTO unfortunately as it's "too complicated"...

Collectives™ on Stack Overflow

Nested JSON parsing using Snowflake SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related