我收集了類似于下面顯示為 3 個物件的各種檔案。
{
comment:{
text_sentiment: "positive",
topic: "A"
}
}, // DOC-1
{
comment:{
text_sentiment: "negative",
topic: "A"
}}, // DOC-2
{
comment:{
text_sentiment: "positive",
topic: "B"
}},..//DOC-3 ..
我想撰寫一個回傳結果的聚合，其結構如下：
{
topic: "A",
topicOccurance: 2,
sentiment: {
positive: 3,
negative: 2,
neutral: 0
}
},...
我撰寫了一個能夠分組的聚合，topic但text_sentiment我不知道如何創建類似于上面顯示的結構。這是我創建的聚合。
db.MyCollection.aggregate({
$match: {
_id: "xyz",
"comment.topic": {$exists: 1},
}
},{
$group: {
_id: {
topic: "$comment.topic",
text_sentiment: "$comment.text_sentiment"
},
total: {$sum: 1},
}
},{
$project: {
topic: {
name: "$_id.topic",
occurence: "$total"
},
sentiment: "$_id.text_sentiment"
}
},{
$sort: {"topic.occurence": -1}
})
它按topicand分組sentiment，但結構與上面的不匹配。我怎樣才能得到類似的結構？
uj5u.com熱心網友回復：
答案 1
你需要2個$group階段。
$match$group-按comment.topic和分組。comment.topic$sum$group- 分組_id.topic，$sum; 并將text_sentiment上total一階段的 and 添加到text_sentimentsvia$push中。$project- 裝飾輸出檔案。設定sentiment通過將text_sentiments陣列轉換為鍵值對$arrayToObject。$sort
db.collection.aggregate([
{
$match: {
_id: "xyz",
"comment.topic": {
$exists: 1
},
}
},
{
$group: {
_id: {
topic: "$comment.topic",
text_sentiment: "$comment.text_sentiment"
},
total: {
$sum: 1
},
}
},
{
$group: {
_id: "$_id.topic",
total: {
$sum: 1
},
text_sentiments: {
$push: {
k: "$_id.text_sentiment",
v: "$total"
}
}
}
},
{
$project: {
topic: "$_id",
topicOccurance: "$total",
sentiment: {
"$arrayToObject": "$text_sentiments"
}
}
},
{
$sort: {
"topicOccurance": -1
}
}
])
示例 Mongo Playground（答案 1）
答案 2
由于提到text_sentiment的值是固定的，您可以使用以下查詢：
db.collection.aggregate([
{
$match: {
_id: "xyz",
"comment.topic": {
$exists: 1
},
}
},
{
$group: {
_id: "$comment.topic",
total: {
$sum: 1
},
text_sentiments: {
$push: "$comment.text_sentiment"
}
}
},
{
$project: {
topic: "$_id",
topicOccurance: "$total",
sentiment: {
"positive": {
$reduce: {
input: "$text_sentiments",
initialValue: 0,
in: {
$sum: [
"$$value",
{
"$cond": {
"if": {
$eq: [
"$$this",
"positive"
]
},
"then": 1,
"else": 0
}
}
]
}
}
},
"negative": {
$reduce: {
input: "$text_sentiments",
initialValue: 0,
in: {
$sum: [
"$$value",
{
"$cond": {
"if": {
$eq: [
"$$this",
"negative"
]
},
"then": 1,
"else": 0
}
}
]
}
}
},
"neutral": {
$reduce: {
input: "$text_sentiments",
initialValue: 0,
in: {
$sum: [
"$$value",
{
"$cond": {
"if": {
$eq: [
"$$this",
"neutral"
]
},
"then": 1,
"else": 0
}
}
]
}
}
}
}
}
},
{
$sort: {
"topicOccurance": -1
}
}
])
缺點：text_sentiment添加/洗掉值時，您必須修改查詢。
示例 Mongo Playground（答案 2）
答案 3
另一種類似于答案 2 的方法是使用$size和$filter替換$reduce.
db.collection.aggregate([
{
$match: {
//_id: "xyz",
"comment.topic": {
$exists: 1
},
}
},
{
$group: {
_id: "$comment.topic",
total: {
$sum: 1
},
text_sentiments: {
$push: "$comment.text_sentiment"
}
}
},
{
$project: {
topic: "$_id",
topicOccurance: "$total",
sentiment: {
"positive": {
$size: {
$filter: {
input: "$text_sentiments",
cond: {
$eq: [
"$$this",
"positive"
]
}
}
}
},
"negative": {
$size: {
$filter: {
input: "$text_sentiments",
cond: {
$eq: [
"$$this",
"negative"
]
}
}
}
},
"neutral": {
$size: {
$filter: {
input: "$text_sentiments",
cond: {
$eq: [
"$$this",
"neutral"
]
}
}
}
},
}
}
},
{
$sort: {
"topicOccurance": -1
}
}
])
示例 Mongo Playground（答案 3）
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/485560.html
標籤：javascript 数据库 mongodb 聚合框架 聚合函数