Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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!

like image 452
user1299412 Avatar asked Nov 01 '18 16:11

user1299412


People also ask

How do I merge two JSON objects?

JSONObject to merge two JSON objects in Java. We can merge two JSON objects using the putAll() method (inherited from interface java.

Can we merge two JSON files?

We can merge two JSON arrays using the addAll() method (inherited from interface java. util. List) in the below program.

Can snowflakes ingest JSON?

One of the key differentiators in Snowflake Cloud Data Platform is the ability to natively ingest semi-structured data such as JSON, store it efficiently, and then access it quickly using simple extensions to standard SQL.


1 Answers

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  |
 }         |
-----------+
like image 146
Marcin Zukowski Avatar answered Sep 22 '22 16:09

Marcin Zukowski