Improve querying fields exist in MongoDB

Dewfy picture Dewfy · Jan 25, 2012 · Viewed 7.6k times · Source

I'm in progress with estimation of MongoDB for our customers. Per requirements we need associate with some entity ent variable set of name-value pairs.

db.ent.insert({'a':5775, 'b':'b1'})
db.ent.insert({'c':'its a c', 'b':'b2'})
db.ent.insert({'a':7557, 'c':'its a c'})

After this I need intensively query ent for presence of fields:

db.ent.find({'a':{$exists:true}})
db.ent.find({'c':{$exists:false}})

Per MongoDB docs:

$exists is not very efficient even with an index, and esp. with {$exists:true} since it will effectively have to scan all indexed values.

Can experts there provide more efficient way (even with shift the paradigm) to deal fast with vary name-value pairs

Answer

Andrew Orsich picture Andrew Orsich · Jan 25, 2012

You can redesign your schema like this:

{
  pairs:[
  {k: "a", v: 5775},
  {k: "b", v: "b1"},
  ]
}

Then you indexing your key:

db.people.ensureIndex({"pairs.k" : 1})

After this you will able to search by exact match:

db.ent.find({'pairs.k':"a"})

In case you go with Sparse index and your current schema, proposed by @WesFreeman, you will need to create an index on each key you want to search. It can affect write performance or will be not acceptable if your keys are not static.