MongoDB.Driver with Cosmos DB array searches

Question

I have an application that was using native mongoDb, we're now pointing it to an Azure Cosmos DB instance, but we're now not getting results when querying arrays.

For example, we have the following customer:

{            
    "email" : "[email protected]",            
    "data" : {                
        "customerGuid" : "a30b5d75ca6241dcbd0260b2516a2165",                                
        "addresses" : [ 
            {                        
                "firstName" : "firstname",
                "lastName" : "lastname",                        
                "postalCode" : "SY1 3VE",                        
            }
        ]
    }
}

And we're using the MongoDB.Driver (via AsQueryable and linq) to find all customers matching on an item in the addresses array

i.e.

var col = db.GetCollection<Customer>("Customer");
var custQuery = col.AsQueryable()
    .Where(c => c.Data.Addresses.Any(a => a.PostalCode == "SY1 3VE"));

But, I am not getting any matches. Digging in further, it seems to be generating a Mongo query that looks like:

{aggregate([{ "$match" : { "data.addresses.postalCode" : "SY1 3VE" } }])}

Which doesn't work for me when I try manually against the database either.

Am I doing something wrong? Or is the Cosmos Mongo Db implementation not fully compatible with the MongoDB.Driver yet?

The query looks good to me. Are you sure you are querying against the right database/collection ? — s7vr
I am pretty sure. If I run the same thing via roboMongo 0 results, edit it to be aggregate([{ "$match" : { "data.addresses" : {$elemMatch: {"postalCode":"SY1 3VE"}}} }]) It then works! — Vdex
Single query condition works alike with and without elemMatch operator. docs.mongodb.com/manual/reference/operator/query/elemMatch/… . Can you try in mongo shell ? — s7vr
This is azure-cosmos DB with a mongo driver, I do know that this works on native mongo, I have one running: Point it there, it works. Point it to azure-cosmos, 0 results for this query. It seems that cosmos db only supports non-array queries — Vdex
Okay, it simply appears that Cosmo via Mongo, is not ready for productions use. Found another issue, where it treats Aggregate(<condition1>,<condition2>) as ORs, but according to mongo docs it should act like an AND — Vdex

Tom Sun - MSFT Tom Sun - MSFT · Accepted Answer · 2017-06-21T06:14:12

Am I doing something wrong? Or is the Cosmos Mongo Db implementation not fully compatible with the MongoDB.Driver yet?

As you mentioned the Cosmos Mongo Db may have not implemented whole commands as native MongoDB. I tried the code you mentioned also get

there is no record returned

But we could use filter to do that, I tested with following code. It works correctly on my side.

 var collection = db.GetCollection<BsonDocument>("BsonDocument");
 var test = collection.Find(Builders <BsonDocument>.Filter.ElemMatch<BsonDocument>("data.addresses",
                    "{\"postalCode\":\"SY1 3VE\"}")).ToList();

Another option:

Based on my experience, it is not recommended, as it will query all of the documents to client.

var col = db.GetCollection<Customer>("Customer");
var custQuery = collection.AsQueryable().ToList().Where(c => c.Data.Addresses.Any(a => a.PostalCode == "SY1 3VE"));

MongoDB.Driver with Cosmos DB array searches

1 Answers