postgresql when it's not your job


A simple JSON difference function

21 January 2016

More as an example than anything else, I wanted a function that would take two JSONB objects in PostgreSQL, and return how the left-hand side differs from the right-hand side. This means any key that is in the left but not in the right would be returned, along with any key whose value on the left is different from the right.

Here’s a quick example of how to do this in a single SELECT. In real life, you probably want more error checking, but it shows how nice the built-in primitives are:

    SELECT jsonb_object_agg(a.key, a.value) FROM
        ( SELECT key, value FROM jsonb_each(l) ) a LEFT OUTER JOIN
        ( SELECT key, value FROM jsonb_each(r) ) b ON a.key = b.key
    WHERE a.value != b.value OR b.key IS NULL;
    LANGUAGE sql;

David Fetter at 22:42, 24 January 2016:

Would FULL OUTER JOIN … WHERE a.key || b.key IS NULL work better here? At least to me, that construction gives a better clue as to what’s going on, at least until SQL gets a JOIN type of SYMMETRIC DIFFERENCE, the one primitive set operation missing from the language.

Xof at 10:11, 25 January 2016:

It’s really a matter of taste. I like the formulation given, because a.key can’t be NULL in this particular case (JSON doesn’t allow for null as a key), but either one will work. Unless you are dealing with gigantic JSON objects, I doubt the performance will be significantly different either way.