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

```sql

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:

1. Get the patient names from patient table
2. aggregate all appointment statuses of the patient
3. aggregate all appointment times of appointments that have status='active'

So the initial query that I created is:

```sql
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](https://dbfiddle.uk/?rdbms=postgres_12&fiddle=14fbe70aa6323c9c0a99d2f9bc8c3567)

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:

```sql
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?