How to get the results of conditions in array_agg into one line using postgres?

Question

I need to process this table, divide the data into columns based on the value in exception_type in postgres.

service_id, date, exception_type
...
"2:11:CIST-100385-1-2023",2020-12-24,"2"
"2:11:CIST-100385-1-2023",2020-12-26,"2"
"2:11:CIST-100385-1-2023",2021-04-02,"1"
"2:11:CIST-100385-1-2024",2020-12-24,"1"
"2:11:CIST-100385-1-2024",2021-11-17,"1"
"2:11:CIST-100385-1-2024",2020-12-26,"2"
...

My code:

SELECT service_id,
case 
when calendardates.exception_type='1'  then array_to_string(array_agg(concat(calendardates.date,' ')), ', ')
end as availabe,
case 
when calendardates.exception_type='2'  then array_to_string(array_agg(concat(calendardates.date,' ')), ', ')
end as unavailable
FROM calendardates
group by service_id ,exception_type

The output of this query is a table with merged data, but always one column with values, and another with null. I need each service_id once with both availabe and unavailabe dates.

This is what i get:

service_id, availabe, unavailabe
"2:100:CIST-595009-1-301","2021-12-26,2021-04-02,2021-04-05","[null]"
"2:100:CIST-595009-1-301","[null]","2021-01-01,2020-12-25"
"2:100:CIST-595009-1-302","2021-09-28,2021-05-08,2020-12-26","[null]"
"2:100:CIST-595009-1-302","[null]","2020-12-25,2021-01-01"

This is what i need:

service_id, availabe, unavailabe
"2:100:CIST-595009-1-301","2021-12-26,2021-04-02,2021-04-05","2021-01-01,2020-12-25"
"2:100:CIST-595009-1-302","2021-09-28,2021-05-08,2020-12-26","2020-12-25,2021-01-01"

Answer 1

Unfortunately as it stands in is impossible to get your desired results given the provided inputs. You would have to explain how service_id "2:11:CIST-100385-1-2023" gets translated to "2:100:CIST-595009-1-301" (or is it 2:...-302). Nor do the input dates correspond to the output dates. You seen to have a consistency issue.
But for organizing the output as desired you are trying to do too much at once. Instead of a single expression separate the available from the unavailable then aggregate the results. The following separates them in a sub-query then the main query aggregates. See here.

select service_id
     , string_agg( availabe, ', ' ) availabe 
     , string_agg( unavailabe, ', ' ) unavailabe        
  from (select service_id 
             , case when c.exception_type='1' then c.date::text end as availabe
             , case when c.exception_type='2' then c.date::text end as unavailabe
          from calendardates c
       ) s
group by service_id;

Notes:

1. Avoid double quoting table and column names, they are just not worth it the effort.
2. Do Not use date as a column name. It is a Postgres data type and SQL Standard reserved word. While you get away with Postgres developers would well within their rights to require it usage as defined. Thus invalidating all your work. Applies to any data type or reserved word.