How to subtract two value in each row and sum the result of all routes

[Nimatullah Razmjo]

I have recrods which looks like:

{
    "_id" : ObjectId("5a2278d223b0db78fd6697c9"),
    "invoiceNumber" : 10007,
    "updatedAt" : ISODate("2017-12-02T09:56:40.566Z"),
    "createdAt" : ISODate("2017-12-02T09:56:34.265Z"),
    "postedResource" : [],
    "deleted" : false,
    "active" : true,
    "packageDetails" : {
        "usedCredit" : 0,
        "price" : {
            "amount" : 180
        },
        "validDays" : 0,
        "pricePlan" : {
            "title" : "Company / Corporation",
            "id" : ObjectId("5a18ffa7e909d2294122132f")
        },
        "numOfJob" : 20,
        "name" : "Jobs Starter",
        "type" : "jobs-posting",
        "id" : ObjectId("5a22780123b0db78fd6697c0")
    },
    "requestedBy" : {
        "lastName" : "advin",
        "firstName" : "advin",
        "id" : ObjectId("5a18fc195555d4126fe1c85f")
    },
    "customer" : {
        "company" : {
            "name" : "Advin LTD",
            "id" : ObjectId("5a18fc1a5555d4126fe1c862")
        },
        "lastName" : "advin",
        "firstName" : "advin",
        "id" : ObjectId("5a18fc195555d4126fe1c85f")
    },
    "paymentDetails" : {
        "bankName" : "m-Paisa",
        "accountName" : "Jobs.af",
        "accountId" : "101111",
        "paymentType" : "bank"
    },
    "isPaid" : false,
    "__v" : 0,
    "approvedAt" : ISODate("2017-12-25T07:27:35.143Z"),
    "approvedBy" : {
        "_id" : ObjectId("5a40a86761ff883a6db07d85"),
        "id" : ObjectId("5a3118c5ca48c425f2f219f1"),
        "firstName" : "Nimatullah",
        "lastName" : "Razmjo"
    },
    "cancelledAt" : ISODate("2017-12-21T18:06:05.586Z"),
    "cancelledBy" : {
        "_id" : ObjectId("5a3bf80df39abc17d89e1fef"),
        "id" : ObjectId("5a3118c5ca48c425f2f219f1"),
        "firstName" : "Nimatullah",
        "lastName" : "Razmjo"
    }
}

/* 2 */
{
    "_id" : ObjectId("5a409e2861ff883a6db07ab7"),
    "invoiceNumber" : 10008,
    "updatedAt" : ISODate("2017-12-25T06:43:52.450Z"),
    "createdAt" : ISODate("2017-12-25T06:43:52.450Z"),
    "postedResource" : [],
    "deleted" : false,
    "active" : true,
    "packageDetails" : {
        "usedCredit" : 0,
        "price" : {
            "amount" : 180
        },
        "validDays" : 0,
        "pricePlan" : {
            "title" : "Company / Corporation",
            "id" : ObjectId("5a18ffa7e909d2294122132f")
        },
        "numOfJob" : 20,
        "name" : "Jobs Starter",
        "type" : "jobs-posting",
        "id" : ObjectId("5a22780123b0db78fd6697c0")
    },
    "requestedBy" : {
        "lastName" : "Razmjo",
        "firstName" : "Nimatullah",
        "id" : ObjectId("5a3118c5ca48c425f2f219f1")
    },
    "customer" : {
        "company" : {
            "name" : "Netlinks Af",
            "id" : ObjectId("5a407d40fb61690bc13e4068")
        },
        "lastName" : "Ghiyasi",
        "firstName" : "Farshid.E",
        "id" : ObjectId("5a311935ca48c425f2f219f3")
    },
    "paymentDetails" : {
        "exchangeRate" : 10,
        "bankName" : "m-Paisa",
        "accountName" : "Jobs.af",
        "accountId" : "101111",
        "paymentType" : "Cash"
    },
    "isPaid" : false,
    "__v" : 0,
    "approvedAt" : ISODate("2017-12-25T07:23:46.770Z"),
    "approvedBy" : {
        "_id" : ObjectId("5a40a78261ff883a6db07d67"),
        "id" : ObjectId("5a3118c5ca48c425f2f219f1"),
        "firstName" : "Nimatullah",
        "lastName" : "Razmjo"
    }
}

