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

Question

I have the following data in a matches table:

5;{"Id":1,"Teams":[{"Name":"TeamA","Players":[{"Name":"AAA"},{"Name":"BBB"}]},{"Name":"TeamB","Players":[{"Name":"CCC"},{"Name":"DDD"}]}],"TeamRank":[1,2]}
6;{"Id":2,"Teams":[{"Name":"TeamA","Players":[{"Name":"CCC"},{"Name":"BBB"}]},{"Name":"TeamB","Players":[{"Name":"AAA"},{"Name":"DDD"}]}],"TeamRank":[1,2]}

I want to select each last distinct Team in the table by their name. i.e. I want a query that will return:

6;{"Name":"TeamA","Players":[{"Name":"CCC"},{"Name":"BBB"}
6;{"Name":"TeamB","Players":[{"Name":"AAA"},{"Name":"DDD"}

So each team from last time that team appears in the table.
I have been using the following (from here):

WITH t AS (SELECT id, json_array_elements(match->'Teams') AS team FROM matches)
SELECT MAX(id) AS max_id, team FROM t GROUP BY team->'Name';

But this returns:

ERROR: could not identify an equality operator for type json
SQL state: 42883
Character: 1680

I understand that Postgres doesn't have equality for JSON. I only need equality for the team's name (a string), the players on that team don't need to be compared.

Can anyone suggest an alternative way to do this?
For reference:

SELECT id, json_array_elements(match->'Teams') AS team FROM matches

returns:

5;"{"Name":"TeamA","Players":[{"Name":"AAA"},{"Name":"BBB"}]}"
5;"{"Name":"TeamB","Players":[{"Name":"CCC"},{"Name":"DDD"}]}"
6;"{"Name":"TeamA","Players":[{"Name":"CCC"},{"Name":"BBB"}]}"
6;"{"Name":"TeamB","Players":[{"Name":"AAA"},{"Name":"DDD"}]}"

EDIT: I cast to text and following this question, I used DISTINCT ON instead of GROUP BY. Here's my full query:

WITH t AS (SELECT id, json_array_elements(match->'Teams') AS team
           FROM matches ORDER BY id DESC)
SELECT DISTINCT ON (team->>'Name') id, team FROM t;

Returns what I wanted above. Does anyone have a better solution?

Teacup

Try casting team->'Name' to text – Froggy

It should be obvious to provide your version of Postgres. Important for the best answer. Table definition would also be useful. – Arsis

Answer 1

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?

Arsis

Just need to change (t.team->'Name') to (t.team->>'Name'), json has no equality. – Teacup

@Watson: Right, fixed. – Arsis

Since jsonb has an equality operator in 9.4+, you can just cast the json column to jsonb in some cases, using my_json_column::jsonb – Lucero