CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Mohammad Taherian
Mohammad Taherian

Reputation: 1694

Problem in using indexes in aggregation pipeline

I have a query like this 

db.UserPosts.aggregate([
{ "$match" : { "Posts.DateTime" : { "$gte" : ISODate("2018-09-04T11:50:58Z"), "$lte" : ISODate("2018-09-05T11:50:58Z") } } }, 
{ "$match" : { "UserId" : { "$in" : [NUUID("aaaaaaaa-cccc-dddd-dddd-5369b183cccc"), NUUID("vvvvvvvv-bbbb-ffff-cccc-e0af0c8acccc")] } } },     
    { "$project" : { "_id" : 0, "UserId" : 1, "Posts" : 1 } }, 
    { "$unwind" : "$Posts" },     
    { "$unwind" : "$Posts.Comments" },     
    { "$sort" : {"Posts.DateTime" : -1} },     
    { "$skip" : 0 }, { "$limit" : 20 },
    { "$project" : { "_id" : 0, "UserId" : 1, "DateTime" : "$Posts.DateTime", "Title" : "$Posts.Title", "Type" : "$Posts.Comments.Type", "Comment" : "$Posts.Comments.Description" } },
    ],{allowDiskUse:true})

I have a compound index 

{
    "Posts.DateTime" : -1,
    "UserId" : 1
}

Posts and Comments are array of objects. I've tried different types of indexes but the problem is it does not use my index in $sort stage. I changed the place of my $sort stage but wasn't successful. It seems it is working in $match but not set to $sort. I even tried 2 simple indexes on those fields and combination of 2 simple indexes and one compound index but none of them works. I also read related documents in MongoDB website for

Could somebody please help me to find the solution?

Upvotes: 0

Views: 80

Answers (1)

Mohammad Taherian
Mohammad Taherian

Reputation: 1694

I solved this problem by changing my data model and moving DateTime to higher level of data.

Upvotes: 1

Related Questions