Aggregation Framework $unwind and Index

[Daniele Tassone]

Hi at all,

I have an Aggregation Framework pipeline that use $unwind in order to expand an Array.
After that I use $match so I can query inside any item into Array.

{ "aggregate" : "User" , "pipeline" : [

{ "$match" : { "geoname_address.countrycode" : "IT"}} ,
{ "$project" : { "geoname_address" : 1 , "User_WebSite" : 1 , "_id" : 1 , "nickname" : 1}} ,
{ "$unwind" : "$User_WebSite.User_GalleryImage"} ,
{ "$match" : { "User_WebSite.User_GalleryImage._GalleryId" : { "$in" : [ { "$oid" : "5059b3489e3d9804e4adb8f6"}}

I just want to know if _GalleryId can use an Index, and if Not how i can optimize this query.

Daniele Tassone

[Asya Kamsky]

Once you unwind the index cannot be used however you can have the $match in two places, once before the $unwind and once after.

The $match before unwind will only pas through the documents which have the matching value in the array.  Now since you already have another $match there best would be to do a single $match that has a compound index that can be used.

That one will use an index.

The one after will filter out the unwound array elements that didn't match - that one is done in memory.

Are you then doing some aggregation or just passing through the matching subdocuments back to the application?

Asya

[Daniele Tassone]

This is the Complete pipeline, modify as i have understand.

[
                   {"$match":
                    {
                     "geoname_address.countrycode":"{param1}",
                     "User_WebSite.User_WebSite_GalleryImage._GalleryId":{$in:[{param3}]}
                    }
                   },
               {"$project":
                            {
                              "geoname_address":1,
                              "User_WebSite":1,
                              "_id":1,
                              "nickname":1
                            }
                   },
           {"$unwind":"$User_WebSite.User_WebSite_GalleryImage"},
           {"$match":{"User_WebSite.User_WebSite_GalleryImage._GalleryId":{$in:[{param3}]}}},
           {"$sort":{"User_WebSite.User_WebSite_GalleryImage.data_insert":-1}},
           {"$limit":{param2}},
                   {
              "$project":{
                           "idImage":"$User_WebSite.User_WebSite_GalleryImage._ImageId",
                           "GalleryId":"$User_WebSite.User_WebSite_GalleryImage._GalleryId",
                           "IdAuthor":"$_id",
                           "NicknameAuthor":"$nickname"
                          "_id":0
                   }
               }
        
        ]