Could not determine equality operator of type json [] when using UNION

Question

I am trying to execute multiple queries in one table using the UNION rule

I have two tables:

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

I want to first get an array of strings that have pinned set to true and populate the remaining last 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 I run this request, I get the following error

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

I do not understand this error. Is this unsuccessful due to an attempt to compare json columns from both results?

I am using Postgres 9.4.

Answer 1

When you use UNION , the DBMS deletes any duplicate rows, and for this you need to determine whether the two rows are equal / identical. This, in turn, means looking at each column of two rows that it compares, and deciding if they are equal.

The error message you see is where one of your columns is built using array_agg(json_build_object(...)) , which creates a value of type json[] , which means "an array of json values". Since Postgres does not know how to compare two arrays of JSON values, it cannot decide if your duplicates were UNION .

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

Answer 2

It turns out that all I had to do was use UNION ALL - I think this ignores the attempt to compare json types between requests.