{
    "_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

CodePudding user response：

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.

CodePudding user response：

I know that this is probably not the answer that you're looking for but this may not be the best use case for Mongo. It's easy to do that in a relational database, it's easy to do that in a database that supports map/reduce but it will not be straightforward in Mongo.

If your data looked different and you kept each log entry as a separate document that references the object (with id 5852725660632d916c8b9a38 in this case) instead of being a part of it, then you could make a simple query for the latest log entry that has that id. This is what I would do in your case if I ware to use Mongo for that (which I wouldn't).

What you can also do is keep a separate collection in Mongo, or add a new property to the object that you have here which would store the latest date of campaign added. Then it would be very easy to search for what you need.

When you are working with a database like Mongo then how your data looks like must reflect what you need to do with it, like in this case. Adding a last campaign date and updating it on every campaign added would let you search for those campaign that you need very easily.

If you want to be able to make any searches and aggregates possible then you may be better off using a relational database.