使用按2个ids分组的聚合查询创建结构

Question

我有一个类似于下面所示的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}
   })

它按topic和sentiment分组，但结构与上面的结构不匹配。我怎样才能得到类似的结构？

Answer 1

答案1

您需要2个$group阶段。

1. $match
2. $group -由comment.topic、comment.topic和$sum组成。
3. $group -由_id.topic，$sum组成的集团，并添加text_sentiment和total从前一阶段通过$push到text_sentiments。
4. $project -装饰输出文件。设置sentiment，通过$arrayToObject将text_sentiments数组转换为键值对.
5. $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
    }
  }
])

蒙戈游乐场样本(答案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值时，您必须修改查询。

蒙戈游乐场样本(答案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
    }
  }
])

蒙戈游乐场样本(答案3)