调查统计信息的Mongo shell查询（使用2D数组展开$）

我的文档结构（仅为该想法提供了2个）：

/* 1 */
{
    "_id" : ObjectId("59edc58af33e9b5988b875fa"),
    "Agent" : {
        "Name" : "NomanAgent",
        "Location" : "Lahore",
        "AgentId" : 66,
        "Suggestion" : [ 
            "Knowledge", 
            "Professionalisn"
        ]
    },
    "Rating" : 2,
    "Status" : "Submitted"
}
/* 2 */
 {
    "_id" : ObjectId("59edc58af33e9b5988b875fb"),
    "Agent" : {
        "Name" : "NomanAgent",
        "Location" : "Lahore",
        "AgentId" : 66,
        "Suggestion" : [ 
            "Knowledge", 
            "Clarity"
        ]
    },
    "Rating" : 1,
    "Status" : "Submitted"
}
/* 3 */
{
    "_id" : ObjectId("59edc58af33e9b5988b875fc"),
    "Agent" : {
        "Name" : "NomanAgent",
        "Location" : "Lahore",
        "AgentId" : 66,
        "Reward" : "Thumb Up"
    },
    "Rating" : 5,
    "Status" : "Submitted"
}

这些基本上是调查回复，因此代理对象可以包含建议（如果客户评价不好）或奖励（如果客户满意），那么我在这里显示2个带有建议的文档和1个带有奖励的文档 .

我已经为奖励创建了一个查询，如下所示，

db.getCollection('_survey.response').aggregate([
    {
        $group:{
            _id: "$Agent.Name",
            Rating: {$avg: "$Rating"},
            Rewards: {$push: "$Agent.Reward"},
            Status: {$push : "$Status"}
        }
    },
    {
        $unwind: "$Rewards"  
    },
    {
        $group:{
            _id: {
                Agent: "$_id",
                Rating: "$Rating",
                Rewards: "$Rewards"
            },
            RewardCount:{$sum: 1},
            SurveyStatus: {$first: "$Status"}

        }
    },
    {
        $group:{
            _id: "$_id.Agent",
            Rewards: {$push:{Reward: "$_id.Rewards", Count: "$RewardCount"}},
            Rating: {$first: "$_id.Rating"},
            SurveyStatus: {$first: "$SurveyStatus"}
        }
    },
    {
        $unwind: "$SurveyStatus"
    },
    {
        $group:{
            _id: {
                Agent: "$_id",
                Survey: "$SurveyStatus"
            },
            StatusCount:{$sum : 1},
            Rating: {$first: "$Rating"},
            Rewards: {$first: "$Rewards"}
        }
    },
    {
        $group:{
            _id: "$_id.Agent",
            Status:{$push:{Status: "$_id.Survey", Count: "$StatusCount"}},
            Rewards: {$first: "$Rewards"},
            Rating: {$first: "$Rating"}
        }
    },
    {
        $project:{
            _id: 0,
            Agent: "$_id",
            Rating: {
                $multiply:[
                    {$divide:["$Rating",5]},
                    100
                ]
            },
            Status: 1,
            Rewards: 1
        }
    }
]);

以上查询对于奖励完全正常，我想要完全相同的建议，如果可以在同一查询中调整建议，我会很高兴（我们也可以为建议创建单独的查询） .

上述查询的回复：

/* 1 */
{
    "Status" : [ 
        {
            "Status" : "Submitted",
            "Count" : 2.0
        }, 
        {
            "Status" : "Pending",
            "Count" : 1.0
        }, 
        {
            "Status" : "Opened",
            "Count" : 2.0
        }
    ],
    "Rewards" : [ 
        {
            "Reward" : "Thumb Up",
            "Count" : 1.0
        }, 
        {
            "Reward" : "Thank You",
            "Count" : 2.0
        }
    ],
    "Agent" : "GhazanferAgent",
    "Rating" : 68.0
}

/* 2 */
{
    "Status" : [ 
        {
            "Status" : "Opened",
            "Count" : 2.0
        }, 
        {
            "Status" : "Viewed",
            "Count" : 2.0
        }, 
        {
            "Status" : "Pending",
            "Count" : 3.0
        }
    ],
    "Rewards" : [ 
        {
            "Reward" : "Gift",
            "Count" : 1.0
        }, 
        {
            "Reward" : "Thumb Up",
            "Count" : 3.0
        }, 
        {
            "Reward" : "Thank You",
            "Count" : 1.0
        }
    ],
    "Agent" : "NomanAgent",
    "Rating" : 60.0
}

到目前为止我尝试过的，我想到了两种方法，但每种方法都有问题，

首先（在阵列中查找平均评级和推送状态和建议）：

db.getCollection("_survey.response").aggregate([
    {
        $match:
        {
            $and:[
                {
                    "Agent.Suggestion":{
                        $exists: true
                    }
                },    
                {
                    Rating: {$lte: 3}
                }
            ]

        }
    },
    {
        $group:{
            _id: {
                AgentName: "$Agent.Name",
                AgentId: "$Agent.AgentId",
                Location: "$Agent.Location"
            },
            Rating: {$avg: "$Rating"},
            Status: {$push : "$Status"},
            Suggestions: {$push: "$Agent.Suggestion"}
        }
    }
]);

使用这种方法面临的问题是，投影中的建议将成为动态大小的数组阵列（最初是一个数组），具体取决于代理在客户响应中获取建议的次数 . 所以问题是应用 $unwind on 2D array of dynamic size .

秒（$将第一阶段的建议解除为一维数组以避免动态大小的二维数组上的$ unwind问题）

db.getCollection("_survey.response").aggregate([
    {
        $match:
        {
            $and:[
                {
                    "Agent.Suggestion":{
                        $exists: true
                    }
                },    
                {
                    Rating: {$lte: 3}
                }
            ]

        }
    },
    {
        $unwind: "$Agent.Suggestion"
    },
    {
        $group: {
            _id:{
                AgentName: "$Agent.Name",
                AgentId: "$Agent.AgentId",
                Suggestion: "$Agent.Suggestion",
                Location: "$Agent.Location"
            },
            Status: {$push: "$Status"},
            Rating: {$avg: "$Rating"},
            Count: {$sum: 1}
        }
    }
]);

使用这种方法的问题是 $unwind Suggestion array 它会使各自的代理人的所有建议变得扁平化，从而增加了文件的数量（与原始答案相比） so i won't be able to find correct value for average rating for each agent on the basis of this grouping and the same will happen the Status(Because i can correctly find these two fields only if i group by agent. While, here i am grouping with agent along with suggestion) ，

我想要对Suggestion查询完全相同的响应，只有响应中的Rewards对象才会替换建议（或者，如果我们能够在同一响应中获得Suggestions对象，那就太棒了）

调查状态可以是，待处理，已打开，已查看，已提交等

输出说明：

我想为每个代理提供建议（有计数），状态（有计数）和％表格（我已经在做），正如你在上面提到的输出中看到的那样 .

提前致谢！！