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?

It should be obvious to provide your version of Postgres. Important for the best answer. Table definition would also be useful. — Erwin Brandstetter
– Erwin Brandstetter, Commented May 29, 2015 at 3:41

Community · Accepted Answer · 2020-06-20 09:12:55Z

18

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:

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?

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 29, 2015 at 3:45

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

janderson Over a year ago

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

Erwin Brandstetter Over a year ago

@Watson: Right, fixed.

nbrustein Over a year ago

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

Collectives™ on Stack Overflow

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

1 Answer 1

JSON and equality

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

JSON and equality

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related