PostgreSQL：比较JSON

问题描述

众所周知，目前PostgreSQL无法比较两个json值。 json = json 之类的比较无效。但是之前将 json 强制转换为 text 怎么样？

As known, at the moment PostgreSQL has no method to compare two json values. The comparison like json = json doesn't work. But what about casting json to text before?

然后

select ('{"x":"a", "y":"b"}')::json::text =
('{"x":"a", "y":"b"}')::json::text

返回 true

同时

select ('{"x":"a", "y":"b"}')::json::text =
('{"x":"a", "y":"d"}')::json::text

返回 false

我尝试了一些具有更复杂对象的变体，它的工作原理是

I tried several variants with more complex objects and it works as expected.

此解决方案中是否有陷阱？

Are there any gotchas in this solution?

更新：

需要与v9.3兼容

推荐答案

是的您的方法存在多个问题（即转换为文本）。请考虑以下示例

Yes there are multiple problem with your approach (i.e. converting to text). Consider the following example

select ('{"x":"a", "y":"b"}')::json::text = ('{"y":"b", "x":"a"}')::json::text;

这类似于您的第一个示例，除了我翻转了 x 和 y 键用于第二个对象，现在即使对象相等也返回false。

This is like your first example example, except that I flipped the order of the x and y keys for the second object, and now it returns false, even thought the objects are equal.

另一个问题是 json 保留空白，所以

Another issue is that json preserves white space, so

select ('{"x":"a", "y":"b"}')::json::text = ('{ "x":"a", "y":"b"}')::json::text;

返回false只是因为我在 x 在第二个对象中。

returns false just because I added a space before the x in the second object.

与v9.3配合使用的解决方案是使用 json_each_text 函数将两个JSON对象扩展到表中，然后比较两个表，例如像这样：

A solution that works with v9.3 is to use the json_each_text function to expand the two JSON objects into tables, and then compare the two tables, e.g. like so:

SELECT NOT exists(
    SELECT
    FROM json_each_text(('{"x":"a", "y":"b"}')::json) t1
         FULL OUTER JOIN json_each_text(('{"y":"b", "x":"a"}')::json) t2 USING (key)
    WHERE t1.value<>t2.value OR t1.key IS NULL OR t2.key IS NULL
)

请注意，仅当两个JSON值是对象（每个键的值都是字符串）的对象时，此方法才有效。

Note that this only works if the two JSON values are objects where for each key, the values are strings.

键位于 exists 内部的查询中：在该查询中，我们将第一个JSON对象中的所有键与第二个JSON对象中的对应键进行匹配。然后，仅保留与以下两种情况之一相对应的行：

The key is in the query inside the exists: In that query we match all keys from the first JSON objects with the corresponding keys in the second JSON object. Then we keep only the rows that correspond to one of the following two cases:

• 两个JSON对象中都存在一个键，但是对应的值是


• 一个键仅存在于两个JSON对象之一中，而另一个不存在

这是唯一见证两个对象不平等的情况，因此我们用 NOT不存在（...）包裹所有内容，即如果我们

These are the only cases that "witness" the inequality of the two objects, hence we wrap everything with a NOT exists(...), i.e. the objects are equal if we didn't find any witnesses of inequality.

如果需要支持其他类型的JSON值（例如数组，嵌套对象等），则可以编写 plpgsql 函数基于上述想法。

If you need to support other types of JSON values (e.g. arrays, nested objects, etc), you can write a plpgsql function based on the above idea.

这篇关于PostgreSQL：比较JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！