Looking to aggregate a result with the minimum date however the min should apply only to fields where the status is true?

Given the following data, $min on $dateModified will return date as 2 however I'm looking to return 3 because it is the lowest dateModified with the status of true

{name: "one", dateModified: "4", status: true},
{name: "one", dateModified: "2", status: false},
{name: "one", dateModified: "3", status: true}

.aggregate([
   $group: {
   _id: "$name",
   date: {$min: "$dateModified" // where status is true},
   total: {$sum: 1}
  }
])

I'll also need the overall count of true and false entries so applying a match on status: true before the group may prevent that

CodePudding user response：

Why not just add the $match stage as the first stage to filter the documents with status: true only?

db.collection.aggregate([
  {
    $match: {
      status: true
    }
  },
  {
    $group: {
      _id: "$name",
      date: {
        $min: "$dateModified"
      }
    }
  }
])

Sample Mongo Playground

As you are keen to remain the document with status: false, then you can work with $cond and $$REMOVE.

db.collection.aggregate([
  {
    $group: {
      _id: "$name",
      date: {
        $min: {
          $cond: {
            if: {
              $eq: [
                "$status",
                true
              ]
            },
            then: "$dateModified",
            else: "$$REMOVE"
          }
        }
      }
    }
  }
])

Sample Mongo Playground 2