Sum of array value and set that value to key

MongoDB Atlas
1

image
image904×419 59.4 KB

Hi This is my JSON I wanted to sum the key grandTotal that is in the array object inside the amount object and get the total and assign that total to grandTotal

For Reference:

{
	"_id": "1141508",
	"orderDevice": "desktop",
	"deviceVersion": "5.0",
	"sourceType": "ONLINE",
	"grandTotal": 3710.0,
	"basketTotal": 0.0,
	"couponCode": "LUCKY200",
	"attributes": {},
	"orders": [{
			"orderId": "1001-66-1141508",
			"storeId": "66",
			"orderStatus": "DELIVERY_ADDRESS",
			"deliveryType": "HYPERLOCAL_DELIVERY",
			"carrierPartner": "TELYPORT",
			"isGiftWrapped": "N",
			"isOtpVerified": "N",
			"isSubscription": "N",
			"isSubscriptionScheduler": "N",
			"posStatus": "N",
			"orderEvents": {
				"orderCreatedDate": "",
				"orderEntryDate": ""
			},
			"amount": {
				"remainingSubTotal": 0.0,
				"totalSkuDiscount": 0.0,
				"totalOtherDiscount": 0.0,
				"grandTotal": 3710.0,
				"giftCardTotal": 0.0,
				"oldGiftCardTotal": 0.0,
				"grandTotalInvoice": 0.0,
				"paymentCharges": 0.0
			}
		},
		{
			"orderId": "2002-999-1141508",
			"storeId": "999",
			"orderStatus": "DELIVERY_ADDRESS",
			"deliveryType": "STANDARD_DELIVERY",
			"carrierPartner": "ECOM_EXPRESS",
			"isGiftWrapped": "N",
			"isOtpVerified": "N",
			"isSubscription": "N",
			"isSubscriptionScheduler": "N",
			"posStatus": "N",
			"orderEvents": {
				"orderCreatedDate": "",
				"orderEntryDate": ""
			},
			"amount": {
				"remainingSubTotal": 0.0,
				"totalSkuDiscount": 247.0,
				"totalOtherDiscount": 0.0,
				"grandTotal": 6520.0,
				"giftCardTotal": 0.0,
				"oldGiftCardTotal": 0.0,
				"grandTotalInvoice": 0.0,
				"paymentCharges": 0.0
			}
		}
	]
}

As above mentioned Json i wanted the output of grandTotal to be 10230 which is now 3710.0 in the reference so i need to sum up order.amount.grandtotal(that is 3710.0)+order.amount.grandtotal(that is 6520.0)
Kindly do the needfull.

2

What you want to do it use $addFields together with $reduce to compute your grandTotal.

3

Hi @Dhanush_R_M and welcome to the MongoDB community forums! :wave:

Steeve gives you one way of doing things, but it really depends on what you need from your output which you don’t show what you’re looking for in a result. If you just need the _id field and a grand total, you could run the following:

db.test.aggregate(
  [
    {
      "$project": {
        "orders": 1
      }
    },
    {
      "$unwind": "$orders"
    },
    {
      "$group": {
        "_id": "$_id",
        "grandTotal": {
          "$sum": "$orders.amount.grandTotal"
        }
      }
    }
  ]
)

This will return the following for the sample document you provided:

[ { _id: '1141508', grandTotal: 10230 } ]

Note that if you have a lot of documents that you’re trying to perform this on, you might run into performance issues so you will want to thoroughly test this before putting it into production. Of course this warning goes for any solution you put into production. The above aggregation pipeline will create a new document for every item in the orders array for every document sent through the pipeline.

1 Like