Couldnt identify equality operator of type json[] when using UNION

Question

I'm trying to perform multiple queries on a single table using a UNION rule

I have two tables:

• project (id, name, pinned BOOLEAN)
• skills (m2m to projects)

I'm looking to first get an array of rows which have pinned set to true and fill up the remaining with the latest entries (pinned set to 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,...

I don't understand this error. Is it failing because its trying to compare the json columns from both results?

I'm using Postgres 9.4.

Answer 1

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.

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 UNION produced duplicates.

If you don't actually care about removing duplicates, the simplest solution is to use UNION ALL which skips this step.

As pointed out in comments, if you do want 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::text or CAST(some_value as text)) but for JSON specifically you probably want the jsonb type, which will ignore formatting when comparing.

You could cast json to jsonb, or json[] to jsonb[], or in this example you could build jsonb directly with array_agg(jsonb_build_object(...)) rather than array_agg(json_build_object(...)).

Answer 2

Turns out all i had to do was use UNION ALL - i guess this ignores trying to compare json types across queries.