Snowflake merge object / json

Question

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!

Parham

Answer 1

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  |
 }         |
-----------+

Ilke

Beautiful, thanks a lot! Hope it is performant enough though :) – Parham

well, it won't be as fast as a native function would be, that's true :( But hopefully it's fast enough! – Ilke

This UDF will fail if O1 is NULL -- but the solution is very simple; just add an empty object as the first parameter: return Object.assign({}, O1, O2); – Solvency

Answer 2

Try map_cat now in open preview.

select map_cat(
  parse_json('{"a":1,"b":2,"c":3}')::map(varchar,variant),
  parse_json('{"c":4, "d":5}')::map(varchar,variant)
) as res;

Returns:

{
  "a": 1,
  "b": 2,
  "c": 4,
  "d": 5
}

Voidance