I have a database with two tables. One containing events & the other containing attendees for those events. Theses events have a jsonb field containing an array of objects with a "duration" attribute
events
| id | name | occurrences |
|---|---|---|
| 1 | "The Great Beyond" | [{"duration": 100}, {"duration": 200}, {"duration": 300}] |
| 2 | "The Final Countdown" | [{"duration": 50}] |
| 3 | "The Next Plain" | null |
attendees
| id | name | event_id |
|---|---|---|
| 1 | "Bob" | 1 |
| 2 | "Susan" | 2 |
| 3 | "Terry" | 3 |
I want to run a single query to pull data about these events & showcase the name of the event, how many people are attending & what the current "duration" is for each event (by summing up all the duration values in the occurrences column.
The current query I use is resulting in incorrect COUNT values for my attendees. I suspect it has to do with the way I am constructing my JOINs such that additional rows are being created.
SELECT
events.id AS "ID",
events.name AS "Name",
SUM(coalesce(occurrences_arry->'duration', '0'):int) as "Duration",
COUNT(attendees.*) as "Attendees"
FROM
events
INNER JOIN attendees on attendees.event_id = events.id
LEFT JOIN jsonb_array_elements(events.occurrences) as occurrences_arry on true
GROUP BY events.id
The result I get back has too high of a count for "Attendees" (The last record should have 1 attendees but says 3). I am pretty sure it has to do with the INNER JOIN LEFT JOIN combo I am using so that I can utilize jsonb_array_elements. Every time a new occurrence is added the attendees count grows