MongoDB聚合中如何用$set返回符合条件的完整文档数组

数据结构 #

movies集合中的数据：

[
    {
        "timings": [
            {
                "start": "11:30 am",
                "end": "03:00 pm"
            },
            {
                "start": "11:30 pm",
                "end": "03:00 pm"
            }
        ]
    },
    {
        "timings": [
            {
                "start": "02:30 am",
                "end": "11:59 am"
            },
            {
                "start": "11:30 pm",
                "end": "03:00 pm"
            }
        ]
    },
    {
        "timings": [
            {
                "start": "08:30 am",
                "end": "11:30 am"
            },
            {
                "start": "09:30 am",
                "end": "11:30 am"
            }
        ]
    },
    {
        "timings": [
            {
                "start": "00:30 am",
                "end": "02:30 am"
            }
        ]
    },
    {
        "timings": []
    }
]

过滤条件 #

请求参数：

req.query = {starting: "03:00 am", ending: "11:59 pm"}

当前使用的聚合查询 #

db.collection.aggregate([
  {"$unwind": "$timings"},
  {$set: {
      startHour: {$add: [
          {$mod: [{$toInt: {$substr: ["$timings.start", 0, 2]}}, 12]},
          {$divide: [{$toInt: {$substr: ["$timing.start", 3, 2]}}, 60]},
          {$cond: [{$eq: [{$substr: ["$timings.start", 6, 2]}, "am"]}, 0, 12]}
      ]},
      startingHour: {$add: [
          {$mod: [{$toInt: {$substr: [req.query.starting, 0, 2]}}, 12]},
          {$divide: [{$toInt: {$substr: [req.query.starting, 3, 2]}}, 60]},
          {$cond: [{$eq: [{$substr: [req.query.starting, 6, 2]}, "am"]}, 0, 12]}
      ]},
      endingHour: {$add: [
          {$toInt: {$substr: [req.query.ending, 0, 2]}},
          {$divide: [{$toInt: {$substr: [req.query.ending, 3, 2]}}, 60]},
          {$cond: [{$eq: [{$substr: [req.query.ending, 6, 2]}, "am"]}, 0, 12]}
      ]},
      endHour: {$add: [
          {$toInt: {$substr: ["$timings.end", 0, 2]}},
          {$divide: [{$toInt: {$substr: ["$timings.end", 3, 2]}}, 60]},
          {$cond: [{$eq: [{$substr: ["$timings.end", 6, 2]}, "am"]}, 0, 12]}
    ]}
  },
  {$set: {
      endHour: {$cond: [{$eq: ["$endHour", 0]}, 24, "$endHour"]},
      endingHour: {$cond: [{$eq: ["$endingHour", 0]}, 24, "$endingHour"]}
    }
  },
  {$match: {
      $expr: {
        $and: [{$gte: ["$startHour", "$startingHour"]},
          {$lte: ["$endHour", "$endingHour"]}]
      }
    }
  },
  {$unset: ["startHour", "endHour", "startingHour", "endingHour"]}
])

预期输出 #

需要返回完整文档，共3条符合条件的结果：

[
    {
        "timings": [
            {
                "start": "11:30 am",
                "end": "03:00 pm"
            },
            {
                "start": "11:30 pm",
                "end": "03:00 pm"
            }
        ]
    },
    {
        "timings": [
            {
                "start": "02:30 am",
                "end": "11:59 am"
            },
            {
                "start": "11:30 pm",
                "end": "03:00 pm"
            }
        ]
    },
    {
        "timings": [
            {
                "start": "08:30 am",
                "end": "11:30 am"
            },
            {
                "start": "09:30 am",
                "end": "11:30 am"
            }
        ]
    }
]

实际输出 #

返回的是拆分后的单个timings对象，而非完整文档：

[
    {
      "_id": ObjectId("5a934e000102030405000000"),
      "timings": {
        "end": "03:00 pm",
        "start": "11:30 am"
      }
    },
    {
      "_id": ObjectId("5a934e000102030405000000"),
      "timings": {
        "end": "03:00 pm",
        "start": "11:30 pm"
      }
    },
    {
      "_id": ObjectId("5a934e000102030405000001"),
      "timings": {
        "end": "03:00 pm",
        "start": "11:30 pm"
      }
    },
    {
      "_id": ObjectId("5a934e000102030405000002"),
      "timings": {
        "end": "11:30 am",
        "start": "08:30 am"
      }
    },
    {
      "_id": ObjectId("5a934e000102030405000002"),
      "timings": {
        "end": "11:30 am",
        "start": "09:30 am"
      }
    }
]

问题分析与修正 #

核心问题 #

1. $unwind导致文档拆分：原查询先展开timings数组，之后未将拆分的文档合并回原结构，最终返回单个timings对象而非完整文档。
2. 拼写错误：$timing.start应为$timings.start（少末尾的s），会导致startHour计算错误。
3. 逻辑目标偏差：原查询筛选单个符合条件的timings项，而需求是保留整个文档（只要文档中存在至少一个符合条件的timings项）。

修正后的聚合查询 #

const { starting, ending } = req.query;

db.collection.aggregate([
  // 计算查询参数的时间数值
  {$set: {
    startingHour: {$add: [
      {$mod: [{$toInt: {$substr: [starting, 0, 2]}}, 12]},
      {$divide: [{$toInt: {$substr: [starting, 3, 2]}}, 60]},
      {$cond: [{$eq: [{$substr: [starting, 6, 2]}, "am"]}, 0, 12]}
    ]},
    endingHour: {$add: [
      {$mod: [{$toInt: {$substr: [ending, 0, 2]}}, 12]},
      {$divide: [{$toInt: {$substr: [ending, 3, 2]}}, 60]},
      {$cond: [{$eq: [{$substr: [ending, 6, 2]}, "am"]}, 0, 12]}
    ]}
  }},
  // 修正endingHour的0值为24
  {$set: {
    endingHour: {$cond: [{$eq: ["$endingHour", 0]}, 24, "$endingHour"]}
  }},
  // 判断文档中是否存在符合条件的timings项
  {$match: {
    $expr: {
      $anyElementTrue: {
        $map: {
          input: "$timings",
          as: "t",
          in: {
            $let: {
              vars: {
                startHour: {$add: [
                  {$mod: [{$toInt: {$substr: ["$$t.start", 0, 2]}}, 12]},
                  {$divide: [{$toInt: {$substr: ["$$t.start", 3, 2]}}, 60]},
                  {$cond: [{$eq: [{$substr: ["$$t.start", 6, 2]}, "am"]}, 0, 12]}
                ]},
                endHour: {$add: [
                  {$mod: [{$toInt: {$substr: ["$$t.end", 0, 2]}}, 12]},
                  {$divide: [{$toInt: {$substr: ["$$t.end", 3, 2]}}, 60]},
                  {$cond: [{$eq: [{$substr: ["$$t.end", 6, 2]}, "am"]}, 0, 12]}
                ]}
              },
              in: {
                $and: [
                  {$gte: ["$$startHour", "$startingHour"]},
                  {$lte: [{$cond: [{$eq: ["$$endHour", 0]}, 24, "$$endHour"]}, "$endingHour"]}
                ]
              }
            }
          }
        }
      }
    },
    // 排除timings为空的文档
    "timings": {$ne: []}
  }},
  // 清理临时字段
  {$unset: ["startingHour", "endingHour"]}
])

修正说明 #

1. 避免$unwind拆分：使用$map遍历timings数组，结合$anyElementTrue判断是否存在符合条件的项，直接匹配整个文档。
2. 修复拼写错误：将错误的$timing.start替换为遍历变量$$t.start，确保时间计算正确。
3. 统一时间计算逻辑：将查询参数和文档内的时间转换逻辑对齐，把0点（00:xx am）转换为24小时制的24。
4. 排除空数组文档：通过"timings": {$ne: []}过滤掉无放映时间的文档。

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