/* 3 */
{
    "_id" : ObjectId("5a409e2e61ff883a6db07ac8"),
    "invoiceNumber" : 10009,
    "updatedAt" : ISODate("2017-12-25T06:43:58.789Z"),
    "createdAt" : ISODate("2017-12-25T06:43:58.789Z"),
    "postedResource" : [],
    "deleted" : false,
    "active" : false,
    "packageDetails" : {
        "price" : {
            "amount" : 180
        },
        "validDays" : 0,
        "pricePlan" : {
            "title" : "Company / Corporation",
            "id" : ObjectId("5a18ffa7e909d2294122132f")
        },
        "numOfJob" : 20,
        "name" : "Jobs Starter",
        "type" : "jobs-posting",
        "id" : ObjectId("5a22780123b0db78fd6697c0")
    },
    "requestedBy" : {
        "lastName" : "Razmjo",
        "firstName" : "Nimatullah",
        "id" : ObjectId("5a3118c5ca48c425f2f219f1")
    },
    "customer" : {
        "company" : {
            "name" : "Netlinks Af",
            "id" : ObjectId("5a407d40fb61690bc13e4068")
        },
        "lastName" : "Ghiyasi",
        "firstName" : "Farshid.E",
        "id" : ObjectId("5a311935ca48c425f2f219f3")
    },
    "paymentDetails" : {
        "exchangeRate" : 10,
        "bankName" : "m-Paisa",
        "accountName" : "Jobs.af",
        "accountId" : "101111",
        "paymentType" : "Cash"
    },
    "isPaid" : false,
    "__v" : 0,
    "rejectedAt" : ISODate("2017-12-25T07:36:58.359Z"),
    "rejectedBy" : {
        "_id" : ObjectId("5a40aa9a61ff883a6db07db9"),
        "id" : ObjectId("5a3118c5ca48c425f2f219f1"),
        "firstName" : "Nimatullah",
        "lastName" : "Razmjo"
    }
}

/* 4 */
{
    "_id" : ObjectId("5a409eda61ff883a6db07b22"),
    "invoiceNumber" : 10010,
    "updatedAt" : ISODate("2017-12-25T06:46:50.618Z"),
    "createdAt" : ISODate("2017-12-25T06:46:50.618Z"),
    "postedResource" : [],
    "deleted" : false,
    "active" : false,
    "packageDetails" : {
        "id" : ObjectId("5a22780123b0db78fd6697c0"),
        "type" : "jobs-posting",
        "name" : "Jobs Starter",
        "numOfJob" : 20,
        "pricePlan" : {
            "id" : ObjectId("5a18ffa7e909d2294122132f"),
            "title" : "Company / Corporation"
        },
        "validDays" : 0,
        "price" : {
            "amount" : 180
        }
    },
    "requestedBy" : {
        "id" : ObjectId("5a3118c5ca48c425f2f219f1"),
        "firstName" : "Nimatullah",
        "lastName" : "Razmjo"
    },
    "customer" : {
        "id" : ObjectId("5a311935ca48c425f2f219f3"),
        "firstName" : "Farshid.E",
        "lastName" : "Ghiyasi",
        "company" : {
            "id" : ObjectId("5a407d40fb61690bc13e4068"),
            "name" : "Netlinks Af"
        }
    },
    "paymentDetails" : {
        "paymentType" : "Cash",
        "accountId" : "101111",
        "accountName" : "Jobs.af",
        "bankName" : "m-Paisa",
        "exchangeRate" : 10
    },
    "isPaid" : false,
    "__v" : 0
}

Now, I want to subtract in each row packageDetails.numOfJob and packageDetails.usedCredit, and calculate result of each row.

I have found how to subtract, but I dont know how to calculate each row result.

My Query to subtract:

db.getCollection('userpackages').aggregate([
{
    "$project": {
        "total": {
                "$sum": {
                       "$subtract": ["$packageDetails.numOfJob", "$packageDetails.usedCredit"]
                    }
            }

     }
}
])

Thanks

[Gaurav Gupta]

Hi Nimatullah ,

Could you clarify your question by providing the desired output from the aggregation ?

If you're looking to get the sum of all rows, you can utilise $group to aggregate them all, for example:

db.getCollection('userpackages').aggregate([

{

"$project": {

"total": {

"$subtract": ["$packageDetails.numOfJob", "$packageDetails.usedCredit"]

}

}

},

{

"$group": {

_id: null, "finalresult": {

"$sum": "$total"

}

}

}

])

For more information, see MongoDB Aggregation Pipeline Stages.

Regards,
Gaurav