Mongoose - How to get the number of users who have the USA country in their sessions array

Question

I have user table and my table has an array called sessions for each user. in the each session I have some property like ip, country, os and ... so this is small part of my table :

const userSchema = new Schema({
    name: {
        type: String,
        required: true
    },
    sessions: [{
        token: {
            type: String,
            required: true
        },
        agent: {
            ip: {
                type: String,
                required: true
            },
            country: {
                type: String,
                required: true
            },
        },
        update: {
            type: Date,
            default: Date.now
        },
    }]
})

I want to know which session of each user has highest update value and what is the value of agent.country in that session. so this is my code :

let count = await User.countDocuments({ 'sessions.agent.country': 'usa' })
console.log('count: ' + count)

But this code is wrong because it doesn't care about the highest update value in that session.

Answer 1

Complex operations like these are best done with the aggregate function. Haven't verified whether this works exactly, but it should be roughly what you are looking for:

const result = await User.aggregate([
  // We unwind the session arrays into individual entries
  {
    $unwind: "$sessions"
  },
  // We rank each session by the update time for each user
  {
    $setWindowFields: {
        partitionBy: "$_id",
        sortBy: { 'sessions.update': -1 },
        output: {
          rankByUpdateTime: {
              $rank: {}
          }
        }
    }
  },
  // We filter to just take the latest session (rank=1) and only usa sessions
  {
    $match: {
      rankByUpdateTime: 1,
      'sessions.agent.country': 'usa'
    }
  },
  // We count the total documents left
  {
    $count: 'matchedItems'
  }
]);

const count = result.matchedItems