为已经存在的集合创建一个对象

Question

我有一个集合"product_reviews"使用这个文档结构

{
_id: 'B000000OE4',
'product/title': 'Working Class Hero',
'product/price': '16.99',
reviews: [
{
'review/userId': 'unknown',
'review/profileName': 'unknown',
'review/helpfulness': '2/3',
'review/score': '4.0',
'review/time': '27/05/1999/00:00:00',
'review/summary': 'Worth it for one song',
'review/text': "I really like Joan Baez'..."
},
{
'review/userId': 'A1W0RKM6J6J73L',
'review/profileName': 'Aaron Woodin ([e-mail])',
'review/helpfulness': '1/1',
'review/score': '3.0',
'review/time': '09/02/1999/00:00:00',
'review/summary': 'The critical lambasting on the Amazon Page Missed one thing.',
'review/text': "They forgot to mention Mary Chapin..."
}, 
...
]
}

我的目标是为每个产品(每个产品都有唯一的_id)添加对象，该对象将具有以下结构:

{
avgReviewScore: 4.5
reviewsCount: 105
reviewScoreDistrib: {
1: 15
2:  0
3: 30
4: 40
5: 20
}
}

我尝试了许多聚合管道，但都没有找到解决方案。

Answer 1

你可以试试下面的代码:

db.product_reviews.aggregate([{
$unwind: "$reviews"
},
{
$group: {
_id: "$_id",
avgReviewScore: {
$avg: "$reviews.review/score"
},
reviewsCount: {
$sum: 1
},
scores: {
$push: "$reviews.review/score"
}
}
},
{
$project: {
avgReviewScore: 1,
reviewsCount: 1,
reviewScoreDistrib: {
$arrayToObject: {
$map: {
input: [1, 2, 3, 4, 5],
as: "num",
in: {
k: {$toString: "$$num"},
v: {
$size: {
$filter: {
input: "$scores",
as: "s",
cond: {
$eq: ["$$s", "$$num"]
}
}
}
}
}
}
}
}
}
},
{
$merge: {
into: "product_reviews",
on: "_id"
}
}
])

如果您有什么问题，可以问

Answer 2

不需要再次执行$unwind和$group(这可能非常低效)。您可以使用简单的updateMany:

db.collection.updateMany({},
[
{$set: {
reviewsData: {$map: {
input: "$reviews.review/score",
in: {$toDouble: "$$this"}
}}
}},
{$set: {
reviewScoreDistrib: {
$arrayToObject: {$map: {
input: {$range: [1, 6]},
as: "num",
in: {
k: {$toString: "$$num"},
v: {$size: {$filter: {
input: "$reviewsData",
cond: {$eq: ["$$this", "$$num"]}
}}}
}
}}
},
avgReviewScore: {$avg: "$reviewsData"},
reviewsCount: {$size: "$reviewsData"}
}}
])

看看它在操场的例子中是如何工作的