CODEWITHSUNDEEP
snowflake-cloud-data-platform
user1299412
user1299412

Reputation: 409

Snowflake merge object / json

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!

Upvotes: 9

Views: 5616

Answers (3)

ahwitz
ahwitz

Reputation: 33

Also for anyone stumbling over this with a fully dynamic case, I was stumbling through awkwardly nested MAP_CAT calls per the other answer here and realized I was just blanking on the fact that OBJECT_AGG existed.

Upvotes: 2

km6zla
km6zla

Reputation: 4907

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
}

Upvotes: 5

Marcin Zukowski
Marcin Zukowski

Reputation: 4739

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

Upvotes: 12

Related Questions