How do I query two fields in an embedded array?

Question

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?

Abhishek Kumar · Accepted Answer · 2013-05-07 04:55:53Z

4

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

edited May 7, 2013 at 4:55

answered May 6, 2013 at 11:49

Abhishek Kumar

3,3662 gold badges20 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

luttkens Over a year ago

Sorry, did not work. As I understand it $elemMatch is for projektion. That is, it only affects what fields in the document to return and not if the document should be returned or not. Looking in the documentation it is only used in the second argument of the find() function.

JohnnyHK Over a year ago

@luttkens $elemMatch is also a query operator. Docs here. As you wrote the query it's working fine, I think you've got your $gte and $lte operators swapped to do what you're actually looking for.

Abhishek Kumar Over a year ago

@luttkens : $elemMatch will work. I have modified my earlier comment, please check

Drew Over a year ago

@Abhishek if you use it and say from 1 to 1 nothing returns but it is within a document. well actually the question is confusing cause 1) it states "within the periods" but 2) it says matches (i guess exactly matches from and to.

Abhishek Kumar Over a year ago

If I understood correctly, you are talking about : { from : {$gte : 1}, to : {$lte : 1 }} For the above query, I can't see any document that is matching the criteria. Please explain, if I missed something

|

Collectives™ on Stack Overflow

How do I query two fields in an embedded array?

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related