3

I want to fetch records that exists in provided dates array.

This provided array of dates will fetch records between start & end date.

Example:

I have this schema:

[
    {
        "_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"
    }
]

Provided array:

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

The expected response is:

[
    {
        "_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"
    }
]

Can anyone help me with the query? Thanks

nimrod serok
  • 14,151
  • 2
  • 11
  • 33
StormTrooper
  • 1,731
  • 4
  • 23
  • 37
  • You want to fetch records if either the startDate or endDate of the records exists in the array you provided? – Seerat Ahmed Jun 04 '22 at 11:22
  • the provided array will be 2 dates always? also what you want to keep, documents that exact match start and end date? or documents that their start/end date are inside those limits of the array? Also i think you should save normal dates in the database, not strings – Takis Jun 04 '22 at 11:23
  • @SeeratAhmed I need this array to fall between start & end dates – StormTrooper Jun 04 '22 at 11:36
  • @Takis it can be multiple dates, and these multiple dates should fall between start & end date – StormTrooper Jun 04 '22 at 11:38

1 Answers1

3

You can use an aggregation pipeline with $filter:

  1. Add the array of dates to the documents
  2. Add a matchingDates field that counts the items in the dates array that matches the condition.
  3. $match only documents that have such items.
  4. Format
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

nimrod serok
  • 14,151
  • 2
  • 11
  • 33