MongoDB : aggregate query matching a specific date from array of start and end dates-CodePudding

I have a mongodb collection of users. The users can activate and deactivate themselves whenever they want. I am storing the start and end date of each activation. Now I want to get the list of users who were active on a specific date.

A record looks like that:

{
  "active" : true,
  "endDates" : [
     16.11.2021,
     27.06.2020
  ],
  "startDates" : [
    21.10.2022,
    16.10.2021,
    09.04.2020
  ]   
}

The startDates are more than endDates in the above example as the user is active at the moment.

Now if I want to check if a user was active on 12.05.2020 or 22.11.2022, it should return true, but it should return false for 17.12.2021.

I tried using unwind on both endDates and startDates like that:

collection
  .aggregate([
    { $match: {} },
    { $unwind: '$endDates' },
    { $unwind: '$startDates' },
  ])

But it gave me all possible combinations of start and end dates and returns 6 documents which is not useful to find the date range.

CodePudding user response：

One option to format your data is to use $dateFromString to format these strings to proper dates, and $zip with $reverseArray to couple them in the right order:

db.collection.aggregate([
  {$project: {
      endDates: {
        $map: {
          input: "$endDates",
          in: {$dateFromString: {
              dateString: "$$this",
              format: "%d.%m.%Y"
          }}
        }
      },
      startDates: {
        $map: {
          input: "$startDates",
          in: {$dateFromString: {
              dateString: "$$this",
              format: "%d.%m.%Y"
          }}
        }
      }
  }},
  {$project: {
      dates: {
        $zip: {
          inputs: [
            {$reverseArray: "$startDates"},
            {$reverseArray: "$endDates"}
          ],
          useLongestLength: true
        }
      }
  }}
])

See how it works on the playground example

Now, if you want to check a specific date, you can add one more step to $filter your array according to your requested date and replace the result with a boolean:

{$project: {
      res: {
        $toBool: {$size: {
            $filter: {
              input: "$dates",
              cond: {
                $and: [
                  {$gte: [ISODate("2021-12-17T00:00:00Z"), {$first: "$$this"}]},
                  {$or: [
                      {$lt: [ISODate("2021-12-17T00:00:00Z"), {$last: "$$this"}]},
                      {$eq: [{$last: "$$this"}, null]}
                  ]}
                ]
              }
            }
        }}
      }
  }}

See how it works on the playground example