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.

Answer 2

This keeps 1 member from Attributes only, the one with the max date. If you want to keep multiple ones use the @ray solution that keeps all members that have the max-date.

*mongoplayground can lose the order, of fields in a document, if you see wrong result, test it on your driver, its bug of mongoplayground tool

Query1 (local-way)

Test code here

aggregate([
  {
    "$project": {
      "maxDateValue": {
        "$max": {
          "$map": {
            "input": "$Attributes",
            "in": { "Date": "$$this.Date", "Value": "$$this.Value" },
           }
         }
       },
      "Type": 1
     }
   },
  {
    "$project": {
      "Date": "$maxDateValue.Date", 
      "Value": "$maxDateValue.Value"
    }
  }
])

Query2 (unwind-way)

Test code here

aggregate([
  {
    "$unwind": { "path": "$Attributes" }
  },
  {
    "$group": {
      "_id": "$Type",
      "maxDate": {
        "$max": {
          "Date": "$Attributes.Date", 
          "Value": "$Attributes.Value"
        }
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "Type": "$_id",
      "Date": "$maxDate.Date",
      "Value": "$maxDate.Value"
    }
  }
])