使用 UNION 时无法识别 json[] 类型的相等运算符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43718733/
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
Couldnt identify equality operator of type json[] when using UNION
提问by Kannaj
I'm trying to perform multiple queries on a single table using a UNIONrule
我正在尝试使用UNION规则对单个表执行多个查询
I have two tables:
我有两个表：
- project (id, name, pinned BOOLEAN)
- skills (m2m to projects)
- 项目（ID、名称、固定布尔值）
- 技能（m2m 到项目）
I'm looking to first get an array of rows which have pinnedset to trueand fill up the remaining with the latest entries (pinnedset to false)
我希望首先获得一组已pinned设置为的行，true并用最新的条目（pinned设置为false）填充剩余的行
SELECT
project.id AS project_id,
project.name AS project_name,
array_agg(json_build_object('skill_id', project_skills.id,'name', project_skills.skill)) AS skills
from project
LEFT OUTER JOIN project_skills on project.name = project_skills.project
WHERE project.pinned = true
GROUP BY project_id,project_name
UNION
SELECT
project.id AS project_id,
project.name AS project_name,
array_agg(json_build_object('skill_id', project_skills.id,'name', project_skills.skill)) AS skills
from project
LEFT OUTER JOIN project_skills on project.name = project_skills.project
WHERE project.id != 1 AND project.pinned = false
GROUP BY project_id,project_name
ORDER BY project.create_date DESC LIMIT 5
When performing this query , i get the below error
执行此查询时，我收到以下错误
ERROR: could not identify an equality operator for type json[] LINE 7: array_agg(json_build_object('skill_id', project_skills.id,...
ERROR: could not identify an equality operator for type json[] LINE 7: array_agg(json_build_object('skill_id', project_skills.id,...
I don't understand this error. Is it failing because its trying to compare the json columns from both results?
我不明白这个错误。是否因为尝试比较两个结果中的 json 列而失败？
I'm using Postgres 9.4.
我正在使用 Postgres 9.4。
回答by IMSoP
When you use a UNION, the DBMS removes any duplicate rows, and in order to do so it needs to identify whether two rows are equal / identical. This in turn means looking at each column of the two rows it's comparing, and deciding if they're equal.
当您使用 a 时UNION，DBMS 会删除任何重复的行，为此它需要确定两行是否相等/相同。这反过来意味着查看它比较的两行的每一列，并确定它们是否相等。
The error message you're seeing is where one of your columns is built using array_agg(json_build_object(...))which produces a value of type json[], which means "array of json values". Because Postgres doesn't know how to compare two arrays of JSON values, it can't decide if your UNIONproduced duplicates.
您看到的错误消息是您的一列是使用array_agg(json_build_object(...))它生成的值构建的地方 type json[]，这意味着“json 值数组”。因为 Postgres 不知道如何比较两个 JSON 值数组，所以它无法确定您UNION生成的是否重复。
If you don't actually care about removing duplicates, the simplest solution is to use UNION ALLwhich skips this step.
如果您实际上并不关心删除重复项，最简单的解决方案是使用UNION ALLwhich 跳过此步骤。
As pointed out in comments, if you dowant to remove duplicates, you can cast the values to something which has a comparison operator defined. The most general solution is to cast to text (e.g. some_value::textor CAST(some_value as text)) but for JSON specifically you probably want the jsonbtype, which will ignore formatting when comparing.
正如评论中指出的那样，如果您确实想删除重复项，您可以将值转换为定义了比较运算符的内容。最普遍的解决方案是强制转换为文本（例如some_value::text或CAST(some_value as text)），但JSON明确你可能想的jsonb类型，这将忽略比较时格式化。
You could cast jsonto jsonb, or json[]to jsonb[], or in this example you could build jsonbdirectly with array_agg(jsonb_build_object(...))rather than array_agg(json_build_object(...)).
你可以投json给jsonb，或者json[]给jsonb[]，或在这个例子中，你可以建立jsonb直接array_agg(jsonb_build_object(...))而不是array_agg(json_build_object(...))。
回答by Kannaj
Turns out all i had to do was use UNION ALL- i guess this ignores trying to compare jsontypes across queries.
原来我所要做的就是使用UNION ALL- 我想这忽略了尝试比较json跨查询的类型。
