javascript - 如何找到其数据与给定项目数组中至少一项的条件匹配的文档 - mongoDB

Question

我想获取存在于提供的日期数组中的记录。

这个提供的日期数组将获取开始和结束日期之间的记录。

例子:

我有这个架构:

[
    {
        "_id": "1",
        "course": "Maths",
        "startDate": "2022-06-07",
        "endDate": "2022-06-12"
    },
    {
        "_id": "2",
        "course": "Chemistry",
        "startDate": "2022-06-06",
        "endDate": "2022-06-09"
    },
    {
        "_id": "3",
        "course": "Physics",
        "startDate": "2022-06-08",
        "endDate": "2022-06-10"
    },
    {
        "_id": "4",
        "course": "Computer Science",
        "startDate": "2022-06-10",
        "endDate": "2022-06-18"
    },
    {
        "_id": "5",
        "course": "Computer Science",
        "startDate": "2022-06-10",
        "endDate": "2022-06-13"
    },
    {
        "_id": "6",
        "course": "Biology",
        "startDate": "2022-06-08",
        "endDate": "2022-06-10"
    }
]

提供的数组:

["2022-06-06", "2022-06-17"]

预期的响应是:

[
    {
        "_id": "2",
        "course": "Chemistry",
        "startDate": "2022-06-06",
        "endDate": "2022-06-09"
    },
    {
        "_id": "4",
        "course": "Computer Science",
        "startDate": "2022-06-10",
        "endDate": "2022-06-18"
    }
]

谁能帮我查询一下？谢谢

Answer 1

您可以将聚合管道与 $filter 一起使用:

1. 将日期数组添加到文档中
2. 添加一个 matchingDates 字段来计算 dates 数组中与条件匹配的项目。
3. $match 仅包含此类项目的文档。
4. 格式

db.collection.aggregate([
  {$addFields: {dates: ["2022-06-06", "2022-06-17"]}},
  {
    $set: {
      matchingDates: {
        $size: {
          $filter: {
            input: "$dates",
            as: "item",
            cond: {
              $and: [
                {$gte: [{$toDate: "$$item"}, {$toDate: "$startDate"}]},
                {$lte: [{$toDate: "$$item"}, {$toDate: "$endDate"}]}
              ]
            }
          }
        }
      }
    }
  },
  {$match: {matchingDates: {$gt: 0}}},
  {$unset: ["dates", "matchingDates"]}
])

Playground example