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?