By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
WITH users AS (
SELECT 1 AS id, 'Jake' AS name UNION ALL
SELECT 2, 'Adam'
),
seasons AS (
SELECT 1 AS ID, 'Fall' AS name UNION ALL
SELECT 2, 'Spring'
),
user_season AS (
SELECT 1 AS user_id, 1 AS season_id UNION ALL
SELECT 1, 2 UNION ALL
SELECT 2, 2
)
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;
| user_id | user_name | seasons |
|---|---|---|
| 1 | Jake | [{ "name": "Fall", "id":1}, { "name": "Spring", "id":2}] |
| 2 | Adam | [{ "name": "Spring", "id":2}] |