Get objects from an array along with other fields in same level, based on some condition, using mongodb aggregate

Question

I have following data. I want to get objects from od array based on some condition. Along with that I want to get em and name field as well.

I am not very much familiar with aggregate of mongodb. So I need help to solve my problem.

{
_id : 1,
em : '[email protected]',
name : 'NewName',
od : 
[
    {
        "oid" : ObjectId("1234"),
        "ca" : ISODate("2016-05-05T13:20:10.718Z")
    },
    {
        "oid" : ObjectId("2345"),
        "ca" : ISODate("2016-05-11T13:20:10.718Z")
    },
    {
        "oid" : ObjectId("57766"),
        "ca" : ISODate("2016-05-13T13:20:10.718Z")
    }
]       
},
{
_id : 2,
em : '[email protected]',
name : 'NewName2',
od : 
[
    {
        "oid" : ObjectId("1234"),
        "ca" : ISODate("2016-05-11T13:20:10.718Z")
    },
    {
        "oid" : ObjectId("2345"),
        "ca" : ISODate("2016-05-12T13:20:10.718Z")
    },
    {
        "oid" : ObjectId("57766"),
        "ca" : ISODate("2016-05-05T13:20:10.718Z")
    }
]       
}

I have tried using $match, $project and $unwind of aggregate to get the desired result. My query is as given below : -

db.collection.aggregate([
{
    $match : {
                    "od.ca" : {
                        '$gte': '10/05/2016',
                        '$lte': '15/05/2016'
                    }
                }
},
{
    $project:{
                    _id: '$_id',
                    em: 1,
                    name : 1,
                    od : 1
                }
},
{
    $unwind : "$od"
}, 
{
    $match : {
                    "od.ca" : {
                        '$gte': '10/05/2016',
                        '$lte': '15/05/2016'
                    }
                }
}])

The result I got is with em and name and od array with one of the object from od, i.e. there are multiple records for same email id.

 {
_id : 1,
em : '[email protected]',
name : 'NewName',
od : 
[
{
    "oid" : ObjectId("57766"),
    "ca" : ISODate("2016-05-13T13:20:10.718Z")
}
]       
}
{
_id : 1,
em : '[email protected]',
name : 'NewName',
od : 
[
{
    "oid" : ObjectId("2345"),
    "ca" : ISODate("2016-05-11T13:20:10.718Z")
}
]       
}

But What I want is return result will be for each email id, inside od array all the objects matching the condition. One sample out put that I want is :-

 {
_id : 1,
em : '[email protected]',
name : 'NewName',
od : 
[
    {
        "oid" : ObjectId("2345"),
        "ca" : ISODate("2016-05-11T13:20:10.718Z")
    },
    {
        "oid" : ObjectId("57766"),
        "ca" : ISODate("2016-05-13T13:20:10.718Z")
    }
]       
}

Any thing wrong I am doing in the query? If the query suppose to return like this, how I can get the result I want? Can someone tell me what should I try or what changes in the query can help me getting the result I want?

chridam · Accepted Answer · 2016-05-20 08:08:20Z

You don't necessarily need a cohort of those aggregation operators except when your MongoDB version is older than the 2.6.X releases. The $filter operator will do the job just fine.

Consider the following example where the $filter operator when applied in the $project pipeline stage will filter the od array to only include documents that have a ca date greater than or equal to '2016-05-10' and less than or equal to '2016-05-15':

var start = new Date('2016-05-10'),
    end = new Date('2016-05-15');

db.collection.aggregate([
    { 
        "$match": {
            "od.ca": { "$gte": start, "$lte": end }
        }
    },
    {
        "$project": {
            "em": 1,
            "name": 1,
            "od": {
                "$filter": {
                    "input": "$od",
                    "as": "o",
                    "cond": {  
                        "$and": [
                            { "$gte": [ "$$o.ca", start ] },
                            { "$lte": [ "$$o.ca", end ] }
                        ]
                    }
                }
            }
        }
    }
])

Bear in mind this operator is only available for MongoDB versions 3.2.X and newer.

Otherwise, for versions 2.6.X up to 3.0.X, you can combine the use of the $map and $setDifference operators to "filter" the documents in the ca array.

The $map operator basically maps some values evaluated by the $cond operator to a set of either false values or the documents which pass the given condition. The $setDifference operator then returnns the difference of the sets from the previous computation. Check how this pans out with the preceding example:

var start = new Date('2016-05-10'),
    end = new Date('2016-05-15');

db.collection.aggregate([
    { 
        "$match": {
            "od.ca": { "$gte": start, "$lte": end }
        }
    },
    {
        "$project": {
            "em": 1,
            "name": 1,
            "od": {
                "$setDifference": [
                    {
                        "$map": {
                            "input": "$od",
                            "as": "o",
                            "in": {
                                "$cond": [
                                    {  
                                        "$and": [
                                            { "$gte": [ "$$o.ca", start ] },
                                            { "$lte": [ "$$o.ca", end ] }
                                        ]
                                    },
                                    "$$o",
                                    false
                                ]
                            }
                        }
                    },
                    [false]
                ]
            }
        }
    }
])

Fo versions 2.4.X and older, you may have to use the concotion of $match, $unwind and $group operators to achieve the same where the above operators do not exist.

The preceding example demonstrates this, which is what you were attempting but just left short of a $group pipeline step to group all the flattened documents into the original document schema, albeit minus the filtered array elements:

db.collection.aggregate([
    { 
        "$match": {
            "od.ca": { "$gte": start, "$lte": end }
        }
    },  
    { "$unwind": "$od" },
    { 
        "$match": {
            "od.ca": { "$gte": start, "$lte": end }
        }
    },
    {
        "$group": {
            "$_id": "$_id",
            "em": { "$first": "$em" },
            "name": { "$first": "$name" },
            "od": { "$push": "$od" }
        }
    }
])

I am using 3.0.0 of mongodb. I thought of using filter initially but since it can be used with 3.0.0. Will try the solution you have given for 2.5.X to 3.0.X.
that gave me the desired result, the way I want it. @chridam
My bad, it should be 2.6.X and not 2.5.X as stated initially. Also, $filter is new in version 3.2 so it's not available for 3.0, as a point of correction.

Collectives™ on Stack Overflow

Get objects from an array along with other fields in same level, based on some condition, using mongodb aggregate

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related