MongoDB内部子集合上的聚合组，并获得带有计数的完整文档 [英] MongoDB aggregate group on inner child collection and get complete document with count

问题描述

我有一个用户集合，其中进一步有一个用户订阅"集合，其中还具有订阅>发布".

I have a User collection, which further have a 'UserSubscription' collection, which further have 'Subscription > Publication'.

Mongo收藏看起来像这样

Mongo collection looks like this

/* 1 */
{
    "_id" : 1,
    "UserSubscriptions" : [ 
    {
        "_id" : 1,
        "Subscription" : {
            "_id" : 1,
            "Publication" : {
                "_id" : 1,
                "Code" : "1MM",
            },
    },
    {
        "_id" : 2,
        "Subscription" : {
            "_id" : 2,
            "Publication" : {
                "_id" : 2,
                "Code" : "2MM",
            },      
    },
    {
        "_id" : 7,
        "Subscription" : {
            "_id" : 7,
            "Publication" : {
                "_id" : 1,
                "Code" : "1MM",
            },      
    }
]
}

/* 2 */
{
    "_id" : 2,
    "UserSubscriptions" : [ 
    {
        "_id" : 3,
        "Subscription" : {
            "_id" : 3,
            "Publication" : {
                "_id" : 1,
                "Code" : "1MM",
            }      
        }
    ]
}

/* 3 */
{
    "_id" : 3,
    "UserSubscriptions" : [ 
    {
        "_id" : 4,
        "Subscription" : {
            "_id" : 4,
            "Publication" : {
                "_id" : 1,
                "Code" : "1MM",
            }      
        }
    ]
}

/* 4 */
{
    "_id" : 4,
    "UserSubscriptions" : [ 
    {
        "_id" : 5,
        "Subscription" : {
            "_id" : 5,
            "Publication" : {
                "_id" : 2,
                "Code" : "2MM",
            }      
       }
   ]
}

我正在尝试获取所有出版物"和计数(用户已订阅订阅计数).所以从上面的收藏中我想要这样的结果

I'm trying to get all 'Publications' and the Count (User subscribed to Subscription count). So from above collection i want result like this

 PublicationCode      Count (Number of users)
 1MM                  3
 2MM                  2 

我尝试过以下MongoDB查询并获得结果

I have tried following MongoDB query and getting result

db.runCommand( {
   aggregate: "User",
   pipeline: [
     {$unwind: '$UserSubscriptions'},
     {$group: {_id: '$_id',pub: {$addToSet: 
                '$UserSubscriptions.Subscription.Publication'}}},
     {$unwind: '$pub'},
     {$group: {_id: '$pub.Code',pub:{$first:'$pub'}, count: {$sum: 1}}},
     {$project:{_id:0,"Publication":"$pub","count":1}}
 ]} )

对应的C#Mongo驱动程序代码为

Corresponding C# Mongo driver code is

 var unwind = new BsonDocument { { "$unwind", "$UserSubscriptions" } };
        var group1 = new BsonDocument
            {
                { "$group",
                    new BsonDocument
                        {
                            { "_id", "$_id"
                            },
                            {
                                "publications", new BsonDocument
                                             {
                                                 {
                                                     "$addToSet","$UserSubscriptions.Subscription.Publication"
                                                 }
                                             }
                            }
                        }
              }
            };
        var unwindCode = new BsonDocument { { "$unwind", "$publications" } };
        var group2 = new BsonDocument
            {
                { "$group",
                    new BsonDocument
                        {
                            { "_id", "$publications.Code"
                            },
                            {
                                "Publications", new BsonDocument
                                             {
                                                 {
                                                     "$first","$publications"
                                                 }
                                             }
                            },
                            {
                                "NumberOfUsers", new BsonDocument
                                             {
                                                 {
                                                     "$sum",1
                                                 }
                                             }
                            }
                        }
              }
            };

        var project = new BsonDocument
        {
            {
                "$project",
                new BsonDocument
                    {
                        {"_id", 0},
                        {"Publication","$Publications"},
                        {"NumberOfUsers", 1},
                    }
            }
        };

        var pipeline = new[] { unwind, group1, unwindCode, group2, project };
        List<BsonDocument> docs= coll.Aggregate<BsonDocument>(pipeline);

结果计数"在出版物"文档中正确无误. 但是查询需要一些时间才能返回结果.对于5万名用户记录，这大约需要23秒.

Resulted 'Count' is coming correct with 'Publication' document. But the query is taking some time to return the result. For 50K Users records it is taking around 23 secs.

请提出一些改进MongoDB查询性能的方法

Please advice some way to improve the performance of the MongoDB query

(Robo 3T 1.2.1)(MongoDB.Driver 2.4.4)

(Robo 3T 1.2.1) (MongoDB.Driver 2.4.4)

推荐答案

主要问题是您需要遍历整个集合(在这种情况下，您不能使用索引来加快处理速度).

The main problem is that you need to go through whole collection (in this situation you can't use the indexes for the process speed up).

您可以尝试以下代码(它比您的操作少):

You can try this code (it have less operation that your):

db.getCollection('User').aggregate([
    {
        $unwind: "$UserSubscriptions"
    },
    {
        $group: {
            _id:"$UserSubscriptions.Subscription.Publication.Code",
            users: {$addToSet: "$_id"}
        }
    },
    {
        $project: {"PublicationCode": "$_id", "Count": {$size: "$users"}}
    }
])

这篇关于MongoDB内部子集合上的聚合组，并获得带有计数的完整文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！