Distinct on array of embedded documents doesn't use index?

[Adam Dohnal]

Hello,

let's say I have collection of documents like this, so there is array field with embedded document

{
  _id: 1,
  item: "abc",
  stock: [
    { size: "S", color: "red", quantity: 25 },
    { size: "S", color: "blue", quantity: 10 },
    { size: "M", color: "blue", quantity: 50 }
  ]
}

I have created a multikey index on "stock.size", but when I run db.items.distinct("stock.size") the whole collections is scanned. This is the result of db.runCommand({distinct: "items", key: "stock.size"})

"stats" : {

               "n" : 1730969,

               "nscanned" : 0,

               "nscannedObjects" : 1730969,

               "timems" : 3178,

               "planSummary" : "COLLSCAN"

       }

What can I do in order to use that index?

[Rhys Campbell]

Can you show us the output of...

db.items.getIndexes();

and 

db.items.distinct("stock.size");

[Rhys Campbell]

Rather...

db.items.distinct("stock.size").explain();

[Adam Dohnal]

db.items.getIndexes()

[

{

             "v" : 1,

              "key" : {

                     "_id" : 1

             },

            "name" : "_id_",

              "ns" : "local.items"

  },

    {

             "v" : 1,

              "key" : {

                     "stock.size" : 1

              },

            "name" : "stock.size_1",

              "ns" : "local.items"

  }

]

db.items.distinct("stock.size")

[ "M", "S" ]

db.items.distinct("stock.size").explain();

2016-04-25T10:19:42.731+0200 E QUERY    TypeError: Object M,S has no method 'explain'

   at (shell):1:33

[Rhys Campbell]

OK, so no explain for this method then!

This looks like the reason...

any of the indexed fields in any of the documents in the collection includes an array. If an indexed field is an array, the index becomes a multi-key index and cannot support a covered query.

https://docs.mongodb.org/manual/core/query-optimization/#covered-queries

Have you tried the aggregate framework? Perhaps you'll have between luck with that to avoid this. $match and then grouping to a distinct list will probably use indexes.

[Adam Dohnal]

I have tried aggregation framework, but when I want distinct values across whole collection, I can't use $match stage and $group stage seems that doesn't use indexes as well.

db.items.aggregate([{$unwind: "$stock"}, {$group: {_id: "$stock.size"}}], {explain: true})
{
  "stages" : [
    {
      "$cursor" : {
        "query" : {
       
        },
        "fields" : {
          "stock" : 1,
          "_id" : 0
        },
        "queryPlanner" : {
          "plannerVersion" : 1,
          "namespace" : "local.items",
          "indexFilterSet" : false,
          "parsedQuery" : {
            "$and" : [ ]
          },
          "winningPlan" : {
            "stage" : "COLLSCAN",
            "filter" : {
              "$and" : [ ]
            },
            "direction" : "forward"
          },
          "rejectedPlans" : [ ]
        }
      }
    },
    {
      "$unwind" : "$stock"
    },
    {
      "$group" : {
        "_id" : "$stock.size"
    }
  }
],
"ok" : 1
}

[Asya Kamsky]

You can vote up this ticket: https://jira.mongodb.org/browse/SERVER-13298

Though this is also related to https://jira.mongodb.org/browse/SERVER-2104

> --
> You received this message because you are subscribed to the Google Groups
> "mongodb-user"
> group.
>
> For other MongoDB technical support options, see:
> https://docs.mongodb.org/manual/support/
> ---
> You received this message because you are subscribed to the Google Groups
> "mongodb-user" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to mongodb-user...@googlegroups.com.
> To post to this group, send email to mongod...@googlegroups.com.
> Visit this group at https://groups.google.com/group/mongodb-user.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/mongodb-user/33b743ef-47eb-415f-958a-01ec9a4d3d02%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.



--
Asya Kamsky
Lead Product Manager
MongoDB
Download MongoDB - mongodb.org/downloads
Free MongoDB Monitoring - cloud.mongodb.com
Free Online Education - university.mongodb.com
Get Involved - mongodb.org/community
We're Hiring! - https://www.mongodb.com/careers