CODEWITHSUNDEEP
mongodbmongodb-.net-drivermongodb-query
pheobas
pheobas

Reputation: 304

How to do query on multiple nested data fields in MongoDB

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",
    }
    }]
}

Upvotes: 3

Views: 3100

Answers (2)

Asya Kamsky
Asya Kamsky

Reputation: 42352

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"}}
      }
  } 
)

Upvotes: 6

pheobas
pheobas

Reputation: 304

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'?

Upvotes: 0

Related Questions