How to merge nested json in snowflake?

Question

object.assign is only perform the direct merge but its not working for nested json. If anyone worked on this, could you please share the steps.

For example,I want to update the phone number and city of the user. City is under the location property. How should i update the value of city?

Example:

const user = {
  name: "Liya",
  phone: 12345,
  location: {
    city: "Camden",
    country: "UK"
  }
};

const updates = {
  name: "David",
  phone: 12345678,
  location: {
    city: "Smithfield"
  }
};

Output should be like this:

console.log(Object.assign({}, user, updates));
{ 
  name: 'Liya',
  phone: 12345678,
  location: {
    country: 'UK',
    city: 'Smithfield'
  }
}

Answer 1

I'm assuming the name should be David since that's the name in the updates.

Based on @Han Moe Htet's comment, I used code from Vincent on that response. I used his because it does not require any external libraries, which Snowflake currently does not allow for Javascript UDFs.

There's an important consideration with this code. It uses recursion, and Snowflake UDFs have rather limited stack depth. If you have a highly nested object, it could run out of stack depth.

set USR = $${
  name: "Liya",
  phone: 12345,
  location: {
    city: "Camden",
    country: "UK"
  }
}$$;

set UPDATES = $${
  name: "David",
  phone: 12345678,
  location: {
    city: "Smithfield"
  }
}$$;

create or replace function MERGE_OBJECTS("obj1" object, "obj2" object)
returns object
language javascript
strict immutable 
as
$$

return merge(obj1, obj2);

function merge(current, updates) {
  for (key of Object.keys(updates)) {
    if (!current.hasOwnProperty(key) || typeof updates[key] !== 'object') current[key] = updates[key];
    else merge(current[key], updates[key]);
  }
  return current;
}

$$;

with x as 
(
    select parse_json($USR) as USR, parse_json($UPDATES) as UPDATES
)
select merge_objects(USR, UPDATES) from X;