Group by in Postgres - No Equality for JSON Data Type

GROUP BY in Postgres - no equality for JSON data type?

Shorter, faster and more elegant with a LATERAL join:

SELECT DISTINCT ON (t.team->>'Name') t.team
FROM   matches m, json_array_elements(m.match->'Teams') t(team);
ORDER  BY t.team->>'Name', m.id DESC;  -- to get the "last"

If you just want distinct teams, the ORDER BY can go. Related:

• Query for element of array in JSON column
• Query for array elements inside JSON type

JSON and equality

There is no equality operator for the json data type in Postgres, but there is one for jsonb (Postgres 9.4+):

• How to query a json column for empty objects?

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

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

could not identify an equality operator for type json when using distinct

Use jsonb_build_object. Notice the b for binary after json.

How to deal with JSON column while using GROUP BY

I'd go with DISTINCT ON() instead:

SELECT DISTINCT ON (age) id, name, salary, age, adress
FROM test
ORDER BY age, salary desc

The DISTINCT ON (age) will give you one row for each age. The one with the highest salary, as the ORDER BY decides.

Postgres return json with group

Don't aggregate the complete row, only the headline:

SELECT json_build_object(news_source, json_agg(headline))
FROM stories 
GROUP BY news_source
ORDER BY news_source;

Online example: http://rextester.com/LUOUR61576

Doctrine orderBy with PostgreSQL json column (could not identify an equality operator for type json)

I found a solution. Just need to add

->groupBy('o.id');

in the query

How to query a json column for empty objects?

There is no equality (or inequality) operator for the data type json as a whole, because equality is hard to establish. Consider jsonb in Postgres 9.4 or later, where this is possible. More details in this related answer on dba.SE (last chapter):

• How to remove known elements from a JSON[] array in PostgreSQL?

SELECT DISTINCT json_column ... or ... GROUP BY json_column fail for the same reason (no equality operator).

Casting both sides of the expression to text allows = or <> operators, but that's not normally reliable as there are many possible text representations for the same JSON value. In Postgres 9.4 or later, cast to jsonb instead. (Or use jsonb to begin with.)

However, for this particular case (empty object) it works just fine:

select * from test where foo::text <> '{}'::text;

---

Related Topics