How to convert Postgres Hstore data type to Snowflake Object or Variant

Question

I am migrating a Postgres DB to Snowflake and facing the next problem. My input is a csv file stored in s3, which has the data of a Postgres table. I want to store a Postgres HSTORE field as an Snowflake Object or Variant. To simplify the problem we could imagine the next query that contains an hstore field after read our csv data from s3 that looks like: '"User__id"=>"31486183"' and I need a format like '{"User__id":"31486183"}' which is understood by Snowflake as a JSON. For example:

select parse_json('"User__id"=>"31486183"')

throw the following error.

SQL compilation error: syntax error line 1 at position 28 unexpected '=>'. syntax error line 1 at position 28 unexpected '=>'.

We want to transform that expression into this other:

select parse_json('{"User__id":"31486183"}')

{ "User__id": "31486183" }

The only workaround that I have found is convert the HSTORE field in the Postgres side with the function hstore_to_json but I can't use this approach to solve my problem

Answer 1

A work around that I found but I would like to avoid is convert the format manually:

select parse_json(CONCAT('{',REPLACE('"User__id"=>"31486183"','=>',':'),'}'));

{ "User__id": "31486183" }

We can create a function with this logic:

CREATE OR REPLACE FUNCTION CONVERT_HSTORE_TO_JSON(str STRING) RETURNS VARIANT AS 'select PARSE_JSON(CONCAT(\'{\',REPLACE(str,\'=>\',\':\'),\'}\'))';

And use it in a cleaner way (but not more efficient):

select CONVERT_HSTORE_TO_JSON('"User__id"=>"31486183"');

and access to our field as a JSON field:

select CONVERT_HSTORE_TO_JSON('"User__id"=>"31486183"'):"User__id";

"31486183"