Displaying Multiple Rows of Data from Two Tables with a One to Many Relationship

I have two tables. Tracks and metadata. Tracks can have multiple metadata attributes, but metadata can only be attached to a single track. The metadata table has a foreign key for a track ID, but the tracks table has no foreign keys for metadata. I am trying to find the best way to get this result:

[{
   trackID: 6,
   trackName: "test", /* these are the data from the tracks table */
   metadata: [
     {
        bpm: 123 /* This is the data from the metadata table */
     },
     ...
   ]
},
{
   trackID: 7,
   trackName: "test2",
   metadata: [
     {
        bpm: 135
     },
     ...
   ]
}]

Essentially, i am trying to get multiple tracks, with each track having multiple metadata attributes. The only way i’ve been able to think of doing this is to first query for the tracks

SELECT * FROM tracks;

then on the backend, for each track returned send another query

SELECT * FROM metadata WHERE track=?

What is the better way of doing this, without sending many many queries to the database?

I have also tried a JOIN such as:

SELECT * FROM tracks LEFT JOIN metadata ON metadata.track = track.id WHERE track.id>5 LIMIT 10 ;

but this would require parsing it on the backend and checking each row to see if it has an identical track ID and overall also seems sluggish and suboptimal.

Is there a better way of doing this, what is the best practice here? Thank you in advance.

PS: I am using Postgres, NodeJS and Prisma if that helps.

Answer

You should use aggregation. It is not clear exactly what your data looks like and what you want the results to look like. For instance, if you only want the bpm column from metadata (as your results suggests), then this puts that value in an array column called metadata:

SELECT t.track_id, t.name,
       ARRAY_AGG(m.bpm) as metadata
FROM tracks t LEFT JOIN
     metadata m
     ON m.track = t.id
WHERE t.id > 5
GROUP BY t.track_id, t.name;

If you want additional columns, you can put the entire record in the array:

ARRAY_AGG(m) as metadata

And Postgres supports all of this with JSON functions as well.