Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL compare two jsonb objects

Tags:

With PostgreSQL(v9.5), the JSONB formats give awesome opportunities. But now I'm stuck with what seems like a relatively simple operation;

compare two jsonb objects; see what is different or missing in one document compared to the other.

What I have so far

WITH reports(id,DATA) AS (
          VALUES (1,'{"a":"aaa", "b":"bbb", "c":"ccc"}'::jsonb),
                 (2,'{"a":"aaa", "b":"jjj", "d":"ddd"}'::jsonb) )
SELECT jsonb_object_agg(anon_1.key, anon_1.value)
FROM
  (SELECT anon_2.key AS KEY,
      reports.data -> anon_2.KEY AS value
   FROM reports,
     (SELECT DISTINCT jsonb_object_keys(reports.data) AS KEY
      FROM reports) AS anon_2
   ORDER BY reports.id DESC) AS anon_1

Should return the difference of row 1 compared to row 2:

'{"b":"bbb", "c":"ccc", "d":null}'

Instead it returns also duplicates ({"a": "aaa"}). Also; there might be a more elegant approach in general!

like image 898
Joost Döbken Avatar asked Mar 16 '16 16:03

Joost Döbken


1 Answers

UPDATED

CREATE OR REPLACE FUNCTION jsonb_diff_val(val1 JSONB,val2 JSONB)
RETURNS JSONB AS $$
DECLARE
  result JSONB;
  v RECORD;
BEGIN
   result = val1;
   FOR v IN SELECT * FROM jsonb_each(val2) LOOP
     IF result @> jsonb_build_object(v.key,v.value)
        THEN result = result - v.key;
     ELSIF result ? v.key THEN CONTINUE;
     ELSE
        result = result || jsonb_build_object(v.key,'null');
     END IF;
   END LOOP;
   RETURN result;
END;
$$ LANGUAGE plpgsql;

Query:

SELECT jsonb_diff_val(
    '{"a":"aaa", "b":"bbb", "c":"ccc"}'::jsonb,
    '{"a":"aaa", "b":"jjj", "d":"ddd"}'::jsonb
);
            jsonb_diff_val             
---------------------------------------
 {"b": "bbb", "c": "ccc", "d": "null"}
(1 row)
like image 166
Dmitry S Avatar answered Oct 25 '22 03:10

Dmitry S