Nested JSON parsing using Snowflake SQL

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

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top