How to Merge Variant columns in Snowflake

Currently, Snowflake does not have a native function to merge JSON data type, the workaround is to continuously use the OBJECT_INSERT, which can have performance implications. Please see the example below:

Table setup

create or replace table test (
    properties variant
);

insert into test
select object_construct('k1', 'v1', 'k2', 'v2');

select * from test;

+---------------+
| PROPERTIES    |
|---------------|
| {             |
|   "k1": "v1", |
|   "k2": "v2"  |
| }             |
+---------------+

In order to add more properties, we have to use the OBJECT_INSERT:

update test set properties = 
  object_insert(
    object_insert(
      object_insert(
        object_insert(properties, 'a1', 'v1'),
        'a2', 'v2'
      ),
      'a3', 'v3'
    ),
    'a4', 'v4'
  );

select * from test;
+---------------+
| PROPERTIES    |
|---------------|
| {             |
|   "a1": "v1", |
|   "a2": "v2", |
|   "a3": "v3", |
|   "a4": "v4", |
|   "k1": "v1", |
|   "k2": "v2"  |
| }             |
+---------------+

You can see how cumbersome it is to do so.

The better approach is to write a custom JS UDF to handle the merge for us, as below:

create or replace function object_merge(obj1 variant, obj2 variant)
returns variant
language javascript
as
$$
 return Object.assign(OBJ1, OBJ2)
$$;

Then use the below to perform the same operation as above:

update test set properties = object_merge(
  properties,
  object_construct('a1', 'v1', 'a2', 'v2', 'a3', 'v3', 'a4', 'v4')
);

select * from test;
+---------------+
| PROPERTIES    |
|---------------|
| {             |
|   "a1": "v1", |
|   "a2": "v2", |
|   "a3": "v3", |
|   "a4": "v4", |
|   "k1": "v1", |
|   "k2": "v2"  |
| }             |
+---------------+

You can see it has significantly simplified the query, and it will also help to boost the performance as well.