How can I 'AND' multiple $elemMatch clauses with C# and MongoDB?

Question

I am using the 10Gen sanctioned c# driver for mongoDB for a c# application and for data browsing I am using Mongovue.

Here are two sample document schemas:

{
  "_id": {
    "$oid": "4ded270ab29e220de8935c7b"
  },
  "Relationships": [
    {
      "RelationshipType": "Person",
      "Attributes": {        
        "FirstName": "Travis",
        "LastName": "Stafford"
      }
    },
    {
      "RelationshipType": "Student",
      "Attributes": {
        "GradMonth": "",
        "GradYear": "",
        "Institution": "Test1",
      }
    },
    {
      "RelationshipType": "Staff",
      "Attributes": {
        "Department": "LIS",
        "OfficeNumber": "12",
        "Institution": "Test2",
      }
    }
  ]
},    

{
  "_id": {
    "$oid": "747ecc1dc1a79abf6f37fe8a"
  },
  "Relationships": [
    {
      "RelationshipType": "Person",
      "Attributes": {        
        "FirstName": "John",
        "LastName": "Doe"
      }
    },
    {
      "RelationshipType": "Staff",
      "Attributes": {
        "Department": "Dining",
        "OfficeNumber": "1",
        "Institution": "Test2",
      }
    }
  ]
}

I need a query that ensures that both $elemMatch criteria are met so that I can match the first document, but not the second. The following query works in Mongovue.

{
  'Relationships': { $all: [
        {$elemMatch: {'RelationshipType':'Student', 'Attributes.Institution': 'Test1'}},
        {$elemMatch: {'RelationshipType':'Staff', 'Attributes.Institution': 'Test2'}}
     ]}
}

How can I do the same query in my c# code?

score 6 · Answer 1 · edited Dec 30 '13 at 18:52

6

There is no way to build above query using c# driver (at least in version 1.0).

But you can build another, more clear query, that will return same result:

{ "Relationships" : 
          { "$elemMatch" : 
              { "RelationshipType" : "Test", 
                "Attributes.Institution" : { "$all" : ["Location1", "Location2"] } 
              } 
          } 
}

And the same query from c#:

Query.ElemMatch("Relationships", 
    Query.And(
        Query.EQ("RelationshipType", "Test"),
            Query.All("Attributes.Institution", "Location1", "Location2")));

edited Dec 30 '13 at 18:52

i3arnon

113,022
33
324
344

answered Jun 07 '11 at 20:42

Andrew Orsich

52,935
16
139
134

Thank you for the quick response. Unfortunately the query you have suggested does not return the same results. My document design is most likely the issue here as I just starting out with MongoDB. Attributes is a sub document and Attributes.Institution is not array. Is there anyway to send a the query string directly to the Find function? – Travis Stafford Jun 07 '11 at 20:54
@TravisStafford: Yeah you can build query from string. Take a look into this my answer: http://stackoverflow.com/questions/6120629/can-i-do-a-text-query-with-the-mongodb-c-driver/6123792#6123792. I tried, but without success ;(. – Andrew Orsich Jun 08 '11 at 09:32
Thank you this was a huge help. Reading the post you point me to in your last comment I was able to build a quick class to build the query string I needed. It's not perfect but it will get the job done. – Travis Stafford Jun 12 '11 at 23:16
I have posted my resulst as requested. Would be nice to get some input on the approach I took. – Travis Stafford Jun 23 '11 at 13:09

TimDog · Answer 2 · 2011-12-20T15:15:05.883

A simple solution is to string together multiple IMongoQuery(s) and then join them with a Query.And at the end:

List<IMongoQuery> build = new List<IMongoQuery>();
build.Add(Query.ElemMatch("Relationships", Query.EQ("RelationshipType", "Person")));

var searchQuery =  String.Format("/.*{0}.*/", "sta");
build.Add(Query.ElemMatch("Relationships", Query.Or(Query.EQ("Attributes.FirstName", new BsonRegularExpression(searchQuery)), Query.EQ("Attributes.LastName", new BsonRegularExpression(searchQuery)))));

