PostgreSQL array_agg but with stop condition

Question

I have table with record of children & i want to get comma separated results in descending order by month but with a breaking condition of status of child in each month. if status is 0 push it to array but if status is 1 then don't push it and break it there and don't check previous months record.

Table

Desired Output:

I have tried it this way which gives me all the months. but i don't know how to break it on status = 1 condition for every child

SELECT name, ARRAY_AGG(month ORDER BY month DESC)
FROM children
GROUP BY name

Answer 1

I think of this as:

SELECT name, ARRAY_AGG(month ORDER BY month DESC)
FROM (SELECT c.*,
             MAX(c.month) FILTER (c.status = 1) OVER (PARTITION BY c.name) as last_1_month
      FROM children c
     ) c
WHERE month > last_1_month 
GROUP BY name;

This logic simply gets the last month where status = 1 and then chooses all later months.

If month is actually sequential with no gaps then you can do:

SELECT name,
       ARRAY_AGG(month ORDER BY month DESC)[1:MAX(month) - MAX(month) FILTER (c.status = 1)]
FROM children c
GROUP BY name;

Answer 2

I'd use a not exists condition to filter out the records you don't want:

SELECT   name, ARRAY_AGG(month ORDER BY month DESC)
FROM     children a
WHERE    NOT EXISTS (SELECT *
                     FROM   children b
                     WHERE  a.name = b.name AND b.status = 1 and a.month <= b.month)
GROUP BY name