Deep Merge two jsonb documents in PostgreSQL

jsonb_merge.sql

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;

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

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

@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.

@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.