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.