How can i calculate price positive and negative pr

Question

below is my userpricing collection data

{
    "_id" : ObjectId("584bc9ba420a6b189c510af6"),
    "user_id" : 1,
    "mobilenumber":"01234",
    "price" : 2000.0,
    "type" : "credit",

},
{
    "_id" : ObjectId("584bc9ba420a6b189c510af6"),
    "user_id" : 1,
    "mobilenumber":"01234",
    "price" : -1000.0,
    "type" : "credit",

},
{
    "_id" : ObjectId("584bc9ba420a6b189c3323w23"),
    "user_id" : 2,
    "mobilenumber":"04321",
    "price" : 1000.0,
    "type" : "credit",

}

here i want to calculate total postive and total negative price of all user and i need to check whether that user is exists or not in summary collection.if record not exists we need to create document in summary collection if its exists we need to update "Totalpositiveprice","Totalnegativeprice" and "Balanceprice"

in summary table already exists this record

    {

        "user_id": "1",
        "mobilenumber":"01234",
        "Totalpositiveprice": 3000.0,
        "Totalnegativeprice": 0,
        "Balanceprice": 3000.0
    },
   {

        "user_id": "3",
        "mobilenumber":"05555",
        "Totalpositiveprice": 1000.0,
        "Totalnegativeprice": -100,
        "Balanceprice": 900.0
    }

1. we need to update the document for "mobilenumber":"01234",

2. we need to create new document for "mobilenumber":"04321",

3. "mobilenumber":"05555" no need to do anything bcoz nothing is there in userpricing

finally i should get summary collection like this

 {

        "user_id": "1",
        "mobilenumber":"01234"
        "Totalpositiveprice": 5000.0,
        "Totalnegativeprice": -1000.0,
        "Balanceprice": 4000.0
    },
    {

        "user_id": "2",
         "mobilenumber":"04321"
        "Totalpositiveprice": 1000.0,
        "Totalnegativeprice": 0,
        "Balanceprice": 1000.0
    },
    {

    "user_id": "3",
    "mobilenumber":"05555",
    "Totalpositiveprice": 1000.0,
    "Totalnegativeprice": -100,
    "Balanceprice": 900.0
}

Answer 1

You can try bulk write to bulk upload the update queries created from aggregation result and update the summary collection.

Here is a quick code that you can try from Mongo shell and you can adjust to your needs.

The below code queries for user_id and increments the price values based on the aggregation values and upserts if no matching user_id is found.

You should change the batch based on your needs.

var bulk = db.getCollection('summary').initializeUnorderedBulkOp();
var count = 0;
var batch = 1;

db.getCollection('userpricing').aggregate([
    {$group: {
        _id:"$user_id", 
        Totalpositiveprice:{$sum:{$cond:[{ '$gt': ['$price', 0]}, "$price", 0]}}, 
        Totalnegativeprice:{$sum:{$cond:[{ '$lt': ['$price', 0]}, "$price", 0]}},
        Balanceprice:{"$sum":"$price"}}
     },
    {$project: {_id:0, user_id:"$_id", Totalpositiveprice:1, Totalnegativeprice:1, Balanceprice:1}}
]).forEach(function(doc){ 
    var user_id = doc.user_id; 
    var totalpositiveprice = doc.Totalpositiveprice; 
    var totalnegativeprice = doc.Totalnegativeprice; 
    var balanceprice = doc.Balanceprice; 
    bulk.find({ "user_id" : user_id }).upsert().updateOne(
      { $inc: {"Totalpositiveprice" : totalpositiveprice, "Totalnegativeprice" : totalnegativeprice, "Balanceprice" : balanceprice } }
   ); 
    count++;  
    if (count == batch) { 
        bulk.execute(); 
        bulk = db.getCollection('summary').initializeUnorderedBulkOp(); 
        count = 0;
    } 
});

if (count > 0) { 
      bulk.execute(); 
 }

Answer 2

Whatever you want to do involves a complex query. See the below query. Let me explain it first.

