MongoDB text search filter by multiple fields

Question

I have the following document structure.

{
   content: 'cat dog bird',
   uid: <another_unique_id>
   cid: <another_unique_id>
}

I am trying to search this collection and want to filter results by uid and/or cid. Some queries that I want to run:

1) db.mycollection.find({uid: '1', cid: '2', $text: {$search: 'cat'}});         
2) db.mycollection.find({cid: '2', $text: {$search: 'cat'}});
3) db.mycollection.find({uid: '1', $text: {$search: 'cat'}});
4) db.mycollection.find({$text: {$search: 'cat'}});
//etc...

I tried to create a compound index like this

db.mycollection.ensureIndex({uid: 1, cid: 1, content: 'text'});

But it only works for query #1, if I don't supply one of the fields I get the below error.

planner returned error: failed to use text index to satisfy $text query 
(if text index is compound, are equality predicates given for all prefix fields?)

Other things I tried:

1. Creating non-compound indices on uid/cid = results in alot of documents being scanned

2. Moving the uid cid indices after the text index ie

   db.mycollection.ensureIndex({content: 'text', uid: 1, cid: 1});

   Same as #1 uid and cid index not used.

Info about what I am trying: http://docs.mongodb.org/manual/tutorial/limit-number-of-items-scanned-for-text-search/

Am I missing something or is this not possible with MongoDB using an index?

Answer 1

Not only is the expected behavior completely documented but I also find your assertion to be false. On a standard sample that could meet the conditions you specify, the results will be as shown. But first the documentation reference:

• If the compound text index includes keys preceding the text index key, to perform a $text search, the query predicate must include equality match conditions on the preceding keys.

Then the explain output of of valid query:

{
    "queryPlanner" : {
       "plannerVersion" : 1,
       "namespace" : "test.mycollection",
       "indexFilterSet" : false,
       "parsedQuery" : {
           "$and" : [
               {
                   "cid" : {
                       "$eq" : 2
                   }
               },
               {
                   "uid" : {
                       "$eq" : 1
                   }
               },
               {
                   "$text" : {
                       "$search" : "cat",
                       "$language" : ""
                   }
               }
          ]
      },
      "winningPlan" : {
          "stage" : "TEXT",
          "indexPrefix" : {
               "uid" : 1,
               "cid" : 2
          },
          "indexName" : "uid_1_cid_1_content_text",
          "parsedTextQuery" : {

          }
      },
      "rejectedPlans" : [ ]
  },
  "serverInfo" : {
      "host" : "trashbox",
       "port" : 27017,
       "version" : "3.0.0",
       "gitVersion" : "a841fd6394365954886924a35076691b4d149168"
  },
  "ok" : 1
}

So if you want to issue queries that have a different pattern the the "compound key" you have actually created and that meets the rules that are clearly specified then you possibly also should have payed attention to the main point:

• A collection can have at most one text index.

So in "any form" compound or other, if you are seeking more than one definition of a MongoDB text index then you cannot do that. The same applies to "geospatial" indexes, as well as the general consideration that outside of an $or expression, or a .sort() the query engine can only select one index at a time.

Modern versions should report the very specific line along with the error:

(if text index is compound, are equality predicates given for all prefix fields?)

So "all" of the fields are required and they "must be" an exact match without using inequality operators.

If you are not going to "always" use the other fields as part of your query with "exact match" conditions then you cannot form a compound index along with a text search.