在Postgres中使用GROUP BY - JSON数据类型没有相等性？

Question

在Postgres中使用GROUP BY - JSON数据类型没有相等性？

sqljsonpostgresqlgreatest-n-per-grouplateral

27

我有一个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;

返回我之前想要的结果。有没有更好的解决方案？

- janderson

1

尝试将team->'Name'转换为text。 - Nick Barnes

提供您所使用的Postgres版本是很明显的，这一点对于得到最佳答案非常重要。表定义也会非常有用。 - Erwin Brandstetter

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

使用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"

如果你只需要不同的团队，ORDER BY 可以省略。相关链接：

JSON和相等性

在Postgres中，json 数据类型没有等于运算符，但是 jsonb 有一个（适用于 Postgres 9.4及以上版本）：

如何查询空对象的 JSON 列？