MongoDB聚合查询：字符串格式时间范围过滤问题求助

问题背景 #

我的movies集合数据如下：

[
  {id:1, name:"abch", start:"12:00 pm", end:"03:00 pm"},
  {id:2, name:"Annabelle", start:"08:30 am", end:"10:00 am"},
  {id:3, name:"Spider Man homecoming", start:"11:30 am", end:"03:00 pm"},
  {id:4, name:"Grudge", start:"10:00 pm", end:"00:00 am"}
]

需要根据查询参数req.query = { starting:"12:00 pm", ending:"00:00 am" }筛选出播放时间段与该范围有交集的电影，期望结果：

[
  {id:1, name:"abch", start:"12:00 pm", end:"03:00 pm"},
  {id:3, name:"Spider Man homecoming", start:"11:30 am", end:"03:00 pm"},
  {id:4, name:"Grudge", start:"10:00 pm", end:"00:00 am"}
]

我之前的聚合查询未生效：

movies.aggregate([
    $or: [
           {"start":req.query.starting},{"end":req.query.ending}
         ]
])

要求不能使用$dateFromString、new Date()或ISODate()函数，求正确查询语句。

问题分析 #

之前的查询存在两个核心问题：

1. 语法错误：聚合管道的每个阶段必须是对象，$or不能直接作为数组元素，必须包裹在$match阶段内；
2. 逻辑错误：用$or匹配start等于指定值或end等于指定值，完全不符合“时间段交集”的筛选逻辑，正确逻辑应该是：电影的播放时间段与查询的时间范围存在重叠，即电影的开始时间不晚于查询结束时间，且电影的结束时间不早于查询开始时间。

由于不能使用日期转换函数，我们需要将12小时制的时间字符串转换为分钟数（如12:00 pm转换为720分钟，00:00 am转换为1440分钟），通过数值比较实现范围判断。

正确查询语句 #

