Mongo aggregation; match where a field's subfield has at least one array element greater than specified value

Dashiell Rose Bark-Huss 0
Viewed 49

I'm trying to find Users who logged in the last day.

The userActivity field is an object on User. The userActivity object contains a field called hourly which is an array of dates. I want the find any users who's hourly array contains a date greater than a day ago using aggregation.

User schema

{
userName:"Bob",
userActivity:
    {"hourly":
       [
         "2022-05-09T02:31:12.062Z", // the user logged in
         "2022-05-09T19:37:42.870Z"  // saved as date object in the db
       ]
    }
}

query that didn't work

    const oneDayAgo = new Date();
    oneDayAgo.setDate(oneDayAgo.getDate() - 1);

    const usersActiveToday = await User.aggregate([
      {
        $match: {
          $expr: { $gt: [oneDayAgo, '$userActivity'] },
        },
      },
    ]);

If today is September 13, 11pm, I'd expect the results to the above to show users who had activity between the 12th and 13th.

Instead I am getting all users returned.

aggregation-framework mongodb-query mongoose mongodb javascript
3 Answers

If you want to use an aggregation pipeline, then one option is to use $max to find if there are items that are greater than oneDayAgo:

db.collection.aggregate([
  {$match: {$expr: {$gt: [{$max: "$userActivity.hourly"}, oneDayAgo]}}}
])

See how it works on the playground example - aggregation

But, you can also do it simply by using find:

db.collection.find({
  "userActivity.hourly": {$gte: oneDayAgo}
})

See how it works on the playground example - find

nimrod serok0

This can be considered as a 3 step process

  1. Find the max value in hourly array and store it in some key(here max)
  2. Check if the max value is greater than or equal to oneDayAgo timestamp
  3. Unset the key that stored max value

Working Code Snippet:

const oneDayAgo = new Date();
oneDayAgo.setDate(oneDayAgo.getDate() - 1);

const usersActiveToday = await User.aggregate([
    {
        $set: {
            max: {
                $max: {
                    $map: {
                        input: "$userActivity.hourly",
                        in: {
                            $max: "$$this",
                        },
                    },
                },
            },
        },
    },
    {
        $match: {
            max: {
                $gte: oneDayAgo.toISOString(),
            },
        },
    },
    {
        $unset: "max",
    },
]);

Here's code in action: Mongo Playground

Akshit Soneji0

You can try something along these lines:

db.collection.aggregate([
  {
    "$addFields": {
      "loggedInADayBefore": {
        "$anyElementTrue": [
          {
            "$map": {
              "input": "$userActivity.hourly",
              "as": "time",
              "in": {
                "$gte": [
                  "$$time",
                  ISODate("2022-05-13T00:00:00.000Z")
                ]
              }
            }
          }
        ]
      }
    }
  },
  {
    "$match": {
      loggedInADayBefore: true
    }
  },
  {
    "$project": {
      loggedInADayBefore: 0
    }
  }
])

Here, we $anyElementTrue, to find if any element in the array hourly, is greater than the day before, and store it as a boolean value in a new field. Then we filter the docs on the basis of that field using $match.

Here's the playground link.

Charchit Kapoor0
Related