14.postgresql- 比较两个jsonb对象

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

 
 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;
 
 
 SELECT jsonb_diff_val(
    '{"a":"aaa","b":"bbb","c":"ccc"}'::jsonb,
    '{"a":"aaa","b":"jjj","d":"ddd"}'::jsonb
);