CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Sudhesh Gnanasekaran
Sudhesh Gnanasekaran

Reputation: 391

How to fetch records based on the max date in mongo document array

Below is the document which has an array name datum and I want to filter the records based on group by year and filter by the types and max date.

{
    "_id" : ObjectId("5fce46ca6ac9808276dfeb8c"),
    "year" : 2018,
    "datum" : [ 
        {
            "Type" : "1",
            "Amount" : NumberDecimal("100"),
            "Date" : ISODate("2018-05-30T00:46:12.784Z")
        }, 
        {
            "Type" : "1",
            "Amount" : NumberDecimal("300"),
            "Date" : ISODate("2023-05-30T00:46:12.784Z")
        }, 
        {
            "Type" : "2",
            "Amount" : NumberDecimal("340"),
            "Date" : ISODate("2025-05-30T00:46:12.784Z")
        }, 
        {
            "Type" : "3",
            "Amount" : NumberDecimal("300"),
            "Date" : ISODate("2021-05-30T00:46:12.784Z")
        }
    ]
}

The aggregate Query I tried.

[{$group: {
  _id :"$year",
  RecentValue : 
  {
     $sum: {
        $reduce: {
          input: '$datum',
          initialValue: {},
          'in': {
            $cond: 
         [
           {
             $and:
               [ 
                 {$or:[
        { $eq:  [ "$$this.Type", '2' ] }, 
        {$eq:  [ "$$this.Type", '3' ] }
        ]}, 
        { $gt:  [ "$$this.Date", "$$value.Date" ] },

                       ] 
           }
             , 
                "$$this.Amount",      
                 0     
            ]
          }
        }
      }
  }
}}]

the expected output would be which having the max date "2025-05-30T00:46:12.784Z"

{
_id   :2018,
RecentValue : 340 
}

Please let me know what mistake I did in the aggregate query.

Upvotes: 2

Views: 817

Answers (1)

turivishal
turivishal

Reputation: 36104

You can get max date before $group stage,

  • $addFields to get document that having max date from, replaced $or with $in condition and corrected return value
  • $group by year and sum Amount
db.collection.aggregate([
  {
    $addFields: {
      datum: {
        $reduce: {
          input: "$datum",
          initialValue: {},
          "in": {
            $cond: [
              {
                $and: [
                  { $in: ["$$this.Type", ["2", "3"]] },
                  { $gt: ["$$this.Date", "$$value.Date"] }
                ]
              },
              "$$this",
              "$$value"
            ]
          }
        }
      }
    }
  },
  {
    $group: {
      _id: "$year",
      RecentValue: { $sum: "$datum.Amount" }
    }
  }
])

Playground

Upvotes: 1

Related Questions