CODEWITHSUNDEEP
mongodbmongodb-query
Param
Param

Reputation: 137

how to sort before querying in the embedded document

I know how to sort the embedded document after the find results but how do I sort before the find so that the query itself is run on the sorted array ? I know this must be possible if I use aggregate but i really like to know if this is possible without that so that I understand it better how it works.

This is my embedded document

   "shipping_charges" : [
            {
                    "region" : "region1",
                    "weight" : 500,
                    "rate" : 10
            },
            {
                    "region" : "Bangalore HQ",
                    "weight" : 200,
                    "rate" : 40
            },
            {
                    "region" : "region2",
                    "weight" : 1500,
                    "rate" : 110
            },
            {
                    "region" : "region3",
                    "weight" : 100,
                    "rate" : 50
            },
            {
                    "region" : "Bangalore HQ",
                    "weight" : 100,
                    "rate" : 150
            }
    ]

This is the query i use to match the 'region' and the 'weight' to get the pricing for that match .. 

db.clients.find( { "shipping_charges.region" : "Bangalore HQ" ,  "shipping_charges.weight" : { $gte : 99 }  }, { "shipping_charges.$" : 1 }  ).pretty()

This query currently returns me the 

     {
    "shipping_charges" : [
            {
                    "region" : "Bangalore HQ",
                    "weight" : 200,
                    "rate" : 40
            }
    ]

}

The reason it possibly returns this set is because of the order in which it appears(& matches) in the embedded document.

But, I want this to return me the last set that best matches to closest slab of the weight(100grams)

What changes required in my existing query so that I can sort the embedded document before the find runs on them to get the results as I want it ?

If for any reasons you are sure this cant be done without a MPR, let me know so that i can stay away from this method and focus only on MPR to get the desired results as I want it .

Upvotes: 0

Views: 104

Answers (1)

JohnnyHK
JohnnyHK

Reputation: 311835

You can use an aggregation pipeline instead of map-reduce:

db.clients.aggregate([
    // Filter the docs to what we're looking for.
    {$match: {
        'shipping_charges.region': 'Bangalore HQ',
        'shipping_charges.weight': {$gte: 99}
    }},

    // Duplicate the docs, once per shipping_charges element
    {$unwind: '$shipping_charges'},

    // Filter again to get the candidate shipping_charges.
    {$match: {
        'shipping_charges.region': 'Bangalore HQ',
        'shipping_charges.weight': {$gte: 99}
    }},

    // Sort those by weight, ascending.
    {$sort: {'shipping_charges.weight': 1}},

    // Regroup and take the first shipping_charge which will be the one closest to 99
    // because of the sort.        
    {$group: {_id: '$_id', shipping_charges: {$first: '$shipping_charges'}}}
])

You could also use find, but you'd need to pre-sort the shipping_charges array by weight in the documents themselves. You can do that by using a $push update with the $sort modifier:

db.clients.update({}, {
    $push: {shipping_charges: {$each: [], $sort: {weight: 1}}}
}, {multi: true})

After doing that, your existing query will return the right element:

db.clients.find({ 
    "shipping_charges.region" : "Bangalore HQ",
    "shipping_charges.weight" : { $gte : 99 }
}, { "shipping_charges.$" : 1 } )

You would, of course, need to consistently include the $sort modifier on any further updates to your docs' shipping_charges array to ensure it stays sorted.

Upvotes: 1

Related Questions