Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Deep Merge two jsonb documents in PostgreSQL
CREATE OR REPLACE FUNCTION jsonb_merge(left JSONB, right JSONB) RETURNS JSONB AS $$
var mergeJSON = function (target, add) {
function isObject(obj) {
if (typeof obj == "object") {
for (var key in obj) {
if (obj.hasOwnProperty(key)) {
return true; // search for first object prop
}
}
}
return false;
}
for (var key in add) {
if (add.hasOwnProperty(key)) {
if (target[key] && isObject(target[key]) && isObject(add[key])) {
mergeJSON(target[key], add[key]);
} else {
target[key] = add[key];
}
}
}
return target;
};
return mergeJSON(left, right);
$$ LANGUAGE plv8;
@phillip-haydon

This comment has been minimized.

Copy link
Owner Author

@phillip-haydon phillip-haydon commented Dec 6, 2016

select jsonb_pretty(jsonb_merge('{"a":{"nested":1}}'::jsonb, '{"a":{"also nested":2}}'::jsonb));

Result:

{
    "a": {
        "nested": 1,
        "also nested": 2
    }
}
@sentient-kshaffer

This comment has been minimized.

Copy link

@sentient-kshaffer sentient-kshaffer commented Jul 28, 2017

Thank you so much for this :).
They really need to implement this directly into postgres...

@tombh

This comment has been minimized.

Copy link

@tombh tombh commented Sep 6, 2021

Seems like we still don't have deep merging in Postgres 12, eg:

select '{"a": {"s1": 1}}'::jsonb || '{"a": {"s2": 2}}'::jsonb
-- {"a": {"s2": 2}}

The above function is still useful. However it does require the plv8 extension which isn't available by default

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment