For simplicity I'll use the following patient and appointment tables. What I want is one report that contains the following data. So what I need to do is: Get the patient names from patient table aggregate all appointment statuses of the patient aggregate all appointment times of appointments that have status='active' So the initial query that I created is: DBFiddle

How to achieve conditional array aggregate?

For simplicity I’ll use the following patient and appointment tables.

CREATE TABLE patient
    (id int primary key, name text);


INSERT INTO patient
    (id, name)
VALUES
    (1, 'Jane'),
    (2, 'John');

CREATE TABLE appointment
    (patient_id int, status text, scheduled_time timestamp);

INSERT INTO appointment
    (patient_id, status, scheduled_time)
VALUES
    (1, 'completed', '2019-04-09 10:00:00'),
    (2, 'active',    '2022-10-01 11:30:00'),
    (2, 'canceled',  '2021-10-01 09:30:00');

What I want is one report that contains the following data.

Patient Name, All Appointment States, Future Active Appointments

So what I need to do is:

Get the patient names from patient table
aggregate all appointment statuses of the patient
aggregate all appointment times of appointments that have status=’active’

So the initial query that I created is:

SELECT p.name AS "Patient Name", 
array_agg(a.status) AS "All Appointment States", 
array_agg(a.scheduled_time) AS "Future Active Appointments"        
FROM patient p
LEFT JOIN appointment a ON a.patient_id = p.id
GROUP BY p.id;

DBFiddle

However the above satisfies the first two requirements, however for the third one it’s aggregating all scheduled times and not just those that have an active status.

It would be nice if array_agg(field WHERE ) works as shown below:

SELECT p.name AS "Patient Name", 
array_agg(a.status) AS "All Appointment States", 
array_agg(a.scheduled_time WHERE a.status='active') AS "Future Active Appointments"        
FROM patient p
LEFT JOIN appointment a ON a.patient_id = p.id
GROUP BY p.id;

But the above does not work. So is there another simple way of achieving this?

Answer

You are looking for the filter() option:

SELECT p.name AS "Patient Name", 
       array_agg(a.status) AS "All Appointment States", 
       array_agg(a.scheduled_time) filter (WHERE a.status='active') AS "Future Active Appointments"        
FROM patient p
  LEFT JOIN appointment a ON a.patient_id = p.id
GROUP BY p.id;

Advertisement

Answer