MongoDB aggregate query with date's month within subdocument

Question

here is my collection records

[
    {
        "username" : "av4617",
        "subscriptions" : [ 
            {
                "sub_id" : "5a56fd399dd78e33948c9b8e",
                "activation_date" : ISODate("2018-01-01T00:00:00.000Z")
            }, 
            {
                "sub_id" : "5a56fd399dd78e33948c9b8e",
                "activation_date" : ISODate("2018-02-01T00:00:00.000Z")
            }, 
            {
                "sub_id" : "5a56fd399dd78e33948c9b8e",
                "activation_date" : ISODate("2018-01-01T00:00:00.000Z")
            }
        ]
    },
    {
        "username" : "av4611",
        "subscriptions" : [ 
            {
                "sub_id" : "5a56fd399dd78e33948c9b8e",
                "activation_date" : ISODate("2018-01-01T00:00:00.000Z")
            }
        ]
    }
]

what i want is records having user's subscription details within range of two dates..i have tried

db.gyms.aggregate([
 {'$unwind': '$subscriptions'},   
   { "$match": { "subscriptions.activation_date": { "$exists": true } } },    
    {        
     "$redact": {      
        "$cond": [           
             { "$eq": [{ "$month": "$subscriptions.activation_date" }, 1] }, "$$KEEP","$$PRUNE"           
                 ]} } 
        ]).pretty()

But it returns

{
        "username" : "av4617",
        "subscriptions" : {
                "sub_id" : "5a56fd399dd78e33948c9b8e",
                "activation_date" : ISODate("2018-01-01T00:00:00Z")
        }
}
{
        "username" : "av4617",
        "subscriptions" : {
                "sub_id" : "5a56fd399dd78e33948c9b8e",
                "activation_date" : ISODate("2018-01-01T00:00:00Z")
        }
}
{
        "username" : "av4611",
        "subscriptions" : {
                "sub_id" : "5a56fd399dd78e33948c9b8e",
                "activation_date" : ISODate("2018-01-01T00:00:00Z")
        }
}

when i tried to have $$DESCEND in place of $$KEEP it works on other data types..but in above query it gives me error due to date datatype..

assert: command failed: {
        "ok" : 0,
        "errmsg" : "can't convert from BSON type missing to Date",
        "code" : 16006,
        "codeName" : "Location16006"
}

What i want is subcontinent within one doc...

[{
            "username" : "av4617",
            "subscriptions" : {
                    "sub_id" : "5a56fd399dd78e33948c9b8e",
                    "activation_date" : ISODate("2018-01-01T00:00:00Z")
            },
            {
                    "sub_id" : "5a56fd399dd78e33948c9b8e",
                    "activation_date" : ISODate("2018-01-01T00:00:00Z")
            }
},
{
        "username" : "av4611",
        "subscriptions" : {
                "sub_id" : "5a56fd399dd78e33948c9b8e",
                "activation_date" : ISODate("2018-01-01T00:00:00Z")
        }
}]

Possible duplicate of [Retrieve only the queried element in an object array in MongoDB collection](https://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection) — s7vr, Jan 16 '18 at 10:46
Something like `db.gyms.aggregate([ { "$addFields": { "subscriptions": { "$filter": { "input": "$subscriptions", "as": "subscription", "cond": { "$eq": [{ "$month": "$subscription.activation_date" }, 1] } } } } } ])` — s7vr, Jan 16 '18 at 10:47
Sorry I missed the $. Try `db.gyms.aggregate([ { "$addFields": { "subscriptions": { "$filter": { "input": "$subscriptions", "as": "subscription", "cond": { "$eq": [{ "$month": "$$subscription.activation_date" }, 1] } } } } } ])` Are you sure you are getting the same error ? I get the expected output for the above data. Can I see your query in that case ? You can add null check but it is not needed. `{$cond:[{"$ifNull":["$$subscription.activation_date", false]}, {"$eq": [ { "$month": "$$subscription.activation_date" }, 1] }, false ] }` — s7vr, Jan 16 '18 at 11:07
Thank you @Veeram.. please answer below above query so that i can up vote you. — AlpeshVasani, Jan 16 '18 at 11:12

score 0 · Answer 1 · answered Jan 16 '18 at 11:12

Your problem is caused by documents that are lacking a proper date value in their "activation_date" field, something like that:

{
    "username" : "av4611",
    "subscriptions" : {
        "sub_id" : "5a56fd399dd78e33948c9b8e",
        "activation_date": null
    }
}

The $exists operator will return true for those as well so they won't be filtered out. You can check for the $type of the field like this instead:

{ "$match": { "subscriptions.activation_date": { $type: "date" } } }

Mind you, your query is not written in an overly efficient way so you might want to use @Veeram's suggestion above.

I have verified that all subscriptions.activation_date fields are in proper date format. — AlpeshVasani, Jan 16 '18 at 11:17

MongoDB aggregate query with date's month within subdocument

1 Answers1