MongoDB Indexes for dynamic sub-fields

Question

MongoDB 3.0.2. Documents examples:

{ "_id" : NumberLong(1), Categories : { "123" : { "Shows" : NumberLong(7), "Clicks" : NumberLong(0) }, "221" : { "Shows" : NumberLong(33), "Clicks" : NumberLong(12) } } }
{ "_id" : NumberLong(2), Categories : { "221" : { "Shows" : NumberLong(33), "Clicks" : NumberLong(12) } } }
{ "_id" : NumberLong(3), Categories : { "123" : { "Shows" : NumberLong(8), "Clicks" : NumberLong(1) } } }
{ "_id" : NumberLong(4), Categories : { "99" : { "Shows" : NumberLong(144), "Clicks" : NumberLong(39) }, "221" : { "Shows" : NumberLong(52), "Clicks" : NumberLong(2) } } }
{ "_id" : NumberLong(5), Categories : { "95" : { "Shows" : NumberLong(18), "Clicks" : NumberLong(3) } } }
{ "_id" : NumberLong(6), Categories : { "123" : { "Shows" : NumberLong(89), "Clicks" : NumberLong(69) } } }

In my case in this example string values like "123", "221", "99", "95" is some categories id's. I need sort only by Shows using category id:

db.myCollection.find().sort( Categories."1".Shows : -1 )
db.myCollection.find().sort( Categories."95".Shows : 1 )
db.myCollection.find().sort( Categories."123".Shows : 1 )
db.myCollection.find().sort( Categories."221".Shows : -1 )

But amount of this categories is about 250+. I cant set Indexes for each, like:

db.myCollection.createIndex( { Categories."[1..250]".Shows : 1 } );

because cat id's more than 64 (MongoDB limit) and this categories can be added and deleted to just some records and this process dynamical.

Without indexes i get:

Uncaught exception 'MongoCursorException' with message 'localhost:27017: Executor error: Overflow sort stage buffered data usage of 33554646 bytes exceeds internal limit of 33554432 bytes' in ..

Can someone give a solution for this case?

Answer 1

Using a slightly modified version of dnickless's schema as shown below:
{
    "_id" : 1,
    "Categories": [
        {
            "CategoryId": "123",
            "Shows": 7,
            "Clicks": 0
        },
        {
            "CategoryId": "221",
            "Shows": 33,
            "Clicks": 12
        }
    ]
}

You could then create an index on shows.
db.collection.createIndex({"Categories.Shows": 1})

Answer 2

I don't think you can solve this using your current document design.

You might want to model your categories in a different way, e.g. using an array for the Categories like this:

{
    "_id" : 1,
    "Categories": [
        {
            "CategoryId": "123",
            "Details": {
                "Shows": 7,
                "Clicks": 0
        }, {
            "CategoryId": "221",
            "Details": {
               "Shows": 33,
               "Clicks": 12
            }
        }
    ]
}

In this case you can easily add a single index db.collection.createIndex({"Categories.Details.Shows": 1})