How do I parse this JSON in a varchar column within my table? Snowflake

Question

I have a table with a column called "message_json" and within that table I have this json stored as a varchar data type.

{
    "request_id": "b53e7cc3-89b1-495b-aab0-e0dd6243b32e",
    "quote_id": "7a760b81-2c9c-4f20-9453-f7b72d4e06c6",
    "tenant_abbreviation": "ahs",
    "tenant_id": "ee312e77-8463-44bd-ad7e-2cd4e75c9e3d",
    "event_detail": {
        "source": "Quote service",
        "event_name": "quote_created",
        "timestamp": {
            "seconds": 1608236418,
            "nanos": 290575000
        },
        "id": "7a760b81-2c9c-4f20-9453-f7b72d4e06c6"
    },
    "quote": {
        "attribute": {
            "contract.renewal": "false",
            "contract.yearsOfService": "0",
            "description": "xx 3X3 xx $xx DC SC",
            "mktgSourceKey": "5fdc118555b95efff7d29f23",
            "order.method": "eCom",
            "originalSalesChannel": "",
            "plan.id": "xxx",
            "product.familyName": "combo",
            "product.name": "xx xx COMBO $x DC SC",
            "product.origin": "TX3C217D",
            "property.address1": "xxx xx xx",
            "property.address2": "",
            "property.ageOfHome": "",
            "property.city": "xxx",
            "property.country": "USA",
            "property.dwellingType": "1",
            "property.dwellingTypeCode": "SINGLE FAMILY RESIDENCE",
            "property.motherInLaw": "",
            "property.sizeOfHome": "4900",
            "property.state": "xxxx",
            "property.unitType": "",
            "property.unitValue": "",
            "property.zip5": "xxxxx",
            "property.zip9": "xxxxxx",
            "salesChannel": "DC",
            "serviceFee": "xxxx"
        }
}

I am trying to create a new table that assigns each key:value pair to a column. I've tried parse_json(message_json) and all it returns for some reason is this.

{
  "event_detail": {
    "event_name": "xxxxxx",
    "id": "77e49765-2b53-4d79-9442-8156b0bde3bc",
    "source": "xxx xxx",
    "timestamp": {
      "nanos": 830472300,
      "seconds": 1572679265
    }
  },
  "quote_id": "77e49765-2b53-4d79-9442-8156b0bde3bc",
  "request_id": "d8ad7a0a-f390-4660-8dc4-2838853d3846",
  "tenant_abbreviation": "xxx",
  "tenant_id": "ee312e77-8463-44bd-ad7e-2cd4e75c9e3d"
}

I've also tried message_json:request_id and I get this error Invalid argument types for function 'GET': (VARCHAR(16777216), VARCHAR(10))

Any help is appreciated

Felipe Hoffa Felipe Hoffa · Accepted Answer · 2020-12-19T00:18:29

I don't know what's wrong in your case, but I could fix the json string in the question by adding a closing bracket:

create or replace temp table texts as
select '{
    "request_id": "b53e7cc3-89b1-495b-aab0-e0dd6243b32e",
    "quote_id": "7a760b81-2c9c-4f20-9453-f7b72d4e06c6",
    "tenant_abbreviation": "ahs",
    "tenant_id": "ee312e77-8463-44bd-ad7e-2cd4e75c9e3d",
    "event_detail": {
        "source": "Quote service",
        "event_name": "quote_created",
        "timestamp": {
            "seconds": 1608236418,
            "nanos": 290575000
        },
        "id": "7a760b81-2c9c-4f20-9453-f7b72d4e06c6"
    },
    "quote": {
        "attribute": {
            "contract.renewal": "false",
            "contract.yearsOfService": "0",
            "description": "xx 3X3 xx $xx DC SC",
            "mktgSourceKey": "5fdc118555b95efff7d29f23",
            "order.method": "eCom",
            "originalSalesChannel": "",
            "plan.id": "xxx",
            "product.familyName": "combo",
            "product.name": "xx xx COMBO $x DC SC",
            "product.origin": "TX3C217D",
            "property.address1": "xxx xx xx",
            "property.address2": "",
            "property.ageOfHome": "",
            "property.city": "xxx",
            "property.country": "USA",
            "property.dwellingType": "1",
            "property.dwellingTypeCode": "SINGLE FAMILY RESIDENCE",
            "property.motherInLaw": "",
            "property.sizeOfHome": "4900",
            "property.state": "xxxx",
            "property.unitType": "",
            "property.unitValue": "",
            "property.zip5": "xxxxx",
            "property.zip9": "xxxxxx",
            "salesChannel": "DC",
            "serviceFee": "xxxx"
        }
}
}' input;

select parse_json(input):request_id
from texts

-- "b53e7cc3-89b1-495b-aab0-e0dd6243b32e"

It even comes back with the supposedly "missing" parts:

select parse_json(input):quote.attribute['property.address1']
from texts

-- "xxx xx xx"

How do I parse this JSON in a varchar column within my table? Snowflake

1 Answers