Differences in local MongoDb vs CosmosDb query execution

Question

Please refer to the document structure below (Under "collection of documents" heading). I want to write a query which would give me documents based on demand timings and filter out resources which already have demands for the given timings and provide me the available resources for the given demand (only based on timings). Every resource has a demand array with demand timings.

Basically, we need documents which satisfies the below condition for every sub-document in the demand array.

startTime: {$gte: demand.EndTime}, endTime: {$lte: demand.StartTime}

Hence, unable to find any suitable operator I decided to invert the results for the "non-matching" documents.

Just to make things simple I have created the below demand requirements and resource documents and the outcomes for MongoDb and CosmosDb.

Demand Query:

db.getCollection('try').
find(
    {"demands": 
        { $not: 
            {$elemMatch:
                { 
                    startTime: {$lt: 8}, endTime: {$gt: 6}  //startTime: {$lt: demand.endTime}, endTime: {$gt: demand.startTime}
                }
            }
        }
    }
)

Collection of Documents

Doc1:

{
    "_id" : 10439090,
    "demands" : [ 
        {
            "_id" : 1003,
            "name" : "Barclays Project",
            "startTime" : 10,
            "endTime" : 20
        }, 
        {
            "_id" : 1003,
            "name" : "Barclays Project",
            "startTime" : 30,
            "endTime" : 40
        }, 
        {
            "_id" : 1003,
            "name" : "Barclays Project",
            "startTime" : 5,
            "endTime" : 9
        }, 
        {
            "_id" : 1003,
            "name" : "Barclays Project",
            "startTime" : 80,
            "endTime" : 90
        }
    ]
}

Doc2:

{
    "_id" : 10439091,
    "demands" : [ 
        {
            "_id" : 1003,
            "name" : "Barclays Project",
            "startTime" : 111,
            "endTime" : 211
        }, 
        {
            "_id" : 1003,
            "name" : "Barclays Project",
            "startTime" : 50,
            "endTime" : 80
        }, 
        {
            "_id" : 1003,
            "name" : "Barclays Project",
            "startTime" : 20,
            "endTime" : 30
        }, 
        {
            "_id" : 1003,
            "name" : "Barclays Project",
            "startTime" : 80,
            "endTime" : 90
        }
    ]
}

Local MongoDb result: Only Doc2

CosmosDb result : Both Doc1 and Doc2

Why are the results different? Any Idea ?

What I can understand is that the query is not working at all for CosmosDB. Any alternate solutions would be very helpful.

Thanks

kevinadi kevinadi · Accepted Answer · 2018-05-30T00:41:35

CosmosDB is not MongoDB. They are two separate products created by two separate companies (MongoDB by MongoDB Inc., and CosmosDB by Microsoft).

Although CosmosDB attempts to provide a MongoDB-like query syntax, they are not the same and may produce different results, as evident in your experience.

I don't think there's anything wrong with your query if you get the correct data by querying a real MongoDB server.

If you find that CosmosDB give the wrong result, then the best course of action is either:

Contact Microsoft support and enquire why they give a different result
Deploy your own MongoDB installation or use a hosted MongoDB e.g. Atlas

Differences in local MongoDb vs CosmosDb query execution

1 Answers