Merging JSON in PostgreSQL

At my $day-job we have a lot of jsonb in our database. From time to time, I have to manually run a query to fix something in there. This week was one of those times.

While you can pretty much do everything you need with regards to JSON editing with jsonb_set, I thought it might be nice if I were able to merge a given JSON object into an existing object. This might be cleaner in some situations, but mostly it is fun to figure it out. And who doesn’t like spending time with plpgsql?

The way I wanted to have it work is like this:

UPDATE user SET properties = jsonb_merge(properties, '{"notifications": {"new_case": false, "new_document": true}}');

And this is the eventual function I produced to do it:

CREATE OR REPLACE FUNCTION jsonb_merge(original jsonb, delta jsonb) RETURNS jsonb AS $$
    DECLARE result jsonb;
    BEGIN
    SELECT
        json_object_agg(
            COALESCE(original_key, delta_key),
            CASE
                WHEN original_value IS NULL THEN delta_value
                WHEN delta_value IS NULL THEN original_value
                WHEN (jsonb_typeof(original_value) <> 'object' OR jsonb_typeof(delta_value) <> 'object') THEN delta_value
                ELSE jsonb_merge(original_value, delta_value)
            END
        )
        INTO result
        FROM jsonb_each(original) e1(original_key, original_value)
        FULL JOIN jsonb_each(delta) e2(delta_key, delta_value) ON original_key = delta_key;
    RETURN result;
END
$$ LANGUAGE plpgsql;