MongoDB - Array of object filter with condition

Question

I have a json file that look like this in my collection :

[
   {
      "change":"00001",
      "patchset":"4"
   },
   //etc
]

Two different object can have the same "change" properties. So first I want to group them by "change" properties and inside this group I want the highest value of the "patchset" properties. I have managed to do this easily with this command

db.collections.aggregate([{$group:{_id:"$change",patchset_max:{$max:"$patchset"}}}])

but then, and this is where I lost it, with this max patchset, I want to get all the objects where object.patchset = max_patchset but still in the group array.

I tried with $filter and $match and then nested $group but nothing works,

Do you guys have any idea ?

Thanks

mickl · Accepted Answer · 2018-10-22 21:20:48Z

3

You need to use $$ROOT which is a special variable that represents whole document to get all the items for each group and then you can use $addFields to overwrite existing array and $filter to get only those docs that have patchset equal to patchset_max. Try:

db.collection.aggregate([
    {
        $group: {
            _id: "$change",
            patchset_max:{$max:"$patchset"},
            docs: { $push: "$$ROOT" }
        }
    },
    {
        $addFields: {
            docs: {
                $filter: {
                    input: "$docs",
                    as: "doc",
                    cond: {
                        $eq: [ "$patchset_max", "$$doc.patchset" ]
                    }
                }
            }
        }
    }
])

Sample playground here

answered Oct 22, 2018 at 21:20

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sofcal Over a year ago

Just as the OP didn't mention the version of Mongo being used, worth noting that $addFields was added in 3.4

CharlesR Over a year ago

Thanks mickl. Yes I forgot the version, it's 4.0.3, and your solution works well, but @sofcal's solution is shorter and works the same since I have the good version ! But thank you !

sofcal · Accepted Answer · 2018-10-22 21:28:23Z

2

EDIT Answer above is correct, but if on Mongo 3.2, here's an alternative

db.collection.aggregate([{ 
    $group: { _id: "$change", patchset: { $push: "$$ROOT" }, patchset_max:{ $max:"$patchset" } } 
},{ 
    $project: {
        patchset: {
            $filter: { 
                input: '$patchset',
                as: 'ps',
                cond: { $eq: ['$$ps.patchset', '$patchset_max'] }
            }
        }
    }
}])

answered Oct 22, 2018 at 21:28

sofcal

4904 silver badges7 bronze badges

1 Comment

CharlesR Over a year ago

Works like a charm in my version 4.0.2. the $project and $filter framework for aggregate with cond is very smart indeed ! Many Thanks !!

Collectives™ on Stack Overflow

MongoDB - Array of object filter with condition

2 Answers 2

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Related