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.

can you post your sample output

Nikhil Savaliya
– Nikhil Savaliya

2019-10-08 03:38:14 +00:00
Commented Oct 8, 2019 at 3:38 — Nikhil Savaliya
– Nikhil Savaliya, Commented Oct 8, 2019 at 3:38

Himanshu Sharma · Accepted Answer · 2019-10-08 04:53:02Z

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"
                }
            }
        ]
    }
}

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related