var _main = Query.And(build.ToArray());

var DB = MongoDatabase.Create("UrlToMongoDB");
DB.GetCollection<ObjectToQuery>("nameOfCollectionInMongoDB").FindAs<ObjectToQuery>(_main).ToList();

`

score 1 · Accepted Answer · edited Jun 24 '11 at 05:24

I have solved the immediate issue by contruction a set of class that allowed for the generation of the following query:

{  'Relationships': 
    { 
        $all: [
         {$elemMatch: {'RelationshipType':'Student', 'Attributes.Institution': 'Test1'}},            
         {$elemMatch: {'RelationshipType':'Staff',   'Attributes.Institution': 'Test2'}}     
        ]
    }
}

Here are the class definitions:

class MongoQueryAll
    {
        public string Name { get; set; }
        public List<MongoQueryElement> QueryElements { get; set; }

        public MongoQueryAll(string name)
       {
           Name = name;
           QueryElements = new List<MongoQueryElement>();
       }

       public override string ToString()
       {
           string qelems = "";
           foreach (var qe in QueryElements)
              qelems = qelems + qe + ",";

           string query = String.Format(@"{{ ""{0}"" : {{ $all : [ {1} ] }} }}", this.Name, qelems); 

           return query;
       }
  }

class MongoQueryElement
{
    public List<MongoQueryPredicate> QueryPredicates { get; set; }

    public MongoQueryElement()
    {
        QueryPredicates = new List<MongoQueryPredicate>();
    }

    public override string ToString()
    {
        string predicates = "";
        foreach (var qp in QueryPredicates)
        {
            predicates = predicates + qp.ToString() + ",";
        }

        return String.Format(@"{{ ""$elemMatch"" : {{ {0} }} }}", predicates);
    }
}

class MongoQueryPredicate
{        
    public string Name { get; set; }
    public object Value { get; set; }

    public MongoQueryPredicate(string name, object value)
    {
        Name = name;
        Value = value;
    }

    public override string ToString()
    {
        if (this.Value is int)
            return String.Format(@" ""{0}"" : {1} ", this.Name, this.Value);

        return String.Format(@" ""{0}"" : ""{1}"" ", this.Name, this.Value);
    }
}

Helper Search Class:

public class IdentityAttributeSearch
{
    public string Name { get; set; }
    public object Datum { get; set; }
    public string RelationshipType { get; set; }
}

Example Usage:

 public List<IIdentity> FindIdentities(List<IdentityAttributeSearch> searchAttributes)
 {
        var server = MongoServer.Create("mongodb://localhost/");
        var db = server.GetDatabase("IdentityManager");
        var collection = db.GetCollection<MongoIdentity>("Identities");

        MongoQueryAll qAll = new MongoQueryAll("Relationships");

        foreach (var search in searchAttributes)
        {
            MongoQueryElement qE = new MongoQueryElement();
            qE.QueryPredicates.Add(new MongoQueryPredicate("RelationshipType", search.RelationshipType));
            qE.QueryPredicates.Add(new MongoQueryPredicate("Attributes." + search.Name, search.Datum));
            qAll.QueryElements.Add(qE);
        }

        BsonDocument doc = MongoDB.Bson.Serialization
                .BsonSerializer.Deserialize<BsonDocument>(qAll.ToString());

        var identities = collection.Find(new QueryComplete(doc)).ToList();

        return identities;
    }

I am sure there is a much better way but this worked for now and appears to be flexible enough for my needs. All suggestions are welcome.

This is probably a separate question but for some reason this search can take up to 24 seconds on a document set of 100,000. I have tried adding various indexes but to no avail; any pointers in this regards would be fabulous.

How can I 'AND' multiple $elemMatch clauses with C# and MongoDB?

3 Answers3

Linked