MongoDB - Fetch document with the latest Timestamp from multiple levels of nested documents

Question

My requirement is to fetch the last updated document from the multiple levels of embedded list of documents. Is there any query or aggregation I can write to get only the document that is last updated from this list of documents?

Document Structure

{
  _id: "123",
  updatedAt: ISODate("2023-03-02T14:04:37.925+00:00"),
  embedded: [
    {
      _id: "456",
      updatedAt: ISODate("2023-04-02T14:04:37.925+00:00"),
      nested:[
           {
             _id: "147",
             updatedAt: ISODate("2023-04-15T14:04:37.925+00:00")
           },
           {
             _id: "258",
             updatedAt: ISODate("2023-03-11T14:04:37.925+00:00")
           }
        ]
    },
    {
      _id: "789",
      updatedAt: ISODate("2023-04-10T14:04:37.925+00:00")
    }
  ]
} 

I want to fetch the document with _id: "147" as it is the last updated document in the embedded list. I want to do this for all the root documents which have embedded key. In simple terms I want to sort on all the timestamps at any level and give out the latest updated document. I don't need to know at which level I found out the latest updated document, but the timestamp of that document. Have to keep in mind that the 1st level nested document might not have another level of embedded list of documents, Yet it needs to be compared to other documents in the list and their timestamps.

Expected Result

{
   _id: "123",
   updatedAt: ISODate("2023-03-02T14:04:37.925+00:00"),
   embedded: [
     {
        _id: "147",
        updatedAt: ISODate("2023-04-15T14:04:37.925+00:00")
     },
   ]
}

Answer 1

Taking all "updatedAt" dates into consideration, here's one way to find the document with the most recent date.

db.collection.aggregate([
  {
    "$project": {
      "_id": 1,
      "updatedAt": 1,
      "newestUpdatedAt": {
        "$cond": [
          {"$eq": [{"$type": "$embedded"}, "array"]},
          {
            "$reduce": {
              "input": "$embedded",
              "initialValue": {
                "_id": "$_id",
                "updatedAt": "$updatedAt"
              },
              "in": {
                "$cond": [
                  {"$eq": [{"$type": "$$this.nested"}, "array"]},
                  {
                    "$reduce": {
                      "input": "$$this.nested",
                      "initialValue": {
                        "$cond": [
                          {"$gt": ["$$this.updatedAt", "$$value.updatedAt"]},
                          {
                            "_id": "$$this._id",
                            "updatedAt": "$$this.updatedAt"
                          },
                          "$$value"
                        ]
                      },
                      "in": {
                        "$cond": [
                          {"$gt": ["$$this.updatedAt", "$$value.updatedAt"]},
                          {
                            "_id": "$$this._id",
                            "updatedAt": "$$this.updatedAt"
                          },
                          "$$value"
                        ]
                      }
                    }
                  },
                  {
                    "$cond": [
                      {"$gt": ["$$this.updatedAt", "$$value.updatedAt"]},
                      {
                        "_id": "$$this._id",
                        "updatedAt": "$$this.updatedAt"
                      },
                      "$$value"
                    ]
                  }
                ]
              }
            }
          },
          {
            "_id": "$_id",
            "updatedAt": "$updatedAt"
          }
        ]
      }
    }
  },
  {"$sort": {"newestUpdatedAt.updatedAt": -1}},
  {"$limit": 1}
])

Example output:

[
  {
    "_id": ObjectId("644f44d1820a8702096444e5"),
    "newestUpdatedAt": {
      "_id": ObjectId("644f44d1820a8702096444ea"),
      "updatedAt": ISODate("2023-05-01T22:40:11Z")
    },
    "updatedAt": ISODate("2023-02-20T10:38:53Z")
  }
]

Try it on mongoplayground.net.