Sorting on documents within an array

Question

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?

wdberkeley · Accepted Answer · 2015-01-30 16:14:55Z

1

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 }.

answered Jan 30, 2015 at 16:14

wdberkeley

11.7k1 gold badge30 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

amoeba Over a year ago

Great, thanks! I hadn't caught the aggregate framework. Those are two good solutions.

Collectives™ on Stack Overflow

Sorting on documents within an array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related