CODEWITHSUNDEEP
mongodb
amoeba
amoeba

Reputation: 4280

Sorting on documents within an array

I've tried very hard to read the documentation on finding and sorting but haven't had luck. I can change my document structure if it's not suitable for the operation I want to do but I'd prefer my current structure.

All users have names and an array of skills, each which have names and associated skill levels. All users have skill levels in every skill.

Here's my structure:

{
    "name": "Alice",
    "skills": [
        {
            "name": "gardening",
            "level": 10
        },
        {
            "name": "shopping",
            "level": 7
        }
    ]
},
{
    "name": "Bruce",
    "skills": [
        {
            "name": "gardening",
            "level": 5
        },
        {
            "name": "shopping",
            "level": 10
        }
    ]
}

And I have many of these objects, each of them I'll call Users. I'd like to sort Users by their Gardening skill level.

I know I can access level 10 Users, regardless of which skill they're level 10 in, with a find query:

{ 'skills.level' : 10 }

But I want to sort users by their Gardening skill level, with a sort command like:

{ "skills.gardening.level" : 1 }

But this doesn't work because of my current structure.

Do I need to change my structure in order to sort like this or can I make this work?

Upvotes: 0

Views: 35

Answers (1)

wdberkeley
wdberkeley

Reputation: 11671

You can make this work without changing your document structure, but the penalty is that the operation will not use an index and will be pretty slow for lots of users. Use the aggregation framework:

> db.users.aggregate([
    { "$unwind" : "$skills" },
    { "$match" : { "skills.name" : "gardening" } }
    { "$sort" : { "skills.level" : 1 } }
])
{ "_id" : ObjectId(...), "name" : "Bruce", "skills" : { "name" : "gardening", "level" : 5 } }
{ "_id" : ObjectId(...), "name" : "Alice", "skills" : { "name" : "gardening", "level" : 10 } }

In the absence of any other information about how you're using the collection, the simplest change to allow you to sort on individual skill levels is to redesign the documents so that one document represents one skill for one user:

{
    "name" : "Alice",
    "skill" : "gardening",
    "level" : 10
}

Sorting on level for a particular skill:

db.skills.find({ "skill" : "gardening" }).sort({ "level" : 1 })

Index on { "skill" : 1, "level" : 1 }.

Retrieving a collection of documents with equivalent information to your old user document:

db.skills.find({ "name" : "Alice" })

Index on { "name" : 1 }.

Upvotes: 1

Related Questions