Skip to content

Instantly share code, notes, and snippets.

@phillip-haydon
Created December 6, 2016 10:17
Show Gist options
  • Save phillip-haydon/54871b746201793990a18717af8d70dc to your computer and use it in GitHub Desktop.
Save phillip-haydon/54871b746201793990a18717af8d70dc to your computer and use it in GitHub Desktop.
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
Copy link
Author

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
Copy link

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

@tombh
Copy link

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

@pvorb
Copy link

pvorb commented May 31, 2022

@tombh This answer on Stack Overflow has a solution that doesn't need extensions.

@phillip-haydon
Copy link
Author

@pvorb how does it handles arrays tho? That's the tricky bit. Unsure if the version I posted handles arrays but the version I have on my site does. Its append / update arrays, with an extra argument to replace the array if wanting to remove ones not listed.

@pvorb
Copy link

pvorb commented May 31, 2022

@phillip-haydon I didn't come here for merging arrays. That's not what the function in the gist does.

I'm not even sure how you would want to merge arrays other than a simple concatenation.

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