postgresql Postgres 中的 GROUP BY - JSON 数据类型不相等？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30519458/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
GROUP BY in Postgres - no equality for JSON data type?
提问by janderson
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
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.
我知道 Postgres与 JSON 不相等。我只需要团队名称（一个字符串）的相等性，不需要比较该团队中的球员。
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 textand following this question, I used DISTINCT ONinstead of GROUP BY. Here's my full query:
编辑：我投到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;
Returns what I wanted above. Does anyone have a better solution?
返回我上面想要的。有没有人有更好的解决方案？
采纳答案by Erwin Brandstetter
Shorter, faster and more elegant with a LATERALjoin:
使用连接更短、更快、更优雅LATERAL：
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 BYcan go. Related:
如果你只想要不同的团队，ORDER BY可以去。有关的：
JSON and equality
JSON 和等式
There is no equality operator for the jsondata type in Postgres, but there is one for jsonb(Postgres 9.4+):
jsonPostgres 中的数据类型没有相等运算符，但有一个 for jsonb(Postgres 9.4+)：
