CODEWITHSUNDEEP
mongodbaggregation-framework
phani
phani

Reputation: 613

MongoDb Aggregate nested documents with $add

I need to get sum value from nested documents.

DB document:

{
  "_id": 123,
  "products": [
    {
      "productId": 1,
      "charges": [
        {
          "type": "che",
          "amount": 100
        }
      ]
    }
  ]
}

i wanted to get sum value. sumValue = products.charges.amount+20; where "products.productId" is 1 and "products.charges.type" is "che"

i tried below query but no hope:

db.getCollection('test').aggregate(
   [
        {"$match":{$and:[{"products.productId": 14117426}, {"products.charges.type":"che"}]},
     { $project: { "_id":0, total: { $add: [ "$products.charges.price", 20 ] } }}

   ]
)

please help me to solve this.

Upvotes: 1

Views: 262

Answers (2)

thev0id
thev0id

Reputation: 195

You have to take a look at $unwind operator which deconstructs an array to output a document for each element of array. Also take a look at add and project operators.

I assume your db query should look like this:

db.test.aggregate([
{$unwind: '$products'}, // Unwind products array
{$match: {'products.productId' : 3}}, // Matching product id 
{$unwind: '$products.charges'}, // Unwind charges
{$match: {'products.charges.type' : 'che'}}, // Matching charge type of che 
{$project: {'with20': {$add: ["$products.charges.amount",  20]}}}, // project total field which is value + 20
{$group: {_id : null,      amount:  { $sum: '$with20' }}}  // total sum
])

Upvotes: 2

mickl
mickl

Reputation: 49945

You can run $reduce twice to convert your arrays into scalar value. The outer condition could be applied as $filter, the inner one can be run as $cond:

db.collection.aggregate([
    {
        "$project": {
            _id: 0,
            total: {
                $reduce: {
                    input: { $filter: { input: "$products", cond: [ "$$this.productId", 1 ] } },
                    initialValue: 20,
                    in: {
                        $add: [
                            "$$value",
                            {
                                $reduce: {
                                    input: "$$this.charges",
                                    initialValue: 0,
                                    in: {
                                        $cond: [ { $eq: [ "$$this.type", "che" ] }, "$$this.amount", 0 ]
                                    }
                                }
                            }
                        ]
                    }
                }
            }
        }
    }
])

Mongo Playground

Upvotes: 1

Related Questions