有了PostgreSQL(v9.5)种,JSONB种格式提供了绝佳的机会.但现在我只能做一个相对简单的手术;
比较两个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
应返回第1行与第2行的差值:
'{"b":"bbb", "c":"ccc", "d":null}'
相反,它也会返回重复项({"a": "aaa"}
).而且总的来说,可能有更优雅的方法!