CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
MKoosej
MKoosej

Reputation: 3696

sort by embedded object value in Mongodb

I have a schema like this:

{
   tags:[
      {
         id:"t1",
         score:70
      },
      {
         id:"t1",
         score:60
      }
   ]
 }

I want to sort the query on tag.id search to get sorted by the corresponding score. So if I search db.collection.find({tags.id:"t1"}).sort({tags.score:-1}) , it sorted by score of the "t1" object not other tags.

Any suggestion?

Upvotes: 0

Views: 1446

Answers (2)

Blakes Seven
Blakes Seven

Reputation: 50406

If you need to calculate out something like this at runtime, with "filtered" content from the array determining the sort order, then you best do something with .aggregate() to reshape and determine a sort value like this:

db.collection.aggregate([
    // Pre-filter the array elements
    { "$project": {
        "tags": 1,
        "score": {
            "$setDifference": [
                { "$map": {
                    "input": "$tags",
                    "as": "tag",
                    "in": {
                        "$cond": [
                            { "$eq": [ "$$el.id", "t1" ] },
                            "$$el.score",
                            false
                        ]
                    }
                }},
                [false]
            ]
        }
    }},
    // Unwind to denormalize
    { "$unwind": "$score" },
    // Group back the "max" score
    { "$group": {
        "_id": "$_id",
        "tags": { "$first": "$tags" },
        "score": { "$max": "$score" }
    }},
    // Sort descending by score
    { "$sort": { "score": -1 } }
])

Where the first part of the pipeline is used to "pre-filter" the array content ( as well as keeping the original field ) to just those values of "score" where the id is equal to "t1". This is done by processing $map which applies a condition to each element via $cond to determine whether to return the "score" for that element or false.

The $setDifference operation does a comparison to a single element array [false] which effectively removes any false values returned from the $map. As a "set", this also removes duplicate entries, but for the sort purpose here this is a good thing.

With the array reduced and reshaped to values you process $unwind ready for the next stage to deal with the values as individual elements. The $group stage essentially applies $max on the "score" to return the highest value contained in the filtered results.

Then it is just a matter of applying the $sort on the determined value to order the documents. Naturaly if you wanted this the other way around then use $min and sort in ascending order instead.

Of course add a $match stage to the beginning if all you really want is documents that actually contain "t1" values for id within the tags. But that part is of least relevance to the sorting on filtered results you want to achieve.

The alternate to calculating is to do it all as you write entries to the array in the documents. Kind of messy, but it goes something like this:

db.collection.update(
    { "_id": docId },
    {
        "$push": { "tags": { "id": "t1", "score": 60 } },
        "$max": { "maxt1score": 60 },
        "$min": { "mint1score": 60 }
    }
)

Here the $max update operator only sets the value for the specified field if the new value is greater than the existing value or otherwise no property yet exists. The reverse case is true of $min, where only if less than it will be replaced with the new value.

This would of course have the effect of adding various additional properties to the documents, but the end result is sorting is greatly simplified:

db.collection.find().sort({ "maxt1score": -1 })

And it's going to run a lot faster than calculating with an aggregation pipeline.

So consider the design principles. Structured data in arrays where you want filtered and paired results for sorting means calculating at run-time to determine which value to sort on. Adding additional properties to the document on .update() means you can simply reference those properties in order to directly sort results.

Upvotes: 1

Rubin Porwal
Rubin Porwal

Reputation: 3845

According to above mentioned description the query

db.collection.find({tags.id:"t1"}).sort({tags.score:-1})

will filter the documents consisting of tag id 1 and will return the results sorted by score in descending order.

Also please provide detailed description along with example document and expected output results

Upvotes: 0

Related Questions