How to Merge Json Objects in Snowflake?

One of the greatest strengths of Snowflake is that it can handle both structured and semi-structured data. Semi-structured data includes JSON and XML. Snowflake allows you to store and query the json or xml data without using any special functions. The built-in function such as merging two or more json object is not available as of now. But, you can make use of JavaScript function by writing Snowflake user defined function. In this article, we will check how to merge two json objects in Snowflake.

Merge JSON Objects in Snowflake

There is no out of the box built-in function to combine two or more json objects in Snowflake. We will write Snowflake user defined function using JavaScript to merge two json objects. Snowflake supports both SQL and JavaScript user-defined functions. We will make use of JavaScript UDF.

Before going into the JavaScript UDF, let us check little bit about the Object.assign() JavaScript function that we are going to use to merge two json objects.

Object.assign( ) in JavaScript

Among many object constructor methods, Object.assign() which is used to copy the values and properties from one or more source objects to a target object.

Following syntax is its syntax:

var new_obj = Object.assign(o1, o2, o3);

where, new_obj is a new object which is constructed using o1, o2 and o3.

Snowflake UDF to Merge JSON Objects

We will make use of Object.assign() inside our Snowflake user-defined function to combine two json object. Note that, you can enhance this UDF to merge two or more json objects.

Following is the Snowflake UDF to cobmine two json objects;

CREATE OR REPLACE FUNCTION json_merge(o1 VARIANT, o2 VARIANT)
  RETURNS VARIANT
  LANGUAGE JAVASCRIPT
AS
$$
  return Object.assign(O1, O2);
$$
;

Following is the output when you call it with two json objects.

SELECT json_merge(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  |
| }         |
+-----------+

SELECT json_merge(parse_json('{"a":1,"b":2,"c":3}'), parse_json('{"d":4, "e":5}')) as res;
+-----------+
| RES       |
|-----------|
| {         |
|   "a": 1, |
|   "b": 2, |
|   "c": 3, |
|   "d": 4, |
|   "e": 5  |
| }         |
+-----------+

Note that, if both objects has the same key, then the function will pick a value from second object.

You can apply this function on the table columns as well.

For example,

CREATE TABLE test_json_merge AS
  SELECT Parse_json('{"a":1,"b":2,"c":3}') AS j1,
         Parse_json('{"d":4, "e":5}')      AS j2; 

SELECT json_merge(j1, j2) from TEST_JSON_MERGE;
+--------------------+
| JSON_MERGE(J1, J2) |
|--------------------|
| {                  |
|   "a": 1,          |
|   "b": 2,          |
|   "c": 3,          |
|   "d": 4,          |
|   "e": 5           |
| }                  |
+--------------------+

The function will work on embedded json as well.

For example,

select json_merge(parse_json('{"a":1,"b":2,"c":3}'), parse_json('{"c":4, "d": {"e":5}}')) as res;
+------------+
| RES        |
|------------|
| {          |
|   "a": 1,  |
|   "b": 2,  |
|   "c": 4,  |
|   "d": {   |
|     "e": 5 |
|   }        |
| }          |
+------------+