MongoDB index array issue

Question

I have question about mongoDB (version:"3.4.10") index arrays as I see they don't work correctly. Maybe I am doing something wrong.

I have schedule document that has some props that aren't important for this question. But each schedule has it's plan (occurrence) period in array.

{ .... "plans": [ { "startDateTime": "2018-01-04T00:00:00Z", "endDateTime": "2018-01-04T23:59:59Z" }, { "startDateTime": "2018-01-11T00:00:00Z", "endDateTime": "2018-01-11T23:59:59Z" } ... ] },

Now I need to search schedule documents by that array item and find all schedules that fit in that period. I have created index plans.startDateTime and plans.endDateTime.

When I perform following query using compas option explain plan I get bad really bad results.

{"Plans.StartDateTime": {$lt: new Date ('2018-01-10')}, "Plans.EndDateTime": {$gte: new Date ('2018-01-15')} }

Results are (this is in test environment where number of documents are really low, in production ratio would be even higher)

Documents Returned:2823
Index Keys Examined:65708
Documents Examined:11554

When I go little bit deeper in analysis I got following (meaning that mongo is ignoring plan end date in index search):

"indexBounds": { "Plans.StartDateTime": [ "(true, new Date(1515542400000))" ], "Plans.EndDateTime": [ "[MinKey, MaxKey]" ] },

Can somebody please tell me how to create better indexes for following search because this one isn't working?

Marcello · Accepted Answer · 2018-01-03 15:27:55Z

1

In order to find all scheduleDocuments having at least one plan overlapping with a given time interval (e.g. 2018-01-10 and 2018-01-14) you have to use $elemMatch MongoDB operator.

db.scheduleDocuments.find({
    plans: {
        $elemMatch: {
            startDateTime: { $lte: ISODate("2018-01-14Z") },
            endDateTime: { $gt: ISODate("2018-01-10Z") }
        }
    }
});

The rule used to test for overlapping intervals can be found here.

This search performs a collection scan, unless you create an index on the array.

db.scheduleDocuments.createIndex({
    "plans.startDateTime": 1,
    "plans.endDateTime": 1
});

Thanks to the index, unmatching documents in the collection are not scanned at all. An IXSCAN is performed and only matching documents are accessed to be fetched and returned.

edited Jan 3, 2018 at 15:27

answered Jan 3, 2018 at 15:02

Marcello

9196 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

MongoDB index array issue

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related