如何使用 Mongo 进行聚合并显示其他字段? [英] How to do group in aggregate but also show other fields using Mongo?

问题描述

我需要运行两次组才能找到评论中平均点赞数最高的帖子.以下是我查询的初始阶段.

I need to run group twice to find the post with the highest average likes in comments. Below is my initial stage of my query.

db.posts.aggregate([
    {"$unwind": "$comments"},
    {"$match":
        {
            "$comments.type": {
                "$ne" : "spam"
            },
        }
    }
])

这是我运行上面的查询后看到的.

This is what I see after running the query above.

    {
        "_id" : ObjectId("50b59cd75bed76f46522c465"),
        "comment_id" : 49,
        "post_id" : 29,
        "likes" : {
            "type" : "accepted",
            "like" : 3
        }
    },
    {
        "_id" : ObjectId("50b59cd75bed76f46522c465"),
        "comment_id" : 49,
        "post_id" : 29,
        "likes" : {
            "type" : "rejected",
            "like" : 7
        }
    }

我现在想要做的是首先从这些有效记录中找出特定评论的平均点赞数，然后在每个帖子中，将每个评论的所有这些平均点赞数相加，然后除以每个帖子的评论总数.

What I want to do now is find the average likes a particular comment gets out of these valid records first and then within each post, sum up all these average likes per comment and then divide by total number of comments each post has.

请注意，comment_id 仅在同一个 post_id 内是唯一的.意思是说，有post_id 28，comment_id 49的记录.

Note that the comment_id is only unique within same post_id. Meaning to say, there are records that are post_id 28, comment_id 49.

我试过这个查询.

db.posts.aggregate([
    {"$unwind": "$comments"},
    {"$match":
        {
            "$comments.type": {
                "$ne" : "spam"
            },
        }
    },
    {"$group" :
        {
            "_id": "$_id",
            "comment_avg":
            {
                "$avg":"$comments.like"
            }
        }
    }])

我得到以下信息:

{
            "_id" : ObjectId("50b59cd75bed76f46522c44d"),
            "comment_avg" : 61.074253191058865
        },
        {
            "_id" : ObjectId("50b59cd75bed76f46522c34e"),
            "comment_avg" : 46.82622896256565
        }

如您所见，我丢失了 post_id 信息.我试过 $project，但我想我一定是做错了.

As you can see I have lost the post_id information. I have tried $project, but I think I must be doing this wrong.

推荐答案

您尚未发布初始文档结构.

You have not posted you initial document structure.

Document Structure:

{
    "_id" : ObjectId("50b59cd75bed76f46522c471"),
    "comment_id" : 61,
    "post_id" : 29,
    "comments" : [
                   {
                       "type" : "accepted",
                       "like" : 3
                   },
                   {
                      "type" : "rejected",
                      "like" : 3
                   },
                   {
                      "type" : "spam",
                      "like" : 3
                   }
                ]
}

假设您的文档结构如上，我已经编写了此查询.您必须根据需要对其进行操作.

Assuming your document structure as above, i have composed this query. You have to manipulate it as per your needs.

db.posts.aggregate([
        {$unwind:"$comments"},
        {$match:{"$comments.type":{$ne:"spam"}}},
        {$group:{_id:{post_id:"$post_id",comment_id:"$comment_id"},LikeSum:{$sum:"$comments.like"}}},
        {$group:{_id:{post_id:"$_id.post_id"},AvgComments:{$avg:"$LikeSum"}}},
        {$sort:{AvgComments:-1}},
        {$limit:1}
              ])

上述查询的构造如下:

1.) Unwind the comments array and form individual documents for each element in the comments array
2.) Select only the non-spam comments
3.) Calculate the sum of likes for each comment of all posts
4.) Calculate the average Comment likes for each post
5.) Sort documents in descending order of Average Comment Likes
6.) Select only the first document.

输出文件类似于

{
    "result" : [
        {
            "_id" : {
                       "post_id" : xx
                    },
            "AvgComments" : xx.xx // Avg Comment likes for post xx
        }
               ],
    "ok" : 1
}

这篇关于如何使用 Mongo 进行聚合并显示其他字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！