Question 116744 in Mongodb Question posted in
The official documentation can be found here.

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

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.

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search