How to get a JSON output with nested objects using SQL for postgres

Question

I have a table called games which holds information about a 'home team' and an 'away team'

there is a foreign key relationship in that games has a home_team_id and an away_team_id that refers to a team

My desired json output should look like this

{
   "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
}

however with this sql query

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;

i get a flat json output with duplicate keys like this

{
   "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
}

how do I write my sql in order to get the json output that i want

Answer 1

demo
Obviously, you need row_to_json twice. The desired result looks like some json and some table rows then row_to_json again to have only one json in the end.

--step1
SELECT
    *
FROM (
    SELECT
        row_to_json(home_team.*) AS home_team,
        row_to_json(away_team.*) AS away_team,
        g.*
    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) t;

--step2
WITH cte AS (
    SELECT
        *
    FROM (
        SELECT
            row_to_json(home_team.*) AS home_team,
        row_to_json(away_team.*) AS away_team,
        g.*
    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) t
)
SELECT
    row_to_json(cte.*)
FROM
    cte;