schedules.sql

select 
    p.person_id,
    p.full_name, p.mail, p.phone, p.location, p.tier
    , COALESCE(json_agg(json_build_object(
        'date_start', v.date_start,
        'date_end', v.date_end,
        'type_id', v.type_id,
        'type', ty.title
    )) FILTER (WHERE v.vacation_id IS NOT NULL), '{}')
    as vacations, p.scopes::json
    , COALESCE(json_agg(row_to_json(h)) FILTER (WHERE h.hour IS NOT NULL), '{}')
    as hours
    , COALESCE(json_agg(row_to_json(d)) FILTER (WHERE d.day_id IS NOT NULL), '{}')
    as days
from (
    select
        p.person_id, p.full_name, p.mail, p.phone, l.title as location, t.tier, t.tier_id
        ,
        COALESCE(json_agg(json_build_object(
            'title', s.title,
            'grade', c.grade,
            'is_customer', s.is_customer
        )) FILTER (WHERE c.competence_id IS NOT NULL), '{}')::text
        as scopes
    from persons p
        join locations l on l.location_id = p.location_id
        join tiers t on p.person_id = t.person_id
        LEFT OUTer join competence c on c.tier_id = t.tier_id
        LEFT OUTer join scopes s on s.scope_id = c.scope_id
    group by p.person_id, l.title, t.tier, t.tier_id
) p
    LEFT OUTer join vacations v on p.person_id = v.person_id
    LEFT OUTer join vacation_types ty on ty.type_id = v.type_id
    LEFT OUTer join hours h on h.tier_id = p.tier_id
    LEFT OUTer join days d on h.day_id= d.day_id
group by p.person_id, p.scopes, p.full_name, p.mail, p.phone, p.location, p.tier;