How to query date range in mongo, where field itself is a date

Question

I have a JSON document stored in mongodb where a sub-document is date-time-stamp. I need to query and filter sub-documents between a date range.

I am using mongo shell to play around with the query.

{
    "_id": ObjectID("5d9bf09c242af456ff5dd149"),
    "configId": "c2",
    "name": "ajit test",
    "description": "this is test desc",
    "confidence": 0,
    "report": {
        "2019-10-05T02:12:44Z": [
            {
                "VariantId": "1",
                "bestProbability": "3.2",
                "hdi": {
                    "low": "2.1",
                    "high": "4.0"
                }
            },
            {
                "VariantId": "2",
                "bestProbability": "3.2",
                "hdi": {
                    "low": "4.5",
                    "high": "4.7"
                }
            }
        ],
        "2019-10-06T02:12:44Z": [
            {
                "VariantId": "1",
                "bestProbability": "3.2",
                "hdi": {
                    "low": "2.1",
                    "high": "4.0"
                }
            },
            {
                "VariantId": "2",
                "bestProbability": "3.2",
                "hdi": {
                    "low": "4.5",
                    "high": "4.7"
                }
            }
        ],
        "2019-10-08T02:12:44Z": [
            {
                "VariantId": "1",
                "bestProbability": "3.2",
                "hdi": {
                    "low": "3.5",
                    "high": "6.7"
                }
            },
            {
                "VariantId": "2",
                "bestProbability": "3.2",
                "hdi": {
                    "low": "3.5",
                    "high": "6.7"
                }
            }
        ]
    },
}

I looking for a query that must return a sub-set of embedded documents between dates 2019-10-06T02:12:44Z and 2019-10-08T02:12:44Z only.

Answer 1

We need to convert the sub-document report into an array of key-value pairs where the key would represent the date. Later, that array has to be filtered and converted back to an object again.

The following query can get us the expected output:

db.collection.aggregate([
    {
        $addFields:{
            "report":{
                $objectToArray:"$report"
            }
        }
    },
    {
        $addFields:{
            "report":{
                $filter:{
                    "input":"$report",
                    "as":"doc",
                    "cond":{
                        $and:[
                            {
                                $gte:["$$doc.k","2019-10-06T02:12:44Z"]
                            },
                            {
                                $lte:["$$doc.k","2019-10-08T02:12:44Z"]
                            }
                        ]
                    }
                }
            }
        }
    },
    {
        $addFields:{
            "report":{
                $arrayToObject:"$report"
            }
        }
    }
]).pretty()

Data set:

{
    "_id" : ObjectId("5d9c15312cb9ef5d628ea95d"),
    "configId" : "c2",
    "name" : "ajit test",
    "description" : "this is test desc",
    "confidence" : 0,
    "report" : {
        "2019-10-05T02:12:44Z" : [
            {
                "VariantId" : "1",
                "bestProbability" : "3.2",
                "hdi" : {
                    "low" : "2.1",
                    "high" : "4.0"
                }
            },
            {
                "VariantId" : "2",
                "bestProbability" : "3.2",
                "hdi" : {
                    "low" : "4.5",
                    "high" : "4.7"
                }
            }
        ],
        "2019-10-06T02:12:44Z" : [
            {
                "VariantId" : "1",
                "bestProbability" : "3.2",
                "hdi" : {
                    "low" : "2.1",
                    "high" : "4.0"
                }
            },
            {
                "VariantId" : "2",
                "bestProbability" : "3.2",
                "hdi" : {
                    "low" : "4.5",
                    "high" : "4.7"
                }
            }
        ],
        "2019-10-08T02:12:44Z" : [
            {
                "VariantId" : "1",
                "bestProbability" : "3.2",
                "hdi" : {
                    "low" : "3.5",
                    "high" : "6.7"
                }
            },
            {
                "VariantId" : "2",
                "bestProbability" : "3.2",
                "hdi" : {
                    "low" : "3.5",
                    "high" : "6.7"
                }
            }
        ]
    }
}

Output:

{
    "_id" : ObjectId("5d9c15312cb9ef5d628ea95d"),
    "configId" : "c2",
    "name" : "ajit test",
    "description" : "this is test desc",
    "confidence" : 0,
    "report" : {
        "2019-10-06T02:12:44Z" : [
            {
                "VariantId" : "1",
                "bestProbability" : "3.2",
                "hdi" : {
                    "low" : "2.1",
                    "high" : "4.0"
                }
            },
            {
                "VariantId" : "2",
                "bestProbability" : "3.2",
                "hdi" : {
                    "low" : "4.5",
                    "high" : "4.7"
                }
            }
        ],
        "2019-10-08T02:12:44Z" : [
            {
                "VariantId" : "1",
                "bestProbability" : "3.2",
                "hdi" : {
                    "low" : "3.5",
                    "high" : "6.7"
                }
            },
            {
                "VariantId" : "2",
                "bestProbability" : "3.2",
                "hdi" : {
                    "low" : "3.5",
                    "high" : "6.7"
                }
            }
        ]
    }
}