1- first pipeline $group is obvious to you.

2- second pipeline $lookup is required to generate your desired result as you want that the current prices and the already summarized prices should be compiled into one document.

3- third stage is just unwinding the array summary we have looked up so that we will be able to compile the prices.

4- fourth $project stage is doing what you really want to do that is to say that it is compiling the summary document you will be able to understand it.

5- generating the summary collection.

db.getCollection('userpricing').aggregate([
     {$group:{
            _id:"$user_id",
            Totalpositiveprice:{$sum:{$cond:{if:{ $gt:["$price",0]}, then: "$price", else: 0}}},
            Totalnegativeprice:{$sum:{$cond:{if:{ $lt:["$price",0]}, then: "$price", else: 0}}},
            Balanceprice:{"$sum":"$price"}
     }},
     {
      $lookup:
        {
          from: "summary",
          localField: "_id",
          foreignField: "user_id",
          as: "summary"
        }
     },
     {$unwind:{path:"$summary", preserveNullAndEmptyArrays:true}},
     {$project:{
         _id:0,
         user_id:"$_id",
         Balanceprice:{$add:["$Balanceprice", {$cond:{if:{ $eq:["$summary",undefined]}, then: 0, else: "$summary.Balanceprice"}}]},
         Totalnegativeprice:{$add:["$Totalnegativeprice", {$cond:{if:{ $eq:["$summary",undefined]}, then: 0, else: "$summary.Totalnegativeprice"}}]},
         Totalpositiveprice:{$add:["$Totalpositiveprice", {$cond:{if:{ $eq:["$summary",undefined]}, then: 0, else: "$summary.Totalpositiveprice"}}]}
     }},
   {$out:"summary"}  
])

Answer 3

As from other answers, the problem with $out is that it will replace the summary collection if one exists and you may not want that. You can use a little JavaScript for that.

var cursor = db.userpricing.aggregate([
  {
    $group : {
      _id : "$user_id",
      Totalpositiveprice : {
        $sum : {
          $cond : {
            if: { $gte : ["$price" , 0]},
            then : "$price",
            else : 0
          }
        }
      },
      Totalnegativeprice : {
        $sum : {
          $cond : {
            if: {$lte : ["$price" , 0]},
            then : "$price",
            else : 0
          }
        }
      },
      Balanceprice : {$sum : "$price"}
    }
  },
  {
    $project : {
      _id : 0,
      user_id : "$_id",
      Totalpositiveprice : 1,
      Totalnegativeprice : 1,
      Balanceprice : 1
    }
  }
]);

while(cursor.hasNext()) {
  var elem = cursor.next();
  db.summary.update(
    {user_id : elem.user_id},
    elem,
    {upsert : true}
  );
}



> db.summary.find()
{ "_id" : ObjectId("58564bb71e2cb47f2ddcf1b4"), "Totalpositiveprice" : 10000, "Totalnegativeprice" : 0, "Balanceprice" : 10000, "user_id" : 2 }
{ "_id" : ObjectId("58564bb71e2cb47f2ddcf1b5"), "Totalpositiveprice" : 20000, "Totalnegativeprice" : -10000, "Balanceprice" : 10000, "user_id" : 1 }

Answer 4

You can do it by using conditional sum and export in another collection use $out: collectionName

can try it :

db.getCollection('userpricing').aggregate([
    {$group: {
        _id:"$user_id", 
        user_id: {$first: "$user_id"}, 
        Totalpositiveprice:{$sum:{$cond:[{ '$gt': ['$price', 0]}, "$price", 0]}}, 
        Totalnegativeprice:{$sum:{$cond:[{ '$lt': ['$price', 0]}, "$price", 0]}},
        Balanceprice:{"$sum":"$price"}}
     },
     {$project: {_id:0, user_id:1, Totalpositiveprice:1, Totalnegativeprice:1, Balanceprice:1}},
     {$out: "summary"}
])

N.B: result exported in summary collection