Warm tip: This article is reproduced from stackoverflow.com, please click
postgresql jsonb postgresql-9.5

PostgreSQL compare two jsonb objects

发布于 2020-03-27 10:29:38

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!

Questioner
Joost Döbken
Viewed
98
分享
Dmitry Savinkov 2016-03-18 05:23

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)
Joost Döbken 2016-03-17 15:47:42

Thanks for your answer: creating a function seems like a nice thing. However; '{"b":"bbb", "c":"ccc", "d":null}' is not a typo since "d" is not in row 1 and therefore the function should return "d":null

Joost Döbken 2016-03-17 15:52:01

I changed "json array" into "json objects" in my question; I was not aware of these definitions

Ajmal sha 2018-07-19 17:19:21

How can we use this with postgres 9.4

freb 2019-01-08 07:14:19

Wish I could give you more up-votes.

Related issues

1
Query is possibly slow?
2
Getting empty rows from "SELECT ALL" query in PostgreSQL
3
Implement smart search by text field in database
4
Is it posible to use queried column_name in INSERT INTO statement?
5
What is the different between replica and db instance in Aurora Postgresql?
6
Entity Framework Core Many to Many change navigation property names
7
Django
8
How can i use longitude and latitude with typeorm and postgres
9
Keeping annotated rank when filtering Django
10
Liquibase validation fails when UNIQUE constraint