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.

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

If you want to use an aggregation pipeline, then one option is to $filter the array to find items that are greater than oneDayAgo:

db.collection.aggregate([
  {$match: {
      $expr: {
        $gt: [
          {$size: {
              $filter: {
                input: "$userActivity.hourly",
                cond: {$gte: ["$$this", oneDayAgo]}
              }
            }
          }, 0]
      }
    }
  }
])

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: ISODate("2022-05-13T00:00:00.000Z")
  }
})

See how it works on the playground example - find