Parse string as JSON with Snowflake SQL

Question

I have a field in a table of our db that works like an event-like payload, where all changes to different entities are gathered. See example below for a single field of the object:

'---\nfield_one: 1\nfield_two: 20\nfield_three: 4\nid: 1234\nanother_id: 5678\nsome_text: Hey you\na_date: 2022-11-29\nutc: this_utc\nanother_date: 2022-11-30\nutc: another_utc'

Since accessing this field with pure SQL is a pain, I was thinking of parsing it as a JSON so that it would look like this:

{
  "field_one":"1", 
  "field_two": "20", 
  "field_three": "4", 
  "id": "1234",
  "another_id": "5678",
  "some_text": "Hey you",
  "a_date": "2022-11-29",
  "utc": "2022-11-29 15:29:28.159296000 Z",
  "another_date": "2022-11-30",
  "utc": "2022-11-30 13:34:59.000000000 Z"
}

And then just use a Snowflake-native approach to access the values I need.

As you can see, though, there are two fields that are called utc, since one is referring to the first date (a_date), and the second one is referring to the second date (another_date). I believe these are nested in the object, but it's difficult to assess with the format of the field.

This is a problem since I can't differentiate between one utc and another when giving the string the format I need and running a parse_json() function (due to both keys using the same name).

My SQL so far looks like the following:

select
    object,
    replace(object, '---\n', '{"') || '"}' as first,
    replace(first, '\n', '","') as second_,
    replace(second_, ': ', '":"') as third,
    replace(third, '    ', '') as fourth,
    replace(fourth, '  ', '') as last
from my_table

(Steps third and fourth are needed because I have some fields that have extra spaces in them)

And this actually gives me the format I need, but due to what I mentioned around the utc keys, I cannot parse the string as a JSON.

Also note that the structure of the string might change from row to row, meaning that some rows might gather two utc keys, while others might have one, and others even five.

Any ideas on how to overcome that?

Answer 1

In case anyone is looking for a cleaner approach to this problem, I came up with a Python UDF in Snowflake that leverages the ruamel.yaml library, and transforms the YAML into a JSON field without the need of ugly SQL:

create or replace function <your_target_schema>.yaml_to_json(S string)
returns string
language python
runtime_version = 3.8
handler = 'yaml_to_json_py'
packages = ('ruamel.yaml==0.17.21')
as $$

import json
from ruamel.yaml import YAML, parser

def yaml_to_json_py(S):
  if S is not None:
    try:
      input_stream = S
      yaml = YAML(typ='rt', pure=True)
      loaded_yaml = yaml.load(input_stream)
      json_str = json.dumps(loaded_yaml, default=str)
      return json_str
    except parser.ParserError:
      return None
  else:
    return None

$$;

This UDF will take a field from a table as input (it's meant to be in YAML format), convert it into a JSON, and return the latter as output.

Answer 2

This may not be what you want but it seems to me that your problem could be solved if the UTC timestamps were to replace the dates preceding it where the keys are not duplicated. You can always calculate dates once you have the timestamps. If this is making sense, see if you can apply your parse_json solution to this output instead

set str='---\nfield_one: 1\nfield_two: 20\nfield_three: 4\nid: 1234\nanother_id: 5678\nsome_text: Hey you\na_date: 2022-11-29\nutc: 2022-11-29 15:29:28.159296000 Z\nanother_date: 2022-11-30\nutc: 2022-11-30 13:34:59.000000000 Z';

               
select regexp_replace($str,'[0-9]{4}-[0-9]{2}-[0-9]{2}\nutc:')

Answer 3

Replace only one occurrence with regexp_replace():

with data as (
    select '---\nfield_one: 1\nfield_two: 20\nfield_three: 4\nid: 1234\nanother_id: 5678\nsome_text: Hey you\na_date: 2022-11-29\nutc: this_utc\nanother_date: 2022-11-30\nutc: another_utc' o
)

select parse_json(last2)
from (
    select o,
        replace(o, '---\n', '{"') || '"}' as first,
        replace(first, '\n', '","') as second_,
        replace(second_, ': ', '":"') as third,
        replace(third, '    ', '') as fourth,
        replace(fourth, '  ', '') as last,
        regexp_replace(last, '"utc"', '"utc2"', 1, 2) last2
    from data
)
;