Postgres中的GROUP BY-JSON数据类型不相等吗?
我在匹配表中有以下数据:
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
并遵循此问题,我DISTINCTON
代替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;
返回上面我想要的。有谁有更好的解决方案?
回答:
通过联接可以更短,更快,更优雅LATERAL
:
SELECT DISTINCT ON (t.team->>'Name') t.teamFROM matches m, json_array_elements(m.match->'Teams') t(team);
ORDER BY t.team->>'Name', m.id DESC; -- to get the "last"
JSON和平等
json
Postgres中没有数据类型的相等运算符,但是jsonb
(Postgres 9.4+)有一个运算符
以上是 Postgres中的GROUP BY-JSON数据类型不相等吗? 的全部内容, 来源链接: utcz.com/qa/407388.html