Snowflake merge object / json

5
votes

is there any way how to merge 2 objects in snowflake? I found https://docs.snowflake.net/manuals/sql-reference/functions/object_insert.html, but that only sets/updates one key at a time. I want to merge 2 objects (something like Object.assign() in js). Also tried to find workaround by converting to array, concatenating and construction object from that array, but did not manage to make it work.

Thanks!

1
snowflake-cloud-data-platform

1 Answers

7
votes

Snowflake does not have a built-in function like that, but it's trivial to do using, well, Object.assign() inside Snowflake's JavaScript UDFs :)

create or replace function my_object_assign(o1 VARIANT, o2 VARIANT) 
returns VARIANT 
language javascript 
as 'return Object.assign(O1, O2);';

select my_object_assign(parse_json('{"a":1,"b":2,"c":3}'), parse_json('{"c":4, "d":5}')) as res;
-----------+
    RES    |
-----------+
 {         |
   "a": 1, |
   "b": 2, |
   "c": 4, |
   "d": 5  |
 }         |
-----------+