select from union select with order by and group by in mongo

Question

我尝试使用c#聚合框架重写下一个SQL查询到mongodb，但我不明白如何做到这一点。我需要合并一些结果

SELECT TOP 100 RES.Agent, RES.Type, RES.Opens FROM ((SELECT UA.ClientDomain AS Agent, UA.Type AS Type, COUNT(*) AS Opens 
                                                    FROM tReadConfirm AS RC INNER JOIN tUserAgent AS UA ON RC.UserAgentId = UA.Id
                                                    WHERE RC.UserId = 2654 AND RC.CampaignId = 27442  AND UA.Type = 1
                                                    GROUP BY UA.ClientDomain, UA.Type)
                                                UNION
                                                (SELECT UA.Family AS Agent, UA.Type AS Type, COUNT(*) AS Opens 
                                                    FROM tReadConfirm AS RC INNER JOIN tUserAgent AS UA ON RC.UserAgentId = UA.Id
                                                    WHERE RC.UserId = 2654 AND RC.CampaignId = 27442 AND UA.Type <> 1
                                                    GROUP BY UA.Family, UA.Type)) AS RES
                                                ORDER BY RES.Opens DESC

这是我的起始代码，但它不做我需要的

db.analytics.aggregate(
    {
        $match: { UserId: 4749, CampaignId: 93178}
    },
    {
        $group :
        {
            _id : 
            { 
                "Family" : "$userAgent.Family",
                "Type" : "$userAgent.Type",
                "ClientDomain" : "$userAgent.ClientDomain",
            }  ,
            "Opens": 
            {
                 $sum : 1 
            }
         }
     },
     {$sort :{"Opens":-1}}
)

Answer 1

我找到了答案，这是我的工作代码示例。操作符"$cond"帮了我的忙。

db.analytics.aggregate(
    { 
        $match : { 
            "UserId" : 4790, 
            "CampaignId" : 93178} 
     },
     {
        $group :
        {
            _id : 
            { 
                "Type" : "$userAgent.Type",
                "ClientDomain" : { $cond: { if: { $eq: [ "$userAgent.Type", 1 ] }, then: "$userAgent.ClientDomain", else: "$userAgent.Family" }},
            }  ,
            "Opens": 
            {
                 $sum : 1 
            }
         }
     },
     {
         $sort :{"Opens":-1}
     },
     {
         $limit:10
     }
)

Answer 2

您可以尝试以下想法:

1. 过滤器公共部分-你已经在$match stage
中完成了

2. 通过$project operator获取新的文档结构。比如

   {代理:[{代理:"userAgent美元。Family"， Index:0}， {Agent:"$userAgent. "$userAgent. ClientDomain"，索引:1}]，类型:"$userAgent. client "。类型"}

3. $unwind Agents字段，这样就可以得到2个文档而不是1个文档和2个元素数组。

4. 再次使用以下思路进行筛选:{$or: [{Index: 0, Type:1}， {Index:1, Type<>1}]}

5. 按Agent、Type、Index字段分组。

如果我没弄错的话，你会得到同样的结果。

EDIT:不幸的是，步骤2不起作用。MongoDB:如何创建一个新的数组字段与聚合框架