MongoDB - Getting the object which contains the maximum element from the array

Question

I am quite new to using MongoDB and need to query some aggregated data. I have this document:

{
   "username_id":"user01",   
   "passwordList":[
      {
         "passwordDate":{
            "$numberLong":"20230111012612"
         },
         "pass":"aaaa"
      },
      {
         "passwordDate":{
            "$numberLong":"20230111012614"
         },
         "pass":"bbbbb"
      },
      {
         "passwordDate":{
            "$numberLong":"20230111012900"
         },
         "pass":"ccccc"
      },      
   ]
}

What I want is to get the entire object that contains the maximum passwordDate:

{
   "passwordDate":{
      "$numberLong":"20230111012900"
   },
   "pass":"ccccc"
}

I tried this:

db.users.aggregate([
  {"$match": 
      {"username_id": "user01" }
  },
  {"$set":
      {"lastPassword":
          {"$max":
              {"$map":
                  {"input": "$passwordList",
                    "in": {"$max": "$$this.passwordDate"}
                  }
              }
          }
      }
  },
  {"$project": 
      {"lastPassword": 1,"_id":0 }
  }  
])

but I only get "ccccc". What is the best way to do it?

Yong Shun · Accepted Answer · 2023-01-11 02:02:06Z

but I only get: ccccc

From your query, you will not get "ccccc" for the lastPassword field, but you will get the max passwordDate.

$match
$set - Set maxPasswordDate field.
$set - Set lastPassword field.

3.1. $first - Get the first element of the array from the result 3.1.1.

3.1.1. $filter - Filter the document by matching passWordDate with maxPasswordDate in the passwordList array.
$project

db.users.aggregate([
  {
    "$match": {
      "username_id": "user01"
    }
  },
  {
    $set: {
      maxPasswordDate: {
        "$max": "$passwordList.passwordDate"
      }
    }
  },
  {
    "$set": {
      "lastPassword": {
        "$first": {
          "$filter": {
            "input": "$passwordList",
            "cond": {
              "$eq": [
                "$maxPasswordDate",
                "$$this.passwordDate"
              ]
            }
          }
        }
      }
    }
  },
  {
    "$project": {
      "lastPassword": 1,
      "_id": 0
    }
  }
])

Demo @ Mongo Playground

Collectives™ on Stack Overflow

MongoDB - Getting the object which contains the maximum element from the array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related