CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Adit A. Pillai
Adit A. Pillai

Reputation: 667

Inserting/Updating Aggregated data on Mongo

Consider the following collection:

requests->

{
  _id : ObjectId("573f28f49b0ffc283676f736"),
  date : '2018-12-31',
  userId: ObjectId("5c1239e93a7b9a72ef1c9197"),
  serviceId: ObjectId("572e29b0116b5db3057bd821"),
  status: 'completed'
}

I have an aggregate operation on requests collection which returns documents in the following format:

{
    "grossRequests" : 2,
    "grossData" : [ 
        {
            "date" : "2018-08-04",
            "count" : 1,
            "requests" : [ 
                ObjectId("5b658147c73beb5ea3debc6e")
            ]
        }, 
        {
            "date" : "2018-08-05",
            "count" : 1,
            "requests" : [ 
                ObjectId("5b658160572faa5dd033fb48")
            ]
        }
    ],
    "netData" : [ 
        {
            "date" : "2018-08-05",
            "count" : 1,
            "requests" : [ 
                ObjectId("5b658160572faa5dd033fb48")
            ]
        }
    ],
    "netRequests" : 1.0,
    "userId" : ObjectId("5c1239e93a7b9a72ef1c9197"),
    "serviceId" : "572e29b0116b5db3057bd821"
}

Above is the document to be inserted in cumulativeData

Now, I need to add all the documents returned by my aggregation operation, into a collection called cumulativeData.

The cumulativeData collection has one document per userId per serviceType.

I am running a query for specific date ranges and while inserting them into the collection, would like to "merge" the documents, rather than replacing them.

so, for example, if I am looping through all the documents returned by the aggregation operation using forEach, for each new document I get ({userId,serviceType} pair which is not present in the cumulativeData collection) I need to insert it as is and for every document which is already present in the collection, I need to update it as follows.

grossRequests -> add both values
grossData -> push the new values into the existing set
netData -> push the new values into the existing set
netRequests -> add both values

The operation would be as follows,

Existing doc in ** cumulativeData **

{
    "grossRequests": 2,
    "grossData": [{
            "date": "2018-08-04",
            "count": 1,
            "requests": [
                ObjectId("5b658147c73beb5ea3debc6e")
            ]
        }, {
            "date": "2018-08-05",
            "count": 1,
            "requests": [
                ObjectId("5b658160572faa5dd033fb48")
            ]
        }
    ],
    "netData": [{
            "date": "2018-08-05",
            "count": 1,
            "requests": [
                ObjectId("5b658160572faa5dd033fb48")
            ]
        }
    ],
    "netRequests": 1,
    "userId": ObjectId("5c1239e93a7b9a72ef1c9197"),
    "serviceId": "572e29b0116b5db3057bd821"
}

New Document generated after aggregation on a new date range

{
    "grossRequests": 2,
    "grossData": [{
            "date": "2018-08-04",
            "count": 1,
            "requests": [
                ObjectId("5b658147c73beb5ea3debc8e")
            ]
        }, {
            "date": "2018-08-05",
            "count": 1,
            "requests": [
                ObjectId("5b658160572faa5dd033fb4l")
            ]
        }
    ],
    "netData": [{
            "date": "2018-08-05",
            "count": 1,
            "requests": [
                ObjectId("5b658160572faa5dd033fb4l")
            ]
        }
    ],
    "netRequests": 1,
    "userId": ObjectId("5c1239e93a7b9a72ef1c9197"),
    "serviceId": "572e29b0116b5db3057bd821"
}

Final Result

{
    "grossRequests": 4,
    "grossData": [{
            "date": "2018-08-04",
            "count": 2,
            "requests": [
                ObjectId("5b658147c73beb5ea3debc6e") , ObjectId("5b658147c73beb5ea3debc8e")
            ]
        }, {
            "date": "2018-08-05",
            "count": 2,
            "requests": [
                ObjectId("5b658160572faa5dd033fb48"), ObjectId("5b658160572faa5dd033fb4l")
            ]
        }
    ],
    "netData": [{
            "date": "2018-08-05",
            "count": 2,
            "requests": [
                ObjectId("5b658160572faa5dd033fb48"),ObjectId("5b658160572faa5dd033fb4l")
            ]
        }
    ],
    "netRequests": 2,
    "userId": ObjectId("5c1239e93a7b9a72ef1c9197"),
    "serviceId": "572e29b0116b5db3057bd821"
}

Upvotes: 0

Views: 561

Answers (1)

s7vr
s7vr

Reputation: 75914

You can use below code to perform updates.

It involves two steps first step when record with userid/service id not found insert data else update data.

Within update step first update the top fields i.e count followed by iterating netData and grossData to perform the merge.

To perform merge we use the writeresult which has nmodified count to identify if we have to update the array value or insert new array value.

You can adjust below query to meet your needs.

db.getCollection('requests').aggregate(your aggregation query).forEach(function(doc) {
var user_id = doc.userId;
var service_id = doc.serviceId;
var gross_requests = doc.grossRequests;
var net_requests = doc.netRequests;
var gross_data = doc.grossData;
var net_data = doc.netData;

var matched = db.getCollection('cumulativeData').findOne({
    "userId": user_id,
    serviceId: service_id
});
if (matched == null) {
    db.getCollection('cumulativeData').insert(doc);
} else {
    db.getCollection('cumulativeData').update({
        "userId": user_id,
        serviceId: service_id
    }, {
        $inc: {
            "grossRequests": gross_requests,
            "netRequests": net_requests
        }
    });
    gross_data.forEach(function(grdoc) {
        var writeresult = db.getCollection('cumulativeData').update({
            "userId": user_id,
            serviceId: service_id,
            "grossData.date": grdoc.date
        }, {
            $inc: {
                "grossData.$.count": grdoc.count
            },
            $push: {
                "grossData.$.requests": {
                    $each: grdoc.requests
                }
            }
        });
        if (writeresult.nModified == 0) {
            db.getCollection('cumulativeData').update({
                "userId": user_id,
                serviceId: service_id
            }, {
                $push: {
                    "grossData": {
                        "count": grdoc.count,
                        "requests": grdoc.requests,
                        "date": grdoc.date
                    }
                }
            });
        }
    });
    net_data.forEach(function(nrdoc) {
        var writeresult = db.getCollection('cumulativeData').update({
            "userId": user_id,
            serviceId: service_id,
            "netData.date": nrdoc.date
        }, {
            $inc: {
                "netData.$.count": nrdoc.count
            },
            $push: {
                "netData.$.requests": {
                    $each: nrdoc.requests
                }
            }
        });
        if (writeresult.nModified == 0) {
            db.getCollection('cumulativeData').update({
                "userId": user_id,
                serviceId: service_id
            }, {
                $push: {
                    "netData": {
                        "count": nrdoc.count,
                        "requests": nrdoc.requests,
                        "date": nrdoc.date
                    }
                }
            });
        }
    });
}

});

Upvotes: 1

Related Questions