我在匹配表中有以下数据:
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]}
我想按名称 Select 表中最后一个不同的团队.i、 e.我想要一个将返回的查询:
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
我知道博士后doesn't have equality for 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"}]}"
EDIT:我投到text，this question之后，我用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;
返回我在上面想要的.有谁有更好的解决方案吗？