返回整数和布尔值的二维数组

Question

我有一个表，用于存储团队的team_id(INT(player_id(INT和active(BOOLEAN(。

目前，有一种数据结构返回带有团队详细信息的JSON，其中一个键是"；玩家"；它是一个整数数组，看起来像这样：

[
{
"team_id": 1,
"team_name": "Team Name",
"players": [
2,
23,
44,
45
],
}
]

对于每个玩家，我也想返回active列的BOOLEAN，所以结构看起来像这样：

[
{
"team_id": 1,
"team_name": "Team Name",
"players": [
[2, TRUE]
[23, TRUE]
[44, FALSE]
[45, TRUE]
],
}
]

为了生成"；玩家"；关键，sql如下：

SELECT team_vs_players.team_id,
ARRAY_AGG(team_vs_players.player_id) AS players
FROM team.team_vs_players
GROUP BY team_vs_players.team_id

Answer 1

不能在原生PostgreSQL数组中混合使用int和boolean。

要组合这些值，您需要创建一个jsonb数组。

如果由于隐式强制转换而无法直接实现，那么我们可能需要查看生成输出json的最后一步。

SELECT team_vs_players.team_id,
ARRAY_AGG(jsonb_build_array(team_vs_players.player_id, team_vs_players.active)) AS players
FROM team.team_vs_players
GROUP BY team_vs_players.team_id