movies.aggregate([
  // 新增字段：将start和end转换为分钟数
  {
    $addFields: {
      startMinutes: {
        $let: {
          vars: {
            timeParts: { $split: ["$start", " "] },
            hourMinute: { $split: [{ $arrayElemAt: ["$$timeParts", 0] }, ":"] }
          },
          in: {
            $add: [
              {
                $multiply: [
                  {
                    $switch: [
                      { case: { $and: [{ $eq: [{ $arrayElemAt: ["$$timeParts", 1] }, "am"] }, { $eq: [{ $arrayElemAt: ["$$hourMinute", 0] }, "12"] }] }, then: 0 },
                      { case: { $and: [{ $eq: [{ $arrayElemAt: ["$$timeParts", 1] }, "pm"] }, { $ne: [{ $arrayElemAt: ["$$hourMinute", 0] }, "12"] }] }, then: { $add: [{ $toInt: { $arrayElemAt: ["$$hourMinute", 0] } }, 12] } },
                      { default: { $toInt: { $arrayElemAt: ["$$hourMinute", 0] } } }
                    ]
                  },
                  60
                ]
              },
              { $toInt: { $arrayElemAt: ["$$hourMinute", 1] } }
            ]
          }
        }
      },
      endMinutes: {
        $let: {
          vars: {
            timeParts: { $split: ["$end", " "] },
            hourMinute: { $split: [{ $arrayElemAt: ["$$timeParts", 0] }, ":"] }
          },
          in: {
            $add: [
              {
                $multiply: [
                  {
                    $switch: [
                      { case: { $and: [{ $eq: [{ $arrayElemAt: ["$$timeParts", 1] }, "am"] }, { $eq: [{ $arrayElemAt: ["$$hourMinute", 0] }, "12"] }] }, then: 0 },
                      { case: { $and: [{ $eq: [{ $arrayElemAt: ["$$timeParts", 1] }, "pm"] }, { $ne: [{ $arrayElemAt: ["$$hourMinute", 0] }, "12"] }] }, then: { $add: [{ $toInt: { $arrayElemAt: ["$$hourMinute", 0] } }, 12] } },
                      { default: { $toInt: { $arrayElemAt: ["$$hourMinute", 0] } } }
                    ]
                  },
                  60
                ]
              },
              { $toInt: { $arrayElemAt: ["$$hourMinute", 1] } }
            ]
          }
        }
      }
    }
  },
  // 筛选时间段有交集的文档
  {
    $match: {
      $expr: {
        $and: [
          // 电影开始时间 <= 查询结束时间（00:00 am转为1440分钟）
          {
            $lte: [
              "$startMinutes",
              {
                $cond: [
                  { $eq: [req.query.ending, "00:00 am"] },
                  1440,
                  {
                    $let: {
                      vars: {
                        timeParts: { $split: [req.query.ending, " "] },
                        hourMinute: { $split: [{ $arrayElemAt: ["$$timeParts", 0] }, ":"] }
                      },
                      in: {
                        $add: [
                          {
                            $multiply: [
                              {
                                $switch: [
                                  { case: { $and: [{ $eq: [{ $arrayElemAt: ["$$timeParts", 1] }, "am"] }, { $eq: [{ $arrayElemAt: ["$$hourMinute", 0] }, "12"] }] }, then: 0 },
                                  { case: { $and: [{ $eq: [{ $arrayElemAt: ["$$timeParts", 1] }, "pm"] }, { $ne: [{ $arrayElemAt: ["$$hourMinute", 0] }, "12"] }] }, then: { $add: [{ $toInt: { $arrayElemAt: ["$$hourMinute", 0] } }, 12] } },
                                  { default: { $toInt: { $arrayElemAt: ["$$hourMinute", 0] } } }
                                ]
                              },
                              60
                            ]
                          },
                          { $toInt: { $arrayElemAt: ["$$hourMinute", 1] } }
                        ]
                      }
                    }
                  }
                ]
              }
            ]
          },
          // 电影结束时间 >= 查询开始时间
          {
            $gte: [
              "$endMinutes",
              {
                $let: {
                  vars: {
                    timeParts: { $split: [req.query.starting, " "] },
                    hourMinute: { $split: [{ $arrayElemAt: ["$$timeParts", 0] }, ":"] }
                  },
                  in: {
                    $add: [
                      {
                        $multiply: [
                          {
                            $switch: [
                              { case: { $and: [{ $eq: [{ $arrayElemAt: ["$$timeParts", 1] }, "am"] }, { $eq: [{ $arrayElemAt: ["$$hourMinute", 0] }, "12"] }] }, then: 0 },
                              { case: { $and: [{ $eq: [{ $arrayElemAt: ["$$timeParts", 1] }, "pm"] }, { $ne: [{ $arrayElemAt: ["$$hourMinute", 0] }, "12"] }] }, then: { $add: [{ $toInt: { $arrayElemAt: ["$$hourMinute", 0] } }, 12] } },
                              { default: { $toInt: { $arrayElemAt: ["$$hourMinute", 0] } } }
                            ]
                          },
                          60
                        ]
                      },
                      { $toInt: { $arrayElemAt: ["$$hourMinute", 1] } }
                    ]
                  }
                }
              }
            ]
          }
        ]
      }
    }
  },
  // 移除临时字段
  {
    $project: {
      startMinutes: 0,
      endMinutes: 0
    }
  }
])

逻辑说明 #

1. 时间转分钟数：通过$split拆分时间字符串，用$switch处理12小时制到24小时制的转换，最终计算出总分钟数；
2. 跨天处理：将00:00 am转换为1440分钟（即当天24点），避免跨天时间比较的逻辑漏洞；
3. 交集判断：通过$lte和$gte判断电影时间段与查询时间段是否存在重叠，筛选出符合条件的文档；
4. 清理字段：最后移除用于计算的临时字段，返回原始结构的数据。

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