Mongodb get document that has max value for each subdocument

Question

I have some data looking like this:

    {'Type':'A',
     'Attributes':[
            {'Date':'2021-10-02', 'Value':5},
            {'Date':'2021-09-30', 'Value':1},
            {'Date':'2021-09-25', 'Value':13}
       ]
     },
    {'Type':'B',
     'Attributes':[
            {'Date':'2021-10-01', 'Value':36},
            {'Date':'2021-09-15', 'Value':14},
            {'Date':'2021-09-10', 'Value':18}
       ]
     }

I would like to query for each document the document with the newest date. With the data above the desired result would be:

{'Type':'A', 'Date':'2021-10-02', 'Value':5}
{'Type':'B', 'Date':'2021-10-01', 'Value':36}

I managed to find some queries to find over all sub document only the global max. But I did not find the max for each document.

Thanks a lot for your help

Answer 1

Starting from MongoDB v5.0+, you can use $setWindowFields to compute $rank for $unwinded documents and select the rank: 1 entry.

db.collection.aggregate([
  {
    "$unwind": "$Attributes"
  },
  {
    "$setWindowFields": {
      "partitionBy": "$_id",
      "sortBy": {
        "Attributes.Date": -1
      },
      "output": {
        "rank": {
          "$rank": {}
        }
      }
    }
  },
  {
    "$match": {
      "rank": 1
    }
  },
  {
    $project: {
      Type: 1,
      Date: "$Attributes.Date",
      Value: "$Attributes.Value"
    }
  }
])

Mongo Playground

Storing date as string is generally considered as bad pratice. Suggest that you change your date field into date type. Fortunately for your case, you are using ISO date format so some effort could be saved.

You can do this in aggregation pipeline:

1. use $max to find out the max date
2. use $filter to filter the Attributes array to contains only the latest element
3. $unwind the array
4. $project to your expected output

db.collection.aggregate([
  {
    "$addFields": {
      "maxDate": {
        $max: "$Attributes.Date"
      }
    }
  },
  {
    "$addFields": {
      "Attributes": {
        "$filter": {
          "input": "$Attributes",
          "as": "a",
          "cond": {
            $eq: [
              "$$a.Date",
              "$maxDate"
            ]
          }
        }
      }
    }
  },
  {
    $unwind: {
      path: "$Attributes",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $project: {
      Type: 1,
      Date: "$Attributes.Date",
      Value: "$Attributes.Value"
    }
  }
])

Here is the Mongo playground for your reference.