Get users with a list of associated seasons

user_4401054 0
Viewed 26

I’m trying to get a list of users with a list of their associated seasons in Postgres. They’re connected to a pivot table.

I am completely lost on how to do the query. I know just joining all 3 tables in a query will return all the rows I need, so technically I could just do an array.reduce with JavaScript, but I think a proper query might be more efficient.

Any ideas?

tables:

users

id name
1 Jake
2 Adam

seasons

id name
1 Fall
2 Spring

user_season

user_id season_id
1 1
1 2
2 2

Desired Output

to send to frontend:

user_id user_name seasons
1 Jake [{ name: “Fall”, id: 1}, {name: “Spring”, id: 2}]
2 Adam [{name: “Spring”, id: 2}]
postgresql sql
1 Answers

Here is one approach using STRING_AGG:

SELECT u.id AS user_id, u.name AS user_name,
       '[' || STRING_AGG('{ "name": "' || s.name || '", "id":' || s.id || 
       '}', ', ') || ']' AS seasons
FROM users u
LEFT JOIN user_season us ON us.user_id = u.id
LEFT JOIN seasons s ON s.id = us.season_id
GROUP BY u.id, u.name
ORDER BY u.id;

screen capture from demo link below

Demo

user_18632290
Related