CODEWITHSUNDEEP
mongodb
luttkens
luttkens

Reputation: 1264

How do I query two fields in an embedded array?

I have a document like the one below. The periods field defines periods the user had access to some data.

{
   "_id":ObjectId("51878ecc0e9528429ab6e7cf"),
   "name" : "Peter Flack",
   "periods":[
      {
         "from":1,
         "to":2
      },
      {
         "from":4,
         "to":6
      },
      {
         "from":10,
         "to":12
      }
   ]
}

Now I want to determine if an arbitrary period is within any of the periods in the period field. I have tried the following:

db.subs.find({"periods.from" : {$gte : 1}, "periods.to" : {$lte : 12}, "periods.from" : {$lte : 12}, "periods.to" : {$gte : 1}})

This query returns the document, since there is one element in the array with from >= 1 and antoher element with to <= 12.

But, I want to get back the document only if both from and to in the same element match the criteria.

How would I write the query to achieve this?

Upvotes: 1

Views: 151

Answers (1)

Abhishek Kumar
Abhishek Kumar

Reputation: 3356

You have to use $elemMatch : elemMatch match for the params in each element of the array.

Mongo > db.subs.find({periods : {$elemMatch : { from : {$gte : 1, $lte:12}, to : {$gte : 1, $lte : 12} }}}).pretty()
{
    "_id" : ObjectId("51878ecc0e9528429ab6e7cf"),
    "name" : "Peter Flack",
    "periods" : [
        {
            "from" : 1,
            "to" : 2
        },
        {
            "from" : 4,
            "to" : 6
        },
        {
            "from" : 10,
            "to" : 12
        }
    ]
}
Mongo > db.subs.find({periods : {$elemMatch : { from : {$gte : 2, $lte : 4}, to : {$gte : 10, $lte : 12} }}}).pretty()
no result

Upvotes: 4

Related Questions