54

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Jayaram
  • 6,276
  • 12
  • 42
  • 78

2 Answers2

103

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(...)).

IMSoP
  • 89,526
  • 13
  • 117
  • 169
  • 4
    @Kunkka: If `UNION` is *needed* one alternative is to cast to `jsonb` (or cast the array to `jsonb[]` accordingly), for which the equality operator is defined. See: http://stackoverflow.com/a/24296054/939860 or http://stackoverflow.com/a/30520760/939860. (You lose original format, though - which is typically irrelevant.) – Erwin Brandstetter May 01 '17 at 12:50
  • Had same issue and I just cast the value into text using `::text` both in the group by and in the select to facilitate the comparison it complained about. Was building hierarchy of views so at a level further up, I just cast back to json when needed with `::json[]`. Using Pg 9.6. – Thalis K. Sep 03 '17 at 20:36
  • 3
    Had the same issue using `distinct`. Didn't need it, so I just took it out. – Dave Liu Oct 23 '19 at 18:24
  • flawless explanation @IMSoP – Gaurav Mar 25 '22 at 12:04
14

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

Jayaram
  • 6,276
  • 12
  • 42
  • 78
  • This should be a comment to IMSoP's answer. – Erwin Brandstetter May 01 '17 at 12:47
  • 3
    @ErwinBrandstetter i actually figured out the solution before i could get the answer to this post. i just added my answer instead of deleting the post. I accepted his answer anyways though since it was more helpful than mine – Jayaram May 01 '17 at 12:49