MongoDB Array Query

Question

I'm new with Mongo DB and I'm trying to figure out how to do some more complex queries. I have a document that has a nested array of DateTime.

Here is my data:

{ "_id" : ObjectId("537d0b8c2c6b912520798b76"), "FirstName" : "Mary", "LastName" : "Johnson", "Age" : 27, "Phone" : "555 555-1212", "Dates" : [ISODate("2014-05-21T21:34:16.378Z"), ISODate("1987-01-05T08:00:00Z")] }

{ "_id" : ObjectId("537e4a7e2c6b91371c684a34"), "FirstName" : "Walter", "LastName" : "White", "Age" : 52, "Phone" : "800 123-4567", "Dates" : [ISODate("1967-12-25T08:00:00Z"), ISODate("2014-12-25T08:00:00Z")] }

What I want to do is return the document where the Dates array contains a value between a range. In my test case the range is 1/1/1987 and 1/10/1987 so I expect to get back the first document listed above (Mary Johnson) because 1/5/1987 is in that Dates array and falls between 1/1/1987 and 1/10/1987.

I'd like to be able to do this with both the MongoDB command line utility and the C# driver.

With the C# driver I tried the following LINQ query (based on an example in the MongoDB documentation):

DateTime beginRange = new DateTime(1987, 1, 1);
DateTime endRange = new DateTime(1987, 1, 10);

var result = from p in people.AsQueryable<Person>()
    where p.Dates.Any(date => date > beginRange && date < endRange)
    select p;

The above code throws an exception from within the C# Driver code:

Any is only supported for items that serialize into documents. The current serializer is DateTimeSerializer and must implement IBsonDocumentSerializer for participation in Any queries.

When I try and query the MongoDB database directly I tried the following:

db.People.find( {Dates: { $gt: ISODate("1987-01-01"), $lt: ISODate("1987-01-10") } } )

This query results in both of the documents coming back instead of just the one that has 1/5/1987 in its Dates array.

EDIT:

I figured out a way to do if from the C# driver. It isn't as clean as I would like but it is doable.

I figured that since there was a way to get what I wanted directly from the command utility that there must be a way to do if from the C# driver as well by just executing the same query from the C# driver.

string command = "{Dates : { $elemMatch : { $gt: ISODate(\"" + beginRange.ToString("yyyy-MM-dd") + "\"), $lt: ISODate(\"" + endRange.ToString("yyyy-MM-dd") + "\") } } } ";
var bsonDoc = BsonSerializer.Deserialize<BsonDocument>(command);
var queryDoc = new QueryDocument(bsonDoc);
MongoCursor<Person> p = people.Find(queryDoc);

i3arnon · Accepted Answer · 2014-05-22 20:54:54Z

1

C# Driver

Just as the exception suggests, you can't do what you want to do using the C# driver as long as your array is of a primitive type (DateTime) and not a genuine document.

From the MongoDB Linq Any description:

This will only function when the elements of the enumerable are serialized as a document. There is a server bug preventing this from working against primitives.

I guess you can create a document wrapper around a DateTime value so you can still do that:

var result = people.AsQueryable<Person>().Where(
    person => person.Dates.Any(date => 
        date.Value > beginRange && date.Value < endRange));

.

public class DocumentWrapper<T>
{
    public ObjectId Id { get; private set; }
    public T Value { get; private set; }

    public DocumentWrapper(T value)
    {
        Id = ObjectId.GenerateNewId();
        Value = value;
    }
}

Native query

As to your query, it isn't actually the equivalent of the Linq query. That would be:

{ 
    Dates : 
    {
        $elemMatch :
        { 
            $gt: ISODate("1987-01-01"), 
            $lt: ISODate("1987-01-10") 
        } 
    }
}

4 Comments

James Over a year ago

Thanks. The Native query works as I expected. I will take a look into the DocumentWrapper. Also, is it possible to use the C# driver without using LINQ to get what I want without the DocumentWrapper using the Query class methods (Query.ElemMatch, Query.And, Query.GT, Query.LT) and Collection.Find()? I tried but I can't seem to get the query formatted correctly. Thanks again.

i3arnon Over a year ago

@Jim I highly doubt that. The issue seems to be in the server rather than the C# driver, so it shouldn't work no matter what. From the release notes: "Added support for the Any operator when the target is an enumerable of documents. This will generate an $elemMatch query. We do NOT support targets that are enumerables of primitives because the mongodb server does not support those. As soon as the server supports this, we will add this in as well."

James Over a year ago

@l3arnon, thanks for all the help. I was able to figure out a way to do it from the C# driver (see edit in original post). It isn't using LINQ or even the query methods but it gets the job done. I will look at the document wrapper idea next.

James Over a year ago

@l3arnon, just wanted to follow up one last time. I tried the suggestion you made to wrap the DateTime in a document wrapper and it works great. LINQ works, Query class methods work and query from the Mongo command line works (although I did have to wrap the $gt and $lt block in another Date: { } block. Thanks so much.

Collectives™ on Stack Overflow

MongoDB Array Query

1 Answer 1

C# Driver

Native query

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

C# Driver

Native query

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related