CODEWITHSUNDEEP
sqljsonsnowflake-cloud-data-platform
Lucila Ferreira
Lucila Ferreira

Reputation: 13

How can I convert a string to JSON in Snowflake?

I have this string {id: evt_1jopsdgqxhp78yqp7pujesee, created: 2021-08-14t16:38:17z} and would like to convert it to a JSON, I tried parse_json but got an error, to_variant and converted to "{id: evt_1jopsdgqxhp78yqp7pujesee, created: 2021-08-14t16:38:17z}"

Upvotes: 1

Views: 7305

Answers (2)

Rich Murnane
Rich Murnane

Reputation: 2920

To Gokhan & Simon's point, the original data isn't valid JSON.

If you're 100% (1000%) certain it'll "ALWAYS" come that way, you can treat it as a string parsing exercise and do something like this, but once someone changes the format a bit it'll have an issue.

create temporary table abc (str varchar);
insert into abc values ('{id: evt_1jopsdgqxhp78yqp7pujesee, created: 2021-08-14t16:38:17z}');


select to_json(parse_json(json_str)) json_json
FROM (
select split_part(ltrim(str, '{'), ',', 1) as part_a,
   split_part(rtrim(str, '}'), ',', 2) as part_b,
   split_part(trim(part_a), ': ', 1) part_a_name,
   split_part(trim(part_a), ': ', 2) part_a_val,
   split_part(trim(part_b), ': ', 1) part_b_name,
   split_part(trim(part_b), ': ', 2) part_b_val,
   '{"'||part_a_name||'":"'||part_a_val||'", "'||part_b_name||'":"'||part_b_val||'"}' as json_str
FROM abc);

which returns a valid JSON

{"created":"2021-08-14t16:38:17z","id":"evt_1jopsdgqxhp78yqp7pujesee"}

Overall this is very fragile, but if you must do it, feel free to.

Upvotes: 1

Gokhan Atil
Gokhan Atil

Reputation: 10039

Your JSON is not valid, as you can validate it using any online tool:

https://jsonlint.com/

This is a valid JSON version of your data:

{
    "id": "evt_1jopsdgqxhp78yqp7pujesee",
    "created": "2021-08-14t16:38:17z"
}

And you can parse it successfully using parse_json:

select parse_json('{ "id": "evt_1jopsdgqxhp78yqp7pujesee", "created": "2021-08-14t16:38:17z"}');

Upvotes: 1

Related Questions