PostgreSQL查询需求：展示面试官单日及本周已占用/可用面试时段

我正在用Retool制作仪表盘，现有users和interviews两张表，用户可兼任面试官与面试者：用户ID在interviews表中作为interviewer_id存储时为面试官，作为interviewee_id时为面试者。interviews表的start_time字段存储面试开始时间，users表的interview_slots字段为JSON格式的面试官时段数据（示例见下文）。

我当前的PostgreSQL查询语句如下，需要优化以实现：根据start_time查询，展示面试官单日（如12月20日周二）及本周的已占用时段和可用时段，格式参考示例中蝙蝠侠的展示形式。

示例interview_slots数据 #

{
  "monday": [
    {
      "start-time": "09:00",
      "end-time": "17:30"
    }
  ],
  "tuesday": [
    {
      "start-time": "09:00",
      "end-time": "17:30"
    }
  ],
  "wednesday": [
    {
      "start-time": "09:00",
      "end-time": "17:30"
    }
  ],
  "thursday": [
    {
      "start-time": "09:00",
      "end-time": "17:30"
    }
  ],
  "friday": [
    {
      "start-time": "09:00",
      "end-time": "17:30"
    }
  ]
}

当前SQL语句 #

SELECT users_tbl.id, 
  users_tbl.name,
  (select name from users where id = interview_tbl.interviewee_id) as interviewee,
  interview_tbl.start_time,
  interview_tbl.duration,
  interview_slots,
  concat(users_tbl.interview_slots->'monday'->0->'start-time',' - ', users_tbl.interview_slots->'monday'->0->'end-time')
    as Monday,
  concat(users_tbl.interview_slots->'tuesday'->0->'start-time',' - ', users_tbl.interview_slots->'tuesday'->0->'end-time')
    as Tuesday,
  concat(users_tbl.interview_slots->'wednesday'->0->'start-time',' - ', users_tbl.interview_slots->'wednesday'->0->'end-time')
    as Wednesday,
  concat(users_tbl.interview_slots->'thursday'->0->'start-time',' - ', users_tbl.interview_slots->'thursday'->0->'end-time')
    as Thursday,
  concat(users_tbl.interview_slots->'friday'->0->'start-time',' - ', users_tbl.interview_slots->'friday'->0->'end-time')
    as Friday,
  concat(users_tbl.interview_slots->'saturday'->0->'start-time',' - ', users_tbl.interview_slots->'saturday'->0->'end-time')
    as Saturday,
  concat(users_tbl.interview_slots->'sunday'->0->'start-time',' - ',users_tbl.interview_slots->'sunday'->0->'end-time')
    as Sunday
  FROM users as users_tbl
  JOIN interviews interview_tbl
  ON users_tbl.id=interview_tbl.interviewer_id 
  AND interview_tbl.created_at >= CURRENT_DATE;

需求示例 #

若12月20日周二10:00有一场1小时的面试，需展示面试官（如蝙蝠侠）的周二已占用时段和可用时段，格式如下：

内容的提问来源于stack exchange，提问作者Suyog