How to do query on multiple nested data fields in MongoDB

Question

So, what I'm trying to do is query all documents that have a City of 'Paris' and a State of 'France'. I need to do some kind of join, but I haven't been able to figure out how to construct it.

I'm using the c# driver, but I'll gladly accept help using any method.

{
    "_id" : ObjectId("519b407f3c22a73a7c29269f"),
    "DocumentID" : "1",
    "Meta" : [{
        "Name" : "City",
        "Value" : "Paris",
    }, {
        "Name" : "State",
        "Value" : "France",
    }
    }]
}

{
    "_id" : ObjectId("519b407f3c22a73a7c29269g"),
    "DocumentID" : "2",
    "Meta" : [{
        "Name" : "City",
        "Value" : "Paris",
    }, {
        "Name" : "State",
        "Value" : "Texas",
    }
    }]
}

Asya Kamsky · Accepted Answer · 2013-05-21 14:22:28Z

The $elemMatch operator is used to indicate that all the conditions within it must be matched by the same array element. So (to switch to shell syntax) to match all documents which have meta city Paris you would do

db.collection.find( {Meta:{$elemMatch:{Name:"City",Value:"Paris"}}} )

This assures you won't match something which has Name: "somethingelse", Value: "Paris" somewhere in its array with a different array element matching the Name:"City".

Now, default combination for combining query conditions is "and" so you can continue adding attributes:

db.collection.find( {Meta: {
                       $elemMatch:{Name:"City",Value:"Paris"}, 
                       $elemMatch:{Name:"State",Value:"France"} 
      }
  } 
)

Now if you want to add another condition you keep adding it but if you want a NOT then you do it like this:

db.collection.find( {Meta: {
                       $elemMatch:{Name:"City",Value:"Paris"}, 
                       $elemMatch:{Name:"State",Value:"France"},
                       $not: {$elemMatch:{Name:"Arrondissement",Value:"Louvre"}}
      }
  } 
)

pheobas · Accepted Answer · 2013-05-21 13:26:03Z

0

I might be answering my own question here, but I'm new to MongoDB, so while this appears to give me the results I'm after, it might not be the optimum approach.

var result = collection.Find(
   Query.And(
   Query.ElemMatch("Meta", Query.EQ("Name", "City")),
   Query.ElemMatch("Meta", Query.EQ("Value", "Paris")),
   Query.ElemMatch("Meta", Query.EQ("Name", "State")),
   Query.ElemMatch("Meta", Query.EQ("Value", "France")))
   );

Which leads to a follow up - how would I get all of the documents whose 'City' is 'Paris' and 'State' is 'France' but whose 'Arrondissement' is not 'Louvre'?

edited May 21, 2013 at 13:26

answered May 21, 2013 at 13:15

pheobas

3141 gold badge5 silver badges16 bronze badges

1 Comment

Asya Kamsky Over a year ago

this is not quite correct - you want $elemMatch to include multiple conditions to indicate that they all must be true for array element to be considered a match.

Collectives™ on Stack Overflow

How to do query on multiple nested data fields in MongoDB

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related