How to combine 2 tables in query SQL and convert to JSON-CodePudding

I have these tables

albums (id integer, name text, year text)
songs (id integer, title text, year text, performer text, genre text, album_id text)

This is my query

SELECT * 
FROM albums 
JOIN songs ON albums.id = songs.album_id 
WHERE albums.id = id

And this is the result of that query:

"albums": [
            {
                "id": "Coldplay-5KkCY8_uEaTBQqO7",
                "name": "Viva la vida",
                "year": 2008,
                "title": "Fix you",
                "performer": "Coldplay",
                "genre": "Pop",
                "album_id": "album-2008-mwlgbkMXbSqt5hlo"
            },
            {
                "id": "Coldplay-5a-QCy2W2jFTfoTR",
                "name": "Viva la vida",
                "year": 2008,
                "title": "Yellow",
                "performer": "Coldplay",
                "genre": "Pop",
                "album_id": "album-2008-mwlgbkMXbSqt5hlo"
            }
        ]

As you can see, the albums is there twice for the same item.

I want my result for the albums to be like this:

"albums": [
            {
                "id": "album-2008-mwlgbkMXbSqt5hlo",
                "name": "Viva la vida",
                "year": 2008,
                "songs": [
                   {
                     "id": "Coldplay-5KkCY8_uEaTBQqO7"
                     "title": "Fix you",
                     "year": 2008,
                     "performer": "Coldplay",
                     "genre": "Pop",
                     "album_id": "album-2008-mwlgbkMXbSqt5hlo"
                   },
                   {
                     "id": "Coldplay-5a-QCy2W2jFTfoTR"
                     "title": "Yellow",
                     "year": 2008,
                     "performer": "Coldplay",
                     "genre": "Pop",
                     "album_id": "album-2008-mwlgbkMXbSqt5hlo"
                   }
                 ]
            }
        ]

How can I get this result?

CodePudding user response：

You can use json_agg function and create sub-json data

SELECT
  albums.id,
  albums.name,
  albums.year,
  json_agg(songs.*) as songs
FROM 
  albums JOIN songs ON albums.id = songs.album_id 
WHERE 
  albums.id = id
GROUP BY 1, 2, 3