Postgres中的GROUP BY-JSON数据类型不相等吗? [英] GROUP BY in Postgres - no equality for JSON data type?
问题描述
我在匹配表中有以下数据:
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';
但这返回:
ERROR: could not identify an equality operator for type json
SQL state: 42883
Character: 1680
我了解到Postgres 对于JSON没有相等性.我只需要团队名称(字符串)相等即可,无需比较该团队中的球员.
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
返回:
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
.这是我的完整查询:
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?
推荐答案
使用LATERAL
联接,更快,更优雅.
Shorter, faster and more elegant with a LATERAL
join:
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"
如果您只想与众不同的团队,则可以使用ORDER BY
.相关:
If you just want distinct teams, the ORDER BY
can go. Related:
- Query for element of array in JSON column
- Query for array elements inside JSON type
在Postgres中没有json
数据类型的相等运算符,但是对于jsonb
(Postgres 9.4+)只有一个运算符:
There is no equality operator for the json
data type in Postgres, but there is one for jsonb
(Postgres 9.4+):
这篇关于Postgres中的GROUP BY-JSON数据类型不相等吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!