How to create an index for array length in MongoDB?

Question

I have a find query, where I need to check if there are any elements in an array on documents. I would like to index the size of the arrays to optimize the query, but I am not sure how to create such an index.

I have created the filter through the MongoDB C# driver:

Builders<Post>.Filter.And(new List<FilterDefinition<Post>>()
{
    Builders<Post>.Filter.Eq(x => x.Category, Category.COLLECTION),
    Builders<Post>.Filter.SizeGt(x => x.Details.References, 0)
})

The document type looks like this:

{ 
    "_id" : ObjectId("577a271f2b365917c4d72678"), 
    "UserId" : ObjectId("577a26a32b365917c4d67e42"), 
    "Category" : NumberInt(4), 
    "Details" : {
        "_id" : ObjectId("577a271f2b365917c4d72677"), 
        "References" : [
            {
                "RefId" : ObjectId("577a887b4fd1ae0e1c7b2035"), 
                "Order" : NumberInt(0), 
                "RefType" : NumberInt(1)
            }
        ]
    }, 
    "Modified" : ISODate("2016-07-05T01:26:33.549+0000"),  
    "IsActive" : true
}

The query looks like this:

{ 
    "query" : {
        "find" : "post", 
        "filter" : {
            "UserId" : {
                "$in" : [
                    ObjectId("577a26a12b365917c4d67dd5"),
                    ObjectId("577a26a12b365917c4d67dd3")
                ]
            }, 
            "IsActive" : true, 
            "$or" : [
                {
                    "Category" : NumberInt(4), 
                    "UserId" : {
                        "$nin" : [

                        ]
                    }, 
                    "Details.References.0" : {
                        "$exists" : true
                    }
                }, 
                {
                    "Category" : {
                        "$ne" : NumberInt(4)
                    }
                }
            ], 
            "Modified" : {
                "$lt" : ISODate("2016-07-04T13:59:32.094+0000")
            }
        }, 
        "sort" : {
            "Modified" : NumberInt(-1)
        }, 
        "limit" : NumberInt(10)
    }, 
    "time" : NumberInt(33), 
    "docsScanned" : NumberInt(1125)
}

As you can see it scans quite a few documents (1125) to find the right ones. I would like to reduce this number further, but I am not sure how to index this part:

"Details.References.0" : {
    "$exists" : true
}

How do I create an index for the length of references?

profesor79 · Accepted Answer · 2016-07-13 08:53:00Z

8

Mongo indexes do not provide such a function yet.

To store size of an array - as a solution you could have field arraySize and update it manually on every change on that array, then create field index on arraySize.

answered Jul 13, 2016 at 8:53

profesor79

9,5033 gold badges36 silver badges53 bronze badges

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

Collectives™ on Stack Overflow

How to create an index for array length in MongoDB?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related