如何在PostgreSQL中生成含嵌套球队对象的赛事JSON输出

我有一张名为games的表，存储关于‘主队（home team）’和‘客队（away team）’的信息。该表通过home_team_id和away_team_id与teams表建立外键关联。

期望的JSON输出 #

{
   "id":18203,
   "date":"2022-10-22T18:00:00",
   "away_team" :  {
       "team_id":24,
       "abbr":"PHI"
   }, 
   "home_team" :  {
       "team_id":22,
       "abbr":"NYK"
   }, 
   "home_team_id":22,
   "away_team_id":24
}

当前使用的SQL查询 #

select row_to_json(t) from (
    select * from games g
    inner join teams home_team on home_team.id = g.home_team_id
    inner join teams away_team on away_team.id = g.away_team_id
    where g.day = '2022-10-22T00:00:00'
)t;

得到的错误输出（扁平且键重复） #

{
   "id":18203,
   "date":"2022-10-22T18:00:00",
   "team_id":24,
   "abbr":"PHI",
   "team_id":22,
   "abbr":"NYK",
   "home_team_id":22,
   "away_team_id":24
}

解决方案 #

需要手动指定字段，并使用row_to_json分别构造主队和客队的嵌套JSON对象，避免直接用select *导致字段冲突。修改后的SQL如下：

select row_to_json(t) from (
    select 
        g.id,
        g.date,
        g.home_team_id,
        g.away_team_id,
        row_to_json(home_team) as home_team,
        row_to_json(away_team) as away_team
    from games g
    inner join (select id as team_id, abbr from teams) home_team on home_team.team_id = g.home_team_id
    inner join (select id as team_id, abbr from teams) away_team on away_team.team_id = g.away_team_id
    where g.day = '2022-10-22T00:00:00'
) t;

说明 #

1. 放弃select *，明确列出需要的字段，避免重复的id、abbr字段引发键冲突。
2. 对teams表做子查询时，将id重命名为team_id，匹配期望的JSON键名。
3. 用row_to_json()分别将主队、客队的子查询结果转换为嵌套JSON对象，对应home_team和away_team键。

如果不需要保留home_team_id和away_team_id字段，直接从选择列表中移除即可。

内容的提问来源于stack exchange，提问作者Francis C