Querying JSONB fields with joins

Question

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 😵‍💫

ID	Name	Duration	Attendees
2	"The Final Countdown"	50	1
3	"The Next Plain"	0	1
1	"The Great Beyond"	600	3

How can I best perform an INNER JOIN on my attendees table and ALSO sum up all the durations in for each event?

You've tagged two different RDBMS, do you not know which platform you are using? — Stu
– Stu, Commented Feb 11, 2022 at 22:45
JSONB is a data type used by PostgreSQL. MySQL has only JSON. — Bill Karwin
– Bill Karwin, Commented Feb 11, 2022 at 22:51
My mistake. I didn't not mean to tag mysql. This has been fixed — BeMathis
– BeMathis, Commented Feb 11, 2022 at 22:54

Dzienny · Accepted Answer · 2022-02-11 23:49:39Z

1

You can count the duration in a subquery. You have most of the code that is needed already, you just have to move it, example:

SELECT
  events.id AS "ID",
  events.name AS "Name",
  (SELECT coalesce(SUM((occurrences_arry->>'duration')::int), 0)
   FROM jsonb_array_elements(events.occurrences) as occurrences_arry) as "Duration",
  COUNT(attendees.*) as "Attendees"
FROM
  events
    INNER JOIN attendees on attendees.event_id = events.id
GROUP BY events.id

A SUM returns null, if there are no rows, hence I used coalesce to default to 0 in this case.

edited Feb 11, 2022 at 23:49

answered Feb 11, 2022 at 23:43

Dzienny

3,4271 gold badge23 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dzienny Over a year ago

You can temporarily play with it in a sandbox.

BeMathis Over a year ago

Thankyou, I knew I was over-thinking this!

Collectives™ on Stack Overflow

Querying JSONB fields with joins

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related