CODEWITHSUNDEEP
mongodbmongodb-query
user3843276
user3843276

Reputation: 133

mongo query for objects with missing key

I have a Mongo db with data stored in this format:

{ "_id" : ObjectId("53e27f602041f3c6fe5373a8"), 
  "miles" : 112650, 
  "history" : [ { "date" : ISODate("2014-05-26T00:00:00Z"), "price" : 8995 }, 
                { "date" : ISODate("2014-06-01T00:00:00Z"), "price" : 8995 } ] }

Objects in the db can have any number "date" and "price data points stored in the history array. However, some of the objects in the db will have multiple history data points but the last "date" key/value pair is not matched with a "price" key/value pair, like this:

{ "_id" : ObjectId("53e27f602041f3c6fe5373a8"), 
  "miles" : 112650, 
  "history" : [ { "date" : ISODate("2014-05-26T00:00:00Z"), "price" : 8995 }, 
                { "date" : ISODate("2014-06-01T00:00:00Z")} ] }

I need to query the db to find 1) all objects where every entry in the history array has a date and a price and 2) all objects where the history array has a "date" entry that is not matched with a "price" entry. Thanks for your help!

Upvotes: 4

Views: 5098

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151132

Answering "2" first for reasons that will become clear, what you are basically asking is to find the documents whose array element does contain a "date" value but not a "price". Since there are two conditions for the array element match you want $elemMatch. And to test for the presence of the field you want $exists like so:

db.collection.find({
  "history": {
    "$elemMatch": {
      "date": { "$exists": true },
      "price": { "$exists": false }
    }
  }
})

That returns your second sample since one of the array entries does not have "price".

In order to answer "1", this is basically just the reverse of the logic above, so all you introduce here is the $not operator:

db.collection.find({
  "history": {
    "$not": { 
      "$elemMatch": {
        "date": { "$exists": true },
        "price": { "$exists": false }
      }
    }
  }
})

That produces the first document in the sample as in this case the "price" exists in all elements and therefore the asserted condition would be false but this is turned around by using $not.

Upvotes: 5

Related Questions