我有一个matches表格,其中包含以下数据:
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]}
我希望能够通过团队名称选择表中每个最后不同的团队。也就是说,我希望查询将返回以下结果:
6;{"Name":"TeamA","Players":[{"Name":"CCC"},{"Name":"BBB"}
6;{"Name":"TeamB","Players":[{"Name":"AAA"},{"Name":"DDD"}
因此,上次出现在表格中的每个团队都会出现在表格中。
我一直在使用以下内容(来自这里):
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';
但是这会返回:
ERROR: could not identify an equality operator for type json
SQL state: 42883
Character: 1680
我知道Postgres 没有针对JSON的相等性。我只需要比较团队名称(一个字符串)的相等性,不需要比较该团队的球员。
有人可以建议另一种方法吗?
供参考:
SELECT id, json_array_elements(match->'Teams') AS team FROM matches
返回:
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"}]}"
编辑:我将其转换为text
,并参考这个问题,使用了DISTINCT ON
而不是GROUP BY
。以下是完整的查询语句:
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;
返回我之前想要的结果。有没有更好的解决方案?
team->'Name'
转换为text
。 - Nick Barnes