gpt4 book ai didi

MongoDB:计算作为列表嵌入文档中的所有特定文档字段的平均值

转载 作者:可可西里 更新时间:2023-11-01 10:44:03 24 4
gpt4 key购买 nike

这里是mongodb中文档的结构:

{
"_id" : NumberLong(1),
"averageRating" : 2.5,
"date" : ISODate("2013-11-12T02:15:14.448Z"),
"filledBy" : "Degroote, Fred",
"results" : [
{
"answer" : "Very caring doctor. I am happy",
"name" : "comment",
"type" : "string"
},
{
"answer" : "4",
"name" : "premises",
"type" : "integer"
},
{
"answer" : "5",
"name" : "staff",
"type" : "integer"
},
{
"answer" : "0",
"name" : "provider",
"type" : "integer"
},
{
"answer" : "1",
"name" : "care",
"type" : "integer"
}
],
"tenantId" : NumberLong(2),
"token" : "38ae01f0-1203-4d10-9ff1-7c9fad1790cc",
"version" : NumberLong(0)
}

我们需要计算所有 tenantId 值为 2 的文档结果中结果名称“staff”的答案得分的平均值。

我们编写的 mongodb 查询是:

> db.surveyResults.aggregate({$match: {'tenantId': 2}}, {$unwind: '$results'}, {$match: {'results.type':'integer'}},{$group: {_id: '$results.name', averageSatisfaction: {$avg: '$results.answer'}}})

但是,上面mongodb查询的问题是字段answer是string类型。有没有办法在不改变字段答案类型的情况下解决这个问题?

最佳答案

我认为这是一个丑陋的查询,但它有效:

db.surveyResults.aggregate(
{$match: {'tenantId': 2}},
{$unwind: '$results'},
{$match: {'results.type':'integer'}},
{$project:{
"results.answer":1,
"results.name":1,
one:{$cond:[{$eq:["$results.answer","1"]},1,0]},
two:{$cond:[{$eq:["$results.answer","2"]},2,0]},
three:{$cond:[{$eq:["$results.answer","3"]},3,0]},
four:{$cond:[{$eq:["$results.answer","4"]},4,0]},
five:{$cond:[{$eq:["$results.answer","5"]},5,0]},
six:{$cond:[{$eq:["$results.answer","6"]},6,0]},
seven:{$cond:[{$eq:["$results.answer","7"]},7,0]},
eight:{$cond:[{$eq:["$results.answer","8"]},8,0]},
nine:{$cond:[{$eq:["$results.answer","9"]},9,0]},
}
},
{$project: {
"results.name":1,
sum:{$add:["$one","$two","$three","$four","$five","$six","$seven","$eight","$nine"]}
}},
{$group:
{_id: '$results.name', averageSatisfaction: {$avg: '$sum'}}


}

)

无论如何,最好的选择是使用整数作为整数。

关于MongoDB:计算作为列表嵌入文档中的所有特定文档字段的平均值,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/20653427/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com