How to calculate the sum of confident_score for every individual vendor?

Data stored in the DB:

  [
            {
                "_id": "61cab38891152daf9387c0c7",
                "name": "dummy",
                "company_email": "[email protected]",
                "brief_msg": "Cillum sed est prae",
                "similar_case_ids": [],
                "answer_id": [
                    "61cab38891152daf9387c0c9"
                ],
                "pros_cons": [
                    {
                        "vendor_name": "xyzlab",
                        "score": [
                            {
                                "question_id": "61c5b47198b2c5bbf9f6471c",
                                "title": "Vendor F",
                                "confident_score": 80,
                                "text": "text1",
                                "_id": "61cac505caeeeb3cec78bf0f"
                            },
                            {
                                "question_id": "61c5b47198b2c5bbf9f6471c",
                                "title": "Vendor FFF",
                                "confident_score": 40,
                                "text": "text1",
                                "_id": "61cac505caeeeb3cec78bf10"
                            }
                        ]
     
                    },
                    {
                        "vendor_name": "abclab",
                        "score": [
                            {
                                "question_id": "61c5b47198b2c5bbf9f6471c",
                                "title": "Vendor B",
                                "confident_score": 50,
                                "text": "text1",
                                "_id": "61cac505caeeeb3cec78bf16"
                            },
                            {
                                "question_id": "61c5b47198b2c5bbf9f6471c",
                                "title": "Vendor BB",
                                "confident_score": 60,
                                "text": "text1",
                                "_id": "61cac505caeeeb3cec78bf17"
                            }
                        ]
                    }
                ]

the query for getting the matching id and grouping objects according to the vendor_name:

aggregate([
    {
       $match: { _id: id } 
    },
    {
      $unwind: {
           path: '$pros_cons'
         }
    },
    {  
      $group: {
         _id: '$pros_cons'
      }
    },

  ])
};

After query I'm getting this:

[
        {
            "_id": {
                "vendor_name": "abclab",
                "score": [
                    {
                        "question_id": "61c5b47198b2c5bbf9f6471c",
                        "title": "Vendor B",
                        "confident_score": 50,
                        "text": "text1",
                        "_id": "61cac505caeeeb3cec78bf16"
                    },
                    {
                        "question_id": "61c5b47198b2c5bbf9f6471c",
                        "title": "Vendor BB",
                        "confident_score": 60,
                        "text": "text1",
                        "_id": "61cac505caeeeb3cec78bf17"
                    }
                ],
            }
        },
        {
            "_id": {
                "vendor_name": "xyzlab",
                "score": [
                    {
                        "question_id": "61c5b47198b2c5bbf9f6471c",
                        "title": "Vendor F",
                        "confident_score": 80,
                        "text": "text1",
                        "_id": "61cac505caeeeb3cec78bf0f"
                    },
                    {
                        "question_id": "61c5b47198b2c5bbf9f6471c",
                        "title": "Vendor FFF",
                        "confident_score": 40,
                        "text": "text1",
                        "_id": "61cac505caeeeb3cec78bf10"
                    }
                ],
            }
        }
    ]

Need to calculate sum for (vendor_name:abclab)TOTAL=110 and for (vendor_name:xyzlab)TOTAL=120 INDIVIDUALLY

required output:

[
           {
                "vendor_name": "abclab",
                 "totalScore": 110,
                 "count"     : 2
           },
          {
                 "vendor_name": "xyzlab",
                 "totalScore": 120,
                 "count"     : 2

        }
    ]

CodePudding user response：

1. $match - Filter documents by id.
2. $unwind - Deconstruct pros_cons array to multiple documents.
3. $project - Decorate output documents. With $reduce, to create totalScore field by summing confident_score from each element in pros_cons.score array.

db.collection.aggregate([
  {
    $match: {
      _id: "61cab38891152daf9387c0c7"
    }
  },
  {
    $unwind: {
      path: "$pros_cons"
    }
  },
  {
    $project: {
      _id: 0,
      vendor_name: "$pros_cons.vendor_name",
      totalScore: {
        $reduce: {
          input: "$pros_cons.score",
          initialValue: 0,
          in: {
            $sum: [
              "$$value",
              "$$this.confident_score"
            ]
          }
        }
      }
    }
  }
])

Sample Demo on Mongo Playground