I have three tables right now in a postgres DB:

ID  Name        

ID  Date    Venue   Price   Is_sold Ages    Time    Multi_day   Pit     

ID  Show_id Artist_id

A show can have one or many artists; eventually I'm going to produce JSON for all shows that looks like this:

    "bands": [
        "Domino And The Derelicts",
    "date": "2015-12-26T08:00:00.000Z",
    "venue": "Stork Club, Oakland",
    "time": null,
    "soldOut": false,
    "pit": false,
    "multiDay": false,
    "ages": "21+",
    "price": null


Right now I'm doing this pretty inefficiently.

First, I'm running a query: SELECT * FROM shows WHERE date >= ${date} ORDER BY date, where date is today's date.

Then, I'm taking the result and running a for loop through each individual show that results and running this query: SELECT artist.* FROM artist INNER JOIN artist_shows ON artist.id = artist_shows.artist_id AND artist_shows.show_id=${id}, where id is each id from the results of the first query on the Shows table.

I'm then stitching together the results of both queries to produce the resulting JSON.

Is there a way to perform a single JOIN operation on my tables so that I can avoid the loop that I'm running? I assume that there might be a way to merge my existing join with my first query so that I get a table with all of the artists and all of the show details in one table. The thing that is tripping me up is the fact that a show can have many artists associated with it.

2 Answers 2


You can only return single row results so grouping in the way you are envisioning is not all that natural. There is a way, however, to use aggregate functions to combine groups and get the exact JSON result you are looking for.

    array_agg(bands) bands,
  FROM shows
  JOIN (
    SELECT show_id, array_agg(artists.name)
    FROM artist_shows
    JOIN artists ON (artist_shows.artist_id = artists.id)
    GROUP BY show_id
  ) bands ON bands.show_id = shows.id
  WHERE date >= now()
) all_shows

Here we are using a subquery (bands) to group all artists names based on the show id and then join the aggregate array into the show row. Lastly you can combine all rows and return a JSON representation of the data. If you don't care to have Postgres wrap everything up into JSON then you can just use what is in the initial FROM clause.

  • getting an ERROR: relation "artists" does not exist on the JOIN statement.
    – fox
    Commented Dec 27, 2015 at 7:12
  • looks like I forgot that my table was named artist not artist, swapping that in works
    – fox
    Commented Dec 27, 2015 at 7:16

My coding mentor suggested that I break this up into two queries because it's going to be more performant, and then to do the assembly in js:

buildArtistHashFromArtistsByShow(artistsByShow) {
    const showArtistsHash = {};

    for (const artist of artistsByShow) {
        if (!(artist.show_id in showArtistsHash)) {
            showArtistsHash[artist.show_id] = [artist.name];
        } else {

    return showArtistsHash;

buildParsedShows(shows, artistsHash) {
    const parsedShows = [];
    for (const show of shows) {
        const artists = artistsHash[show.id];
            'bands': artists,
            'date': show.date,
            'venue': show.venue,
            'time': show.time,
            'soldOut': show.is_sold,
            'pit': show.pit,
            'multiDay': show.multi_day,
            'ages': show.ages,
            'price': show.price,
    return parsedShows;

async fetchParsedShowsWithGroupedBandsAfterToday() {
    try {
        const shows = await this.db.query(`
            SELECT shows.* FROM shows WHERE date >= now() ORDER BY date

        const artistsByShow = await this.db.query(`
            SELECT artist_shows.show_id, artist.*
                FROM artist_shows
                INNER JOIN artist ON artist_shows.artist_id = artist.id
                WHERE artist_shows.show_id IN (SELECT shows.id
                    FROM shows
                    WHERE date >= now())

        const artistsHash = this.buildArtistHashFromArtistsByShow(artistsByShow);

        const parsedShows = this.buildParsedShows(shows, artistsHash);

        return parsedShows;

    } catch (error) {

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.