$match in date after $project and $subtract returns empty result in MongoDB

Question

The documents follow this structure:

{ 
    "_id" : ObjectId("5a01b474d88dc4001e684c97"), 
    "created_time" : ISODate("2017-11-07T11:26:12.563+0000"), 
    "posts" : [
        {
            "story" : "Test", 
            "created_time" : ISODate("2017-11-06T17:38:02.000+0000"), 
            "id" : "769055806629274_768721009996087", 
            "_id" : ObjectId("5a01b498d88dc4001e68553c")
        }, 
        {
            "story" : "Test", 
            "created_time" : ISODate("2017-11-05T12:00:00.000+0000"), 
            "id" : "1637086293239159_2011737915773993", 
            "_id" : ObjectId("5a01b498d88dc4001e68553d")
        }
        [...]
     ]
}

I want to filter the posts collection by created_time. Each post needs to have created_time greater than created_time of the document. In other words, I want to get posts only for the last month based on the document.

I'm trying this aggregation:

db.collection.aggregate([
{
    $project: {
        "past_month": {
            $subtract: ["$created_time", 2629746000] //a month
        },
        "created_time": "$created_time",
        "posts": "$posts"
    }
}, {
    $unwind: '$posts'
}, {
    $match: {
        "posts.created_time": {
            $gte: "$past_month"
        }
    }
}, {
    "$group": {
        "_id": "$_id",
        "posts": {
            "$push": "$posts"
        }
    }
}
])

But the result is always empty. If I change $gte: "$past_month" to $gte: ISODate("2017-10-08T00:57:06.563+0000") to test, the results is not empty.

we want last month posts, which would be matched by "created_time" in POSTS array. Right ? — cop, Jan 30 '18 at 14:06
You need a aggregation comparison operator to compare two document values in `$match` stage. Regular query functions compares a value against a document field. Take a look [here](https://stackoverflow.com/questions/4442453/mongodb-query-condition-on-comparing-2-fields). Something like `{$match:{$expr:{$gt:["$posts.created_time", "$past_month"]}}}` in 3.6 — s7vr, Jan 30 '18 at 14:10
Yes @cop, matched by `created_time` from up level document, the document `created_time` — BrTkCa, Jan 30 '18 at 14:13
I think we use $project: { "fieldName" :1 } for particular collection. so use $project : { ... "created_time": 1, "posts": 1} — IftekharDani, Jan 30 '18 at 14:14

chridam · Accepted Answer · 2018-01-30T14:40:29.963

For the requirement:

Each post needs to have created_time greater than created_time of the document

to be satisfied, with MongoDB Server version 3.4 and above, use the $addFields pipeline in conjunction with the $filter operator to filter the posts as:

db.collection.aggregate([
    {
        "$addFields": {
            "posts": {
                "$filter": {
                    "input": "$posts",
                    "as": "post",
                    "cond": { 
                        "$gt": [
                            "$$post.created_time", 
                            { "$subtract": ["$created_time", 2629746000] }
                        ] 
                    }
                }
            }
        }
    }
])

The $addFields will replace the posts array with the filtered one in the expression above.

For MongoDB 3.2 you can still use $filter not within $addFields pipeline as it's not supported but with $project instead.

For MongoDB 3.0 use a combination of $setDifference and $map operators to filter the posts array as

db.collection.aggregate([
    { 
        "$project": {
            "created_time": 1,
            "posts": { 
                "$setDifference": [
                    { 
                        "$map": {
                            "input": "$posts",
                            "as": "post",
                            "in": {
                                "$cond": [
                                    { 
                                        "$gt": [
                                            "$$post.created_time", 
                                            { "$subtract": ["$created_time", 2629746000] }
                                        ] 
                                    },
                                    {
                                        "story" : "$$post.story",
                                        "created_time" : "$$post.created_time",
                                        "id" : "$$post.id",
                                        "_id" : "$$post._id",
                                    },
                                    false
                                ]
                            }
                        }
                    },
                    [false]
                ]
            }
        }
    }
])

Useful answer. I have a question. `"$gt": ["$$post.created_time", "$created_time"]` is subtracting the date or I need to project `past_month` before filter? I'm MongoDB 3.4 — BrTkCa, Jan 30 '18 at 14:36
You can substitute that with the expression `{ "$gt": [ "$$post.created_time", { "$subtract": ["$created_time", 2629746000] } ] }`, no need for an extra pipeline prior to this. — chridam, Jan 30 '18 at 14:38

score 0 · Answer 2 · answered Jan 30 '18 at 14:40

You can do this in simple and clean way with the help of moment.js library -

var checkForDate = moment().subtract(1, 'months');

var startDate= moment(checkForDate).startOf('month');
var endDate= moment(checkForDate).endOf('month');


db.collection.find({
                 "posts.created_time":{
                                   $lt:endDate,
                                   $gt:startDate
                             }
                  })

you can achieve your desired result with this without using aggregate chain mechanism

I'm using only mongo query, not javascript – BrTkCa Jan 30 '18 at 16:34 — BrTkCa, Jan 30 '18 at 16:34

$match in date after $project and $subtract returns empty result in MongoDB

2 Answers2