Qfyilyi

Given documents such as

{

  _id: 'abcd',

  userId: '12345',

  activities: [

    { status: 'login', timestamp: '10000001' },

    { status: 'logout', timestamp: '10000002' },

    { status: 'login', timestamp: '10000003' },

    { status: 'logout', timestamp: '10000004' },

  ]

}

I am trying to create a pipeline such as all users that have their latest login/logout activities recorded between two timestamps will be returned. For example, if the two timestamp values are between 10000002 and 10000003, the expected document should be

{

  _id: 'abcd',

  userId: '12345',

  login: '10000003',

  logout: '10000002'

}

Of if the two timestamp values are between -1 and 10000001, the expected document should be :

{

  _id: 'abcd',

  userId: '12345',

  login: '10000001',

  logout: null

}

Etc.

I know it has to do with aggregations, and I need to $unwind, etc., but I'm not sure about the rest, namely evaluating two fields from the same document array

You can try below aggregation:

db.col.aggregate([

    { 

        $unwind: "$activities" 

    },

    {

        $match: {

            $and: [

                { "activities.timestamp": { $gte: "10000001" } },

                { "activities.timestamp": { $lte: "10000002" } }

            ]

        }

    },

    {

        $sort: {

            "activities.timestamp": -1

        }

    },

    {

        $group: {

            _id: "$_id",

            userId: { $first: "$userId" },

            activities: { $push: "$activities" }

        }

    },

    {

        $addFields: {

            login: { $arrayElemAt: [ { $filter: { input: "$activities", as: "a", cond: { $eq: [ "$$a.status", "login" ] } } } , 0 ] },

            logout: { $arrayElemAt: [ { $filter: { input: "$activities", as: "a", cond: { $eq: [ "$$a.status", "logout" ] } } } , 0 ] }

        }

    },

    {

        $project: {

            _id: 1,

            userId: 1,

            login: { $ifNull: [ "$login.timestamp", null ] },

            logout: { $ifNull: [ "$logout.timestamp", null ] }

        }

    }

])

We need to use $unwind + $sort + $group to make sure that our activities will be sorted by timestamp. After $unwind you can use $match to apply filtering condition. Then you can use $filter with $arrayElemAt to get first (latest) value of filtered array. In the last $project you can explicitly use $ifNull (otherwise JSON key will be skipped if there's no value)

You can use below aggregation

Instead of $unwind use $lte and $gte with the $fitler aggregation.

db.collection.aggregate([

  { "$project": {

    "userId": 1,

    "login": {

      "$max": {

        "$filter": {

          "input": "$activities",

          "cond": {

            "$and": [

              { "$gte": ["$$this.timestamp", "10000001"] },

              { "$lte": ["$$this.timestamp", "10000004"] },

              { "$lte": ["$$this.status", "login"] }

            ]

          }

        }

      }

    },

    "logout": {

      "$max": {

        "$filter": {

          "input": "$activities",

          "cond": {

            "$and": [

              { "$gte": ["$$this.timestamp", "10000001"] },

              { "$lte": ["$$this.timestamp", "10000004"] },

              { "$lte": ["$$this.status", "logout"] }

            ]

          }

        }

      }

    }

  }}

])