如何計算每個供應商的confidence_score總和？
存盤在資料庫中的資料：
[
{
"_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"
}
]
}
]
根據 vendor_name 獲取匹配的 id 和分組物件的查詢：
aggregate([
{
$match: { _id: id }
},
{
$unwind: {
path: '$pros_cons'
}
},
{
$group: {
_id: '$pros_cons'
}
},
])
};
查詢后我得到這個：
[
{
"_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"
}
],
}
}
]
需要單獨計算 (vendor_name:abclab)TOTAL=110 和 (vendor_name:xyzlab)TOTAL=120 的總和
所需輸出：
[
{
"vendor_name": "abclab",
"totalScore": 110,
"count" : 2
},
{
"vendor_name": "xyzlab",
"totalScore": 120,
"count" : 2
}
]
uj5u.com熱心網友回復：
$match- 過濾檔案id。$unwind- 將pros_cons陣列解構為多個檔案。$project- 裝飾輸出檔案。使用$reduce,totalScore通過對陣列中的confident_score每個元素求和來創建欄位pros_cons.score。
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"
]
}
}
}
}
}
])
Mongo Playground 上的示例演示
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/396565.html
標籤：数据库 MongoDB 猫鼬 mongodb-查询