MongoDB: Find duplicate docs where a field has lowest values

user_4617276 0
Viewed 53

so I have this problem

I have this duplicate collection that goes like:

{name: "a", otherField: 1, _id: "id1"},
{name: "a", otherField: 2, _id: "id2"},
{name: "a", otherField: 3, _id: "id3"},
{name: "b", otherField: 1, _id: "id4"}
{name: "b", otherField: 2, _id: "id5"}

My goal is to get id of with less otherField that will look like:

{"name": "a", _id: "id1"},
{"name": "a", _id: "id2"},
{"name": "b", _id: "id4"}

Since highest otherField from a and b is "id3" and "id5", I want id other than the highest otherField

How to achieve this through query in mongodb?

Thank you

aggregation-framework mongodb-query mongodb
1 Answers

You can try below query :

db.collection.aggregate([
    /** group all docs based on name & push docs to data field & find max value for otherField field */
    {
        $group: {
            _id: "$name",
            data: {
                $push: "$$ROOT"
            },
            maxOtherField: {
                $max: "$otherField"
            }
        }
    },
    /** Recreate data field array with removing doc which has max otherField value */
    {
        $addFields: {
            data: {
                $filter: {
                    input: "$data",
                    cond: {
                        $ne: [
                            "$$this.otherField",
                            "$maxOtherField"
                        ]
                    }
                }
            }
        }
    },
    /** unwind data array */
    {
        $unwind: "$data"
    },
    /** Replace data field as new root for each doc in coll */
    {
        $replaceRoot: {
            newRoot: "$data"
        }
    }
])

Test : MongoDB-Playground

Note : We might lean towards sorting docs on field otherField, but it's not preferable on huge datasets.

user_72376130
Related