Postgres - Return the results from multiple rows

Question

I have the below tables:

CREATE SCHEMA pulse;

CREATE TABLE pulse.event(
id integer,
url text,
name text, 
event_start  date, 
event_end   date,
sub_type text,
preference jsonb
);

CREATE TABLE pulse.event_meta(
event_id integer,
data json
);

CREATE TABLE pulse.slot_archive(
id integer,
event_id integer,
location_id integer, 
date text,
start_time text,
end_time text,
current_registration integer,
preference json
);

CREATE TABLE pulse.event_booking_archive(
id integer,
event_id integer,
slot_id integer, 
status text,
data json
);

The below query retrieves the event when the status is canceled (From the event table, preference column) along with additional data from other tables.

Query:

Select 
COALESCE((evnt.preference::json #>> '{eventStatus,status}' is not null)::boolean, false) as "eventCancelled", 
COALESCE(attendancecount,0) as "attendanceCount", 
COALESCE((meta.data ->> 'walkins')::int, 0) as walkins, 
COALESCE((meta.data ->> 'attendanceSubmitted')::boolean, false) as "attendanceSubmitted", 
meta.data -> 'heroImage' as "heroImage", 
meta.data -> 'tileContent' -> 'registrationPage' ->> 'title' as title, 
evnt.id as "eventId", 
evnt.url as "eventUrl", 
evnt.name as name, 
evnt.event_start AT TIME ZONE 'America/New_York' as "startTime",
evnt.event_end AT TIME ZONE 'America/New_York' as "endTime",  
evnt.sub_type as "subType", 
agg_slot.slotDates as slots, 
agg_slot.registrationcount as "registrationCount" from pulse.event as evnt 
inner join pulse.event_meta meta on evnt.id = meta.event_id 
left join (select event_id, COALESCE(sum((data ->> 'attendanceCount')::int),0) as attendancecount from pulse.event_booking_archive where status = 'SUCCESS' group by event_id) as eb 
on evnt.id = eb.event_id, 
(select event_id, location_id, array_agg(CONCAT_WS(' ', slot.date,slot.start_time,slot.end_time)) as slotDates, sum(current_registration) as registrationCount from pulse.slot_archive as slot 
group by slot.event_id, slot.location_id) as agg_slot 
where evnt.id = agg_slot.event_id 
and evnt.id in (select id from pulse.event where event_end + interval '48h' < now()) 
and agg_slot.location_id = '3305';

But, I need help in finding out the event whose event location is canceled. i.e All the slots for that particular event location_id in the slot_archive table have "statusMeta": "CS". I'm stuck here. Can someone please assist?

https://www.db-fiddle.com/f/iqxvqrjDrqb8B3tG1xzpHN/14

Answer 1

I rewrote your request to makes it more clear. So we have :

With booking_archive_success as 
(select event_id, COALESCE(sum((data ->> 'attendanceCount')::int),0) as attendancecount 
                    from pulse.event_booking_archive where status = 'SUCCESS' 
                        group by event_id)
, agg_slot as (
    select slot.event_id, slot.location_id
            , array_agg(CONCAT_WS(' ', slot.date,slot.start_time,slot.end_time)) as slotDates
            , sum(slot.current_registration) as registrationCount
            , array_agg(slot.preference->'slotStatus'->>'statusMeta') as statuses
            from pulse.slot_archive as slot 
            group by slot.event_id, slot.location_id)
select COALESCE((evnt.preference::json #>> '{eventStatus,status}' is not null)::boolean, false) as "eventCancelled"
    , COALESCE(attendancecount,0) as "attendanceCount"
    , COALESCE((meta.data ->> 'walkins')::int, 0) as walkins
    , COALESCE((meta.data ->> 'attendanceSubmitted')::boolean, false) as "attendanceSubmitted"
    , meta.data -> 'heroImage' as "heroImage"
    , meta.data -> 'tileContent' -> 'registrationPage' ->> 'title' as title
    , evnt.id as "eventId"
    , evnt.url as "eventUrl"
    , evnt.name as name
    , evnt.event_start AT TIME ZONE 'America/New_York' as "startTime"
    ,evnt.event_end AT TIME ZONE 'America/New_York' as "endTime"
    --, evnt.type as type
    , evnt.sub_type as "subType"
    , agg_slot.slotDates as slots
    , agg_slot.registrationcount as "registrationCount"
    , agg_slot.statuses
from pulse.event as evnt 
    inner join pulse.event_meta meta on evnt.id = meta.event_id 
    left join booking_archive_success as eb on evnt.id = eb.event_id
    inner join agg_slot on evnt.id = agg_slot.event_id
where evnt.id in 
                (select id from pulse.event where event_end + interval '48h' < now())
    and not exists (Select 1 from (Select unnest(agg_slot.statuses) as  statusMeta from agg_slot a Where a.event_id = evnt.id) t
                where statusMeta not like 'CS' or statusMeta is null 
               )
and agg_slot.location_id = '3305';

Several good habits to take :

• Prefer the using of CTE when it is possible. The code is more readable, and easier to conceive
• Avoid Cartesian Product in the from with the join condition in the where, so I fixed it by using an inner join clause

To solve (I hope) your problem, I used the unnest function in a "not exists" condition in the where. The goal is to forbid every eventid where one of the statusMeta is not CS