Implementing an index that would improve a query in mongoDB

Question

I'm currently struggling to implement an index to my query. Here is the original query:

*db.getCollection('products').aggregate([
    { $unwind: "$categories" },
    {  $unwind: "$categories"},
    {$group: {"_id": "$_id","title": {$first:"$title"},
            "asin":{$first:"$asin"},
            "categories": { $push: "$categories" }} },
    { $match: { "categories": { $in: ['T-Shirts']}} },
    { "$project":{ "_id": 0, "asin":1, "title":1  }  } ])*

This is my current code for my index:

*var cursor = 
db.products.explain("allPlansExecution").find(categories:{"T-Shirts"},{ categories:1, title:1, asin:1, _id:0,})
while (cursor.hasNext()) {
    print(cursor.next());
}*

When I run the index code I should get nReturned as 8 currently at 0.

Could someone please guide me how to do this? Or can someone tell me what they would add?

Could we see a sample document? Does `$categories` need to be unwound twice? — Joe, Mar 16 '20 at 23:41
Yes because “T-shirts” you cant find from just unwinding once” — StructureMan, Mar 17 '20 at 00:19
About [indexes](https://docs.mongodb.com/manual/indexes/index.html). Also, post a sample document, and tell what is it you are trying to achieve with the aggregation. — prasad_, Mar 17 '20 at 01:17
This is the first part of the question which is the code for finding t-shirts. List the product ID and title of all products in the “T-Shirts” category. No other product details are required. — StructureMan, Mar 17 '20 at 07:13
This is for index: Implement one index that would improve the querying of the database based on one or more of the queries — StructureMan, Mar 17 '20 at 07:15

score 0 · Answer 1 · answered Mar 17 '20 at 18:59

There is not a simple way to index the individual values in an array contained inside another array.

It this case, since the values in question are string, you could use a text index, such as:

db.products.createIndex({"$**":"text"})

You could then use the $text query operator to search the index for an exact match:

db.products.find({$text:{$search:"\"T-Shirts\""}},{_id:0, asin:1, title:1})

Implementing an index that would improve a query in mongoDB

1 Answers1