can you index a field to find objects that don't have a value in an array?

[Ian Clarke]

I know that Multikeys allow you to efficient find objects that have an array as a field, where a particular value is present in that array.

For example, you could store an object:

    {

      "ar":["book","cat"]

    }

And then, provided the "ar" field is indexed, you could say:

    db.blah.find({"ar":"cat"})

And it will efficiently find the above object, and any others in which "cat" is present in the array.

However, would something like this work:

    db.blah.find({"ar":{$not : "cat"}})

Here I'd like to find all objects where the "ar" array does not contain a "cat".  Would this query work, and if it works, would it be efficient?  (ie. would it use the index on the "ar" field?)

Any advice would be greatly appreciated,

Ian.

[Ian Clarke]

I should just add that in my example, please assume that very few objects would not have a "cat" in the "ar" array, so the find() would not return many objects.

Ian.

[Scott Hernandez]

Take a look at the query operators here:
http://www.mongodb.org/display/DOCS/Advanced%20Queries#AdvancedQueries-%24ne

You most likely want $nin or $ne.

> --
> You received this message because you are subscribed to the Google Groups
> "mongodb-user" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/mongodb-user/-/kfCfrlY8z58J.
>
> To post to this group, send email to mongod...@googlegroups.com.
> To unsubscribe from this group, send email to
> mongodb-user...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/mongodb-user?hl=en.

[Ian Clarke]

Ok, but if I use $nin, will it take advantage of an index on that field?

Ian.

--
Ian Clarke

Blog: http://blog.locut.us/

[Scott Hernandez]

Indexes are very useful for finding thing which match, not which
don't. It can use the index but how efficient that is is another
question.

Do an explain() and see the differences for the $ne/$nin and forcing
the index use using hint().

Are you using any other criteria other than that one in your query?

[Ian Clarke]

Scott,

That will be the primary criteria with which to distinguish between records to retrieve.

Unfortunately I can't really "suck it and see" because we're still at the design stage, and don't currently have a database containing representative data.

I was hoping someone could clarify whether the indexing mechanism was effective when identifying records by the non-membership of an item in an array.

Ian.

[Scott Hernandez]

On Tue, Feb 21, 2012 at 11:27 PM, Ian Clarke <ian.c...@gmail.com> wrote:
> Scott,
>
> That will be the primary criteria with which to distinguish between records
> to retrieve.
>
> Unfortunately I can't really "suck it and see" because we're still at the
> design stage, and don't currently have a database containing representative
> data.
>
> I was hoping someone could clarify whether the indexing mechanism was
> effective when identifying records by the non-membership of an item in an
> array.

As I said, "Indexes are very useful for finding thing which match, not
which don't."

Systems like oracle simply table scan and skip indexes all together
for queries like this.