json - 使用 UNION 时无法识别 json[] 类型的相等运算符

Question

我正在尝试使用 UNION 规则对单个表执行多个查询

我有两张 table :

• 项目(id、名称、固定 bool 值)
• 技能(m2m 到项目)

我希望首先获得一个 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

执行此查询时，我收到以下错误

ERROR:  could not identify an equality operator for type json[]
LINE 7:   array_agg(json_build_object('skill_id', project_skills.id,...

我不明白这个错误。它是否因为试图比较两个结果中的 json 列而失败？

我使用的是 Postgres 9.4。

Answer 1

当您使用 UNION 时，DBMS 会删除所有重复的行，为此它需要识别两行是否相等/相同。这反过来意味着查看它正在比较的两行的每一列，并确定它们是否相等。

您看到的错误消息是您的列之一是使用 array_agg(json_build_object(...)) 构建的，它产生 json[] 类型的值>，意思是“json 值的数组”。因为 Postgres 不知道如何比较两个 JSON 值数组，所以它无法确定您的 UNION 是否产生重复。

如果您实际上并不关心删除重复项，最简单的解决方案是使用跳过此步骤的 UNION ALL。

正如评论中所指出的，如果您确实想要删除重复项，您可以将值转换为定义了比较运算符的值。最通用的解决方案是转换为文本(例如 some_value::text 或 CAST(some_value as text))，但对于 JSON，您可能特别需要 the jsonb type ，比较时会忽略格式。

您可以将 json 转换为 jsonb，或将 json[] 转换为 jsonb[]，或者在此例如，您可以直接使用 array_agg(jsonb_build_object(...)) 而不是 array_agg(json_build_object(...)) 构建 jsonb。