Snowflake VARCHAR to VARIANT Inserting “\” characters

Question

I have some raw data in a varchar column but when I run the to_variant() function on it the data changes drastically. For example.

This json data stored in varchar

{
  "event_detail": {
    "event_name": "quote_created",
    "id": "679cc110-1c06-4f7d-aeed-1f637c0eff2d",
    "source": "Quote service",
    "timestamp": {
      "nanos": 730693300,
      "seconds": 1603366821
    }
  }

Turns into this

"{
   \"event_detail\": {
     \"event_name\": \"quote_created\",
     \"id\": \"679cc110-1c06-4f7d-aeed-1f637c0eff2d\",
     \"source\": \"Quote service\",
     \"timestamp\": {
       \"nanos\": 730693300,
       \"seconds\": 1603366821
    }
  }"

Has anyone seen this before? It makes parsing the variant extremely difficult? I've also seen the first json payload stored without "\" characters in variant data types before.

if you do select to_variant(<json here>) it will output the above. It doesn't seem to be escaping the quotes! — Bigmoose70

Gokhan Atil Gokhan Atil · Accepted Answer · 2021-02-10T00:00:17

As I know, it's expected behaviour. Instead of converting varchar to variant using to_variant function, you may use parse_json function. In this case, it would be easy to parse:

select parse_json('{  "event_detail": {
    "event_name": "quote_created",
    "id": "679cc110-1c06-4f7d-aeed-1f637c0eff2d",
    "source": "Quote service",
    "timestamp": {
      "nanos": 730693300,
      "seconds": 1603366821 }}}'):event_detail.event_name::String;

This will return: quote_created

Snowflake VARCHAR to VARIANT Inserting “\” characters

1 Answers