Postgres - using `ARRAY_AGG()` aggregate function in multiple cases for same array

Question

I am trying to write an SQL query using CASE where I want to return the values from the campaign table of the selected row when on two cases:

1. The current time is between the start date and end date, which is displayed in the query. That part is working as expected and is included in the provided example.

2. WHERE publish_end_at is null. This is what I am trying to implement.

My query:

CASE
    WHEN publish_queue.message_id IS NOT NULL
    THEN
        (
         SELECT ARRAY_AGG(id) FROM campaign WHERE react_to_publish_queue_id = publish_queue.id
         AND (CURRENT_TIMESTAMP BETWEEN publish_start_at AND publish_end_at)
        )
     ELSE '{}'
 END AS running,
 CASE
     WHEN publish_queue.message_id IS NOT NULL
     THEN
         (
           SELECT ARRAY_AGG(id) FROM campaign WHERE react_to_publish_queue_id = publish_queue.id 
           AND publish_start_at < CURRENT_TIMESTAMP)
     ELSE '{}'
 END AS published,

Basically, I mean that if publish_end_at is null and publish_start_at is less than a minute older than the current timestamp, it should appear in the running. If it’s more than a minute, it should appear in the published.

I do not know how to define this part of the query in the already defined one which I provided an example. Should I use OR operator inside the aggregate function ARRAY_AGG() or I can do it differently?

There are two tables just for the sake of the representation of data:

Here is publish_queue table:

id	team_member_id	message_id	campaign_id
1859001	8566	1171	51543

campaign table:

id	team_member_id	publish_start_at	publish_end_at	react_to_publish_queue_id
2	8566	2022-05-30 09:19:00	null	1859001

The output would be:

"running_status": [
    2,
    ...
]

Can someone please help, I am pretty new to writing complex cases. Thanks

Answer 1

Assuming these conditions:

1. Status running:

   • publish_end_at is not known but publish_start_at was less than a minute ago

   OR

   • we're currently between publish_start_at and a known publish_end_at.

2. Status published:

   • publish_end_at unknown but publish_start_at was more than a minute ago

   OR

   • we're currently past a known publish_end_at timestamp.

CASE
    WHEN publish_queue.message_id IS NOT NULL
    THEN
        (SELECT ARRAY_AGG(id) 
         FROM campaign 
         WHERE react_to_publish_queue_id = publish_queue.id
         AND (   (    publish_end_at IS null
                  AND publish_start_at BETWEEN now()-'1 minute'::interval AND now() 
                 )
              OR (    publish_end_at IS NOT null
                  AND now() BETWEEN publish_start_at AND publish_end_at 
                 ) 
             )
        )
     ELSE '{}'
 END AS running,
 CASE
     WHEN publish_queue.message_id IS NOT NULL
     THEN
        (SELECT ARRAY_AGG(id) 
         FROM campaign 
         WHERE react_to_publish_queue_id = publish_queue.id
         AND (   (    publish_end_at IS null
                  AND publish_start_at < now()-'1 minute'::interval 
                 )
              OR (    publish_end_at IS NOT null
                  AND now() > publish_end_at  
                 )
             )
        )
     ELSE '{}'
 END AS published,

Demo