在Postgres中的GROUP BY操作 - JSON数据类型没有相等性？

11 浏览
0 Comments

在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并按照这个问题的方法，我使用了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;

返回了我想要的结果。有人有更好的解决方案吗？

0

1 答案

0 Comments

GROUP BY在Postgres中的使用存在问题，特别是对于JSON数据类型。JSON数据类型在Postgres中没有等号运算符，但是在Postgres 9.4+版本中有一个针对JSONB数据类型的等号运算符。

解决方法是使用LATERAL连接来实现更简洁、更快速、更优雅的查询。通过使用LATERAL连接和json_array_elements函数，可以实现对JSON数组中的元素进行GROUP BY操作。以下是使用LATERAL连接进行GROUP BY操作的示例代码：

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;

上述代码中，我们通过LATERAL连接将JSON数组中的元素进行展开，并对展开后的元素进行GROUP BY操作。通过DISTINCT ON子句，我们可以获取每个组中的第一个元素。通过ORDER BY子句，我们可以按照指定的字段进行排序，以获取每个组中的最后一个元素。

如果只需要获取不重复的团队信息，可以省略ORDER BY子句。通过使用LATERAL连接和json_array_elements函数，我们可以轻松地对JSON数组中的元素进行GROUP BY操作。

关于JSON和等号运算符的问题，我们可以使用jsonb数据类型来解决。在Postgres 9.4+版本中，jsonb数据类型具有等号运算符。如果需要对json数据类型进行等号比较，可以将其强制转换为jsonb数据类型。以下是将json数据类型强制转换为jsonb数据类型的示例代码：

my_json_column::jsonb

通过将json数据类型转换为jsonb数据类型，我们可以在某些情况下使用等号运算符进行比较操作。

总结起来，GROUP BY在Postgres中对于JSON数据类型存在问题，但是可以通过使用LATERAL连接来解决。此外，json数据类型没有等号运算符，但是可以通过将其转换为jsonb数据类型来实现等号比较操作。通过上述方法，我们可以更方便地对JSON数据类型进行GROUP BY操作和等号比较操作。

0