I am quite new 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 passworDate:

  {
     "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?

CodePudding user response：

but I only get: ccccc

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

1. $match

2. $set - Set maxPasswordDate field.

3. $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.

4. $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