Exclude from the selection the values ​that are in the array

I have two tables:

my_temp:

schedules:

I need to select only those time ranges from my_temp that correspond to the employee's schedule. Breaks must be excluded.

It is also necessary to check whether this day was a working day for the employee according to the schedule. If it is not, then leave only one entry in the table for this date and in column day_type write one of the following values: ['working_day', 'not_working_day']. In the start_time and end_time columns, write the time according to the work schedule. I'm having trouble excluding all entries that fall during the employee's break, entries that go beyond working hours are not excluded.

How to leave only one entry in the query for a date that is not a working day for the employee?

My query:

    select 
                e.employee_id, 
                e.date,
                case when 
                    EXTRACT(DOW FROM e.date) = ANY(SELECT jsonb_array_elements(s.workDays::jsonb)) 
                then granula_start else s.start_time end as granula_start,
                case when 
                    EXTRACT(DOW FROM e.date) = ANY(SELECT jsonb_array_elements(s.workDays::jsonb)) 
                then granula_end else s.end_time end as granula_end,
                count_seconds,
                case when 
                    EXTRACT(DOW FROM e.date) = ANY(SELECT jsonb_array_elements(s.workDays::jsonb)) 
                then 'working_day' else 'not_working_day' end as day_type
            FROM my_temp e
            inner join schedules s on s.employee_id = e.employee_id
      where
     
            granula_start
            >= 
            s.start_time
            and not exists (
                            select 1 from jsonb_array_elements(s.breaks::jsonb) br
                            where granula_start > (br->>'from')::time
                            and   granula_end < (br->>'to')::time
                          )

which returns:

Please help me. Here is my example: https://dbfiddle.uk/G542L9Wh

If I understand your problem well, there are two things we can do:

• First of all, instead of repeating case when extract(…) = any(…) then x else y end 3 times, we will put it as a new column wd ("Working Day") in a Common Table Expression that we will later reuse (hence the name "Common"!)
• By the way, PostgreSQL has operator @> to test for a JSONB array being a subset of another one. So by transforming our EXTRACT(…) to a JSONB array of 1 element, we can directly test if it is a subset of workDays:
  s.workDays @> to_jsonb(array[EXTRACT(DOW FROM e.date)])
• To get the conditional accordion effect on non-working days (aggregate all rows to one when not on a working day), we will group by case when <working day> then <criteria to group by granule> else <criteria to group by whole day> end: as granules are supposed unique, this will get 1 row per granule on working days, but 1 row for the whole day for non-working days (as intended)

with wd as
(
  select 
    e.employee_id, 
    e.date,
    e.count_seconds,
    e.granula_start,
    e.granula_end,
    s.start_time,
    s.end_time,
    s.workDays @> to_jsonb(array[EXTRACT(DOW FROM e.date)]) wd
  FROM my_temp e
  inner join schedules s on s.employee_id = e.employee_id
  where
    granula_start
    >= 
    s.start_time
    and not exists (
        select 1 from jsonb_array_elements(s.breaks::jsonb) br
        where granula_start >= (br->>'from')::time
        and   granula_end <= (br->>'to')::time
    )
)
select
  employee_id, date,
  case when wd then granula_start else start_time end granula_start,
  case when wd then granula_end else end_time end granula_end,
  sum(count_seconds) count_seconds, 
  wd
from wd
group by 1, 2, 3, 4, 6 -- Group by employee_id, date, and the two formulas to choose either the granula-specific start and end, or the start and end of the day for non-working days (don't bother repeating them here, use PostgreSQL's ability to specify "the 3rd and 4th columns"), as well as the "working day" flag.
order by 1, 2, 3;

This is shown as the last query of a fiddle, returning from your data:

Due to the question having some points vague, there are limitations to this solution:

• /!\ I've changed your > and < in the "break rule" to >= and <=: if a granule starts at exactly 12:30 (= start of the break), it will now be excluded (part of the break), while your query counted it as included in working hours.
• /!\ A granule crossing over both a worked hour and a break will be counted
• /!\ Your query's where granula_start >= s.start_time seems contradictory to your rule "entries that go beyond working hours are not excluded". Sticking to the SQL I'll interpret it as "entries out of schedule are excluded if they are before schedule, not exluded if they are after the schedule
• /!\ Doesn't handle well cross-day schedules (if someone works from 6 PM to 6 AM, we suppose we should take workdays as the day for the start, thus if workdays are monday to friday it means from monday evening to saturday morning; but then what if he is seen working on saturday 6:15 AM, 15 mn after the end of his schedule: do we consider it's already the non-working day (to aggregate as the next schedule, saturday 6:00 PM to sunday 6:00 AM), or is it still counted as out of schedule for the working day of friday to saturday (and then "entries that go beyond working hours are not excluded")?)