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

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

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