CODEWITHSUNDEEP
javascriptmongodbmongoose
user2402616
user2402616

Reputation: 1563

Return unique document per Mongo IN query

I'm querying a Collection that has 350k+ Documents. I'm querying a field using the IN clause where the IN is an array of 27k+ fields.

This actually seems to return rather fast in Mongoose. However, some of the matches of each item in the IN can have multiple Documents associated with them. I'd like to only have 1 Document returned per each match (sorted by another field). Is this possible?

Example Let's say I have a Collection of Fruit. 

[
 {type:'apple', price:10},{type:'apple', price:5},{type:'apple', price:3},
 {type:'orange', price:2},
 {type:'pear', price:12}, {type:'pear', price:2}
]

So, currently I have 

const types = ['apple', 'orange', 'pear'];

//Will return full example above
//Returns 12k Docs in real app but bc multiple Docs are returned per item in IN

Fruit.find({type: { $in: types }}, (err, results) => {
        if (err) return console.error(err);
        console.log(results);
});

I'd like to just have

 [
         {type:'apple', price:10}
         {type:'orange', price:2},
         {type:'pear', price:12}
    ]

How can I adjust my query to do something like this? Thanks! returned. So instead of all documents matching the type - I just get only 1 with the highest price.

Upvotes: 1

Views: 47

Answers (1)

mickl
mickl

Reputation: 49995

You need to $group by type and use $max to get highest prices:

db.collection.aggregate([
    {
        $match: { type: { $in: ['apple', 'orange', 'pear'] } }
    },
    {
        $group: {
            _id: "$type",
            price: { $max: "$price" }
        }
    },
    {
        $project: {
            type: "$_id",
            _id: 0,
            price: 1
        }
    }
])

Mongo Playground

Upvotes: 1

Related Questions