Set difference for jsonb

Question

The hstore type in Postgres provides an operation to calculate the set difference (EXCEPT) of two records. Is there something similar for jsonb? The best I've got is SELECT * FROM jsonb_each('{"a":1, "b":2}'::jsonb) EXCEPT SELECT * FROM jsonb_each('{"a":1}'::jsonb), but I don't know how to convert it back to jsonb.

I want to use this to calculate the difference (the changed values) between OLD and NEW in a trigger.

Answer 1

As a_horse_with_no_name pointed in the comments to a blog post describing the problem that there is no operation for jsonb - jsonb, I found this solution:

DO $$
DECLARE
  OLD CONSTANT RECORD := row(12, 'Test', false, 4);
  NEW CONSTANT RECORD := row(12, 'Test', true, NULL);
  data_diff jsonb;
BEGIN
    SELECT jsonb_object_agg(key, value) INTO STRICT data_diff
    FROM (
        SELECT * FROM jsonb_each(to_jsonb(NEW))
        EXCEPT SELECT * FROM jsonb_each(to_jsonb(OLD))
    ) as t;

    raise notice '%', data_diff;
END
$$;