Find after aggregate in MongoDB

Question

{
    "_id" : ObjectId("5852725660632d916c8b9a38"),
    "response_log" : [ 
    {
        "campaignId" : "AA",
        "created_at" : ISODate("2016-12-20T11:53:55.727Z")
    }, 
    {
        "campaignId" : "AB",
        "created_at" : ISODate("2016-12-20T11:55:55.727Z")

    }]
}

I have a document which contains an array. I want to select all those documents that do not have response_log.created_at in last 2 hours from current time and count of response_log.created_at in last 24 is less than 3.

I am unable to figure out how to go about it. Please help

Answer 1

You can use the aggregation framework to filter the documents. A pipeline with $match and $redact steps will do the filtering.

Consider running the following aggregate operation where $redact allows you to proccess the logical condition with the $cond operator and uses the system variables $$KEEP to "keep" the document where the logical condition is true or $$PRUNE to "remove" the document where the condition was false.

This operation is similar to having a $project pipeline that selects the fields in the collection and creates a new field that holds the result from the logical condition query and then a subsequent $match, except that $redact uses a single pipeline stage which is more efficient:

var moment = require('moment'),
    last2hours = moment().subtract(2, 'hours').toDate(),
    last24hours = moment().subtract(24, 'hours').toDate();

MongoClient.connect(config.database)
    .then(function(db) {
        return db.collection('MyCollection')
    })
    .then(function (collection) {
        return collection.aggregate([
            { '$match': { 'response_log.created_at': { '$gt': last2hours } } },
            { 
                '$redact': {
                    '$cond': [
                        { 
                            '$lt': [
                                {
                                    '$size': {
                                        '$filter': {
                                            'input': '$response_log',
                                            'as': 'res',
                                            'cond': { 
                                                '$lt': [
                                                    '$$res.created_at', 
                                                    last24hours
                                                ] 
                                            }
                                        }
                                    }
                                },
                                3
                            ]
                        },
                        '$$KEEP',
                        '$$PRUNE'
                    ]
                }
            }
        ]).toArray();  
    })
    .then(function(docs) {
        console.log(docs)
    })
    .catch(function(err) {
        throw err;
    });

---

Explanations

In the above aggregate operation, if you execute the first $match pipeline step

collection.aggregate([
    { '$match': { 'response_log.created_at': { '$gt': last2hours } } }
])

The documents returned will be the ones that do not have "response_log.created_at" in last 2 hours from current time where the variable last2hours is created with the momentjs library using the subtract API.

---

The preceding pipeline with $redact will then further filter the documents from the above by using the $cond ternary operator that evaluates this logical expression that uses $size to get the count and $filter to return a filtered array with elements that match other logical condition

{ 
    '$lt': [
        {
            '$size': {
                '$filter': {
                    'input': '$response_log',
                    'as': 'res',
                    'cond': { '$lt': ['$$res.created_at', last24hours] }
                }
            }
        },
        3
    ]
}

to $$KEEP the document if this condition is true or $$PRUNE to "remove" the document where the evaluated condition is false.