MongoDB find documents that match by regex and have biggest price

user_1507533 0
Viewed 46

Sample Docs :

{"_id": <id>, "name" : "Bread_1", "version" : 1 }
{"_id": <id>, "name" : "Bread_2", "version" : 1 }
{"_id": <id>, "name" : "Bread_1", "version" : 2 }
{"_id": <id>, "name" : "Bread_2", "version" : 2 }
{"_id": <id>, "name" : "Bread_3", "version" : 1 }

Required output :

{"_id": <id>, "name" : "Bread_1", "version" : 2 }
{"_id": <id>, "name" : "Bread_2", "version" : 2 }
{"_id": <id>, "name" : "Bread_3", "version" : 1 }

It's kind of grouping by name and biggest version. I've tried this query:

db.products.aggregate({$group: {_id: {name: '$name'}, version: {$max: '$version'}}});

Actual Output :

{"_id": {"name" : "Bread_1"}, "version" : 2 }
{"_id": {"name" : "Bread_2"}, "version" : 2 }
{"_id": {"name" : "Bread_3"}, "version" : 1 }

While I need an ID of the document as well. What did I miss in this case? Thank you

aggregation-framework mongodb-query mongodb
1 Answers

There is no direct way to get it, So you can try below query :

db.products.aggregate([
  /** Group on 'name' & get max of 'version' & push all docs to 'docs' array field */
  {
    $group: {
      _id: "$name",
      version: { $max: "$version" },
      docs: { $push: "$$ROOT" }
    }
  },
  /** Transform fields in required format, Iterate over docs array & get '_id' of doc where version do match */
  {
    $project: {
      name: "$_id",
      version: 1,
      _id: {
        $let: {
          vars: {
            id: {
              $arrayElemAt: [ // As filter returns an array get first doc, There can only be one doc
                {
                  $filter: {
                    input: "$docs",
                    cond: { $eq: ["$$this.version", "$version"] } // Check max version == current object's version
                  }
                },
                0
              ]
            }
          },
          in: "$$id._id"
        }
      }
    }
  }
]);

Test : MongoDB-Playground

user_72376130
Related