PostgreSQL 比较两个 jsonb 对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36041784/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL compare two jsonb objects
提问by Joost D?bken
With PostgreSQL(v9.5), the JSONBformats give awesome opportunities. But now I'm stuck with what seems like a relatively simple operation;
使用PostgreSQL(v9.5)，JSONB格式提供了极好的机会。但现在我被困在一个看似相对简单的操作上；
compare two jsonb objects; see what is different or missing in one document compared to the other.
比较两个 jsonb 对象；查看一份文件与另一份文件相比有何不同或缺失。
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:
应该返回第 1 行与第 2 行的差异：
'{"b":"bbb", "c":"ccc", "d":null}'
Instead it returns also duplicates ({"a": "aaa"}). Also; there might be a more elegant approach in general!
相反，它也返回重复项 ( {"a": "aaa"})。还; 一般可能有更优雅的方法！
回答by Dmitry Savinkov
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)
回答by J. Raczkiewicz
I have created similar function that would scan the object recursively and will return the difference between new object and old object. I was not able to find a 'nicer' way to determine if jsonb object 'is empty' - so would be grateful for any suggestion how to simplify that. I plan to use it to keep track of updates made to the jsonb objects, so I store only what have changed.
我创建了类似的函数，它将递归扫描对象并返回新对象和旧对象之间的差异。我无法找到一种“更好”的方法来确定 jsonb 对象是否“为空”——所以如果你有任何关于如何简化它的建议，我将不胜感激。我打算用它来跟踪对 jsonb 对象所做的更新，所以我只存储发生了变化的内容。
Here is the function:
这是函数：
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;
Then simple query looks like this:
然后简单的查询如下所示：
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)
回答by Sahap Asci
Here is a solution without creating a new function;
这是一个无需创建新函数的解决方案；
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
The result is;
结果是；
{"b" : "bbb", "c" : "ccc", "d" : null}
This method only compares first level of json. It does NOT traverse the whole object tree.
此方法仅比较第一级 json。它不会遍历整个对象树。
回答by langpavel
My solution is not recursive but you can use it for detecting common key/values:
我的解决方案不是递归的，但您可以使用它来检测常见的键/值：
-- Diff two jsonb objects
CREATE TYPE jsonb_object_diff_result AS (
old jsonb,
new jsonb,
same jsonb
);
CREATE OR REPLACE FUNCTION jsonb_object_diff(in_old jsonb, in_new jsonb)
RETURNS jsonb_object_diff_result AS
$jsonb_object_diff$
DECLARE
_key text;
_value jsonb;
_old jsonb;
_new jsonb;
_same jsonb;
BEGIN
_old := in_old;
_new := in_new;
FOR _key, _value IN SELECT * FROM jsonb_each(_old) LOOP
IF (_new -> _key) = _value THEN
_old := _old - _key;
_new := _new - _key;
IF _same IS NULL THEN
_same := jsonb_build_object(_key, _value);
ELSE
_same := _same || jsonb_build_object(_key, _value);
END IF;
END IF;
END LOOP;
RETURN (_old, _new, _same);
END;
$jsonb_object_diff$
LANGUAGE plpgsql;
Result can look like this:
结果可能如下所示：
SELECT * FROM jsonb_object_diff(
'{"a": 1, "b": 5, "extra1": "woo", "old_null": null, "just_null": null}'::jsonb,
'{"a": 1, "b": 4, "extra2": "ahoj", "new_null": null, "just_null": null}'::jsonb);
-[ RECORD 1 ]--------------------------------------
old | {"b": 5, "extra1": "woo", "old_null": null}
new | {"b": 4, "extra2": "ahoj", "new_null": null}
same | {"a": 1, "just_null": null}
回答by ThePianoDentist
(not enough points to comment)
（点数不够评论）
for https://stackoverflow.com/a/37278190/3920439, it worked great,
对于https://stackoverflow.com/a/37278190/3920439，效果很好，
however the jsonb_typeof(val1) = 'null' check just works for 'null' strings/jsonb values.
但是 jsonb_typeof(val1) = 'null' 检查仅适用于 'null' 字符串/jsonb 值。
if you pass an actual null into val1, it will return null.
如果您将实际空值传递给 val1，它将返回空值。
changing IF val1 IS NULL OR jsonb_typeof(val1) = 'null'let me return whole of val2, for case where val1 was null
(this scenario comes up when doing lag functions, for the first row)
更改IF val1 IS NULL OR jsonb_typeof(val1) = 'null'让我返回整个 val2，对于 val1 为空的情况（在执行滞后函数时出现这种情况，对于第一行）
