PostgreSQL比较两个jsonb对象

Question

对于PostgreSQL（v9.5），JSONB格式提供了极好的机会。但现在我只能做一个相对简单的手术；

比较两个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

应该返回第1行与第2行的差异：

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

相反，它还返回重复项（{“a”：“aaa”}）。而且总的来说，可能有一种更优雅的方法！




Answer 1

这是一个不创建新函数的解决方案；

SELECT
    json_object_agg(COALESCE(old.key, new.key), old.value)
  FROM json_each_text('{"a":"aaa", "b":"bbb", "c":"ccc"}') old
  FULL OUTER JOIN json_each_text('{"a":"aaa", "b":"jjj", "d":"ddd"}') new ON new.key = old.key 
WHERE 
  new.value IS DISTINCT FROM old.value

结果是；

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

该方法仅比较json的第一级。它不会遍历整个对象树。

Answer 2

我已经创建了类似的函数，可以递归扫描对象并返回新对象和旧对象之间的差异。我无法找到一种“更好”的方法来确定jsonb对象“是否为空”——所以如果有任何建议可以简化它，我将不胜感激。我计划使用它来跟踪对jsonb对象所做的更新，因此我只存储已更改的内容。

以下是函数：

CREATE OR REPLACE FUNCTION jsonb_diff_val(val1 JSONB,val2 JSONB)
RETURNS JSONB AS $$
DECLARE
    result JSONB;
    object_result JSONB;
    i int;
    v RECORD;
BEGIN
    IF jsonb_typeof(val1) = 'null'
    THEN 
        RETURN val2;
    END IF;

    result = val1;
    FOR v IN SELECT * FROM jsonb_each(val1) LOOP
        result = result || jsonb_build_object(v.key, null);
    END LOOP;

    FOR v IN SELECT * FROM jsonb_each(val2) LOOP
        IF jsonb_typeof(val1->v.key) = 'object' AND jsonb_typeof(val2->v.key) = 'object'
        THEN
            object_result = jsonb_diff_val(val1->v.key, val2->v.key);
            -- check if result is not empty 
            i := (SELECT count(*) FROM jsonb_each(object_result));
            IF i = 0
            THEN 
                result = result - v.key; --if empty remove
            ELSE 
                result = result || jsonb_build_object(v.key,object_result);
            END IF;
        ELSIF val1->v.key = val2->v.key THEN 
            result = result - v.key;
        ELSE
            result = result || jsonb_build_object(v.key,v.value);
        END IF;
    END LOOP;

    RETURN result;

END;
$$ LANGUAGE plpgsql;

然后简单查询如下所示：

SELECT jsonb_diff_val(                                                                                                                                                                                                                                                           
    '{"a":"aaa", "b":{"b1":"b","b2":"bb","b3":{"b3a":"aaa","b3c":"ccc"}}, "c":"ccc"}'::jsonb,
    '{"a":"aaa", "b":{"b1":"b1","b3":{"b3a":"aaa","b3c":"cccc"}}, "d":"ddd"}'::jsonb
);
                                jsonb_diff_val                                 
-------------------------------------------------------------------------------
 {"b": {"b1": "b1", "b2": null, "b3": {"b3c": "cccc"}}, "c": null, "d": "ddd"}
(1 row)

Answer 3

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
);
            jsonb_diff_val             
---------------------------------------
 {"b": "bbb", "c": "ccc", "d": "null"}
(1 row)