代码之家 › 专栏 › 技术社区 › HIRA THAKUR

在MongoDB中按多个字段分组并格式化结果?

aggregation-framework mongodb-query mongodb

HIRA THAKUR · 技术社区 · 6 年前

我有一个像下面这样存储数据的集合。

{ 
    "_id" : ObjectId("5c52ba3177c895e98b3f41f7"), 
    "email" : "name@gmail.com", 
    "mobile" : "1234567890", 
    "userId" : "59b94212e4b0a459d044cd31", 
    "leadId" : "AEVUSSMS26B", 
    "leadName" : "LeadB", 
    "text" : "call client AEVUSSMS26B", 
    "reminderAt" : ISODate("2019-02-22T11:54:24.123+0000")
}
{ 
    "_id" : ObjectId("5c52bdc277c895e98b3f41f8"), 
    "email" : "name@gmail.com", 
    "mobile" : "1234567890", 
    "userId" : "59b94212e4b0a459d044cd31", 
    "leadId" : "AEVUSSMS26A", 
    "leadName" : "LeadA", 
    "text" : "call client AEVUSSMS26A", 
    "reminderAt" : ISODate("2019-02-22T11:55:24.123+0000")
}
{ 
    "_id" : ObjectId("5c52bdcd77c895e98b3f41f9"), 
    "email" : "name@gmail.com", 
    "mobile" : "1234567890", 
    "userId" : "59b94212e4b0a459d044cd31", 
    "leadId" : "AEVUSSMS26B",
    "leadName" : "LeadB",  
    "text" : "call client AEVUSSMS26B", 
    "reminderAt" : ISODate("2019-02-22T11:56:24.123+0000")
}
{ 
    "_id" : ObjectId("5c5403c7407b4aefc00dae39"), 
    "email" : "name@gmail.com", 
    "mobile" : "1234567890", 
    "userId" : "59b94212e4b0a459d044cd31", 
    "leadId" : "AEVUSSMS26C", 
    "leadName" : "LeadC", 
    "text" : "call client AEVUSSMS26C", 
    "reminderAt" : ISODate("2019-02-22T11:56:24.123+0000")
}
{ 
    "_id" : ObjectId("5c54040f407b4aefc00dae3a"), 
    "email" : "name@gmail.com", 
    "mobile" : "1234567890", 
    "userId" : "59b94212e4b0a459d044cd31", 
    "leadId" : "AEVUSSMS26C", 
    "leadName" : "LeadC", 
    "text" : "call client AEVUSSMS26C", 
    "reminderAt" : ISODate("2019-02-22T11:56:24.123+0000")
}
{ 
    "_id" : ObjectId("5c540426407b4aefc00dae3b"), 
    "email" : "name@gmail.com", 
    "mobile" : "1234567890", 
    "userId" : "59b94212e4b0a459d044cd31", 
    "leadId" : "AEVUSSMS26C",
    "leadName" : "LeadC",  
    "text" : "call client AEVUSSMS26C", 
    "reminderAt" : ISODate("2019-02-22T11:56:24.123+0000")
}
//I have 1000s such users

一个用户可以有多个潜在客户。一条线索可以有多个提醒。

我希望查询和格式化结果,使一个文档代表一个用户。每个文档都有一个前导字段,用于进一步存储所有提醒。我在等下面的事情:

{ 
    "userId" : "59b94212e4b0a459d044cd31", 
    "email":"name@gmail.com",
    "mobile":"1234567890"
    "leads" : [
        {
            "_id" : {
                "leadId" : "AEVUSSMS26C", 
                "leadName" : "LeadC"
            }, 
            "leadData" : [
                {
                    "_id" : ObjectId("5c5403c7407b4aefc00dae39"), 
                    "text" : "call client AEVUSSMS26C", 
                    "reminderAt" : ISODate("2019-02-22T11:56:24.123+0000")
                }, 
                {
                    "_id" : ObjectId("5c54040f407b4aefc00dae3a"), 
                    "text" : "call client AEVUSSMS26C", 
                    "reminderAt" : ISODate("2019-02-22T11:56:24.123+0000")
                }, 
                {
                    "_id" : ObjectId("5c540426407b4aefc00dae3b"), 
                    "text" : "call client AEVUSSMS26C", 
                    "reminderAt" : ISODate("2019-02-22T11:56:24.123+0000")
                }
            ]
        }, 
        {
            "_id" : {
                "leadId" : "AEVUSSMS26A", 
                "leadName" : "LeadA"
            }, 
            "leadData" : [
                {
                    "_id" : ObjectId("5c52bdc277c895e98b3f41f8"), 
                    "text" : "call client AEVUSSMS26A", 
                    "reminderAt" : ISODate("2019-02-22T11:55:24.123+0000")
                }
            ]
        }, 
        {
            "_id" : {
                "leadId" : "AEVUSSMS26B", 
                "leadName" : "LeadB"
            }, 
            "leadData" : [
                {
                    "_id" : ObjectId("5c52ba3177c895e98b3f41f7"), 
                    "text" : "call client AEVUSSMS26B", 
                    "reminderAt" : ISODate("2019-02-22T11:54:24.123+0000")
                }, 
                {
                    "_id" : ObjectId("5c52bdcd77c895e98b3f41f9"), 
                    "text" : "call client AEVUSSMS26B", 
                    "reminderAt" : ISODate("2019-02-22T11:56:24.123+0000")
                }
            ]
        }
    ]
}

这就是我迄今为止所做的。我面临的问题: 1.如何在根级别设置电子邮件和移动? 2.如何传递有限的数据作为响应而不是$$root?

db.getCollection("MyCollection").aggregate([
{ 
  "$group": {
        "_id": {
            "leadId": "$leadId",
            "userId": "$userId"   
        },
        "leadData": { $push: "$$ROOT" } 
    }
},
{ 
  "$group": {
        "_id": "$_id.userId",
        "leads": { 
            "$push": "$$ROOT",
        }
    }
}

])

1 回复 | 直到 6 年前

Ashh 6 年前

$first $group

db.collection.aggregate([
  { "$group": {
    "_id": { "leadId": "$leadId", "userId": "$userId", "leadName": "$leadName" },
    "email": { "$first": "$email" },
    "mobile": { "$first": "$mobile" },
    "leadData": {
      "$push": {
        "text": "$text",
        "_id": "$_id",
        "reminderAt": "$reminderAt"
      }
    }
  }},
  { "$group": {
    "_id": "$_id.userId",
    "email": { "$first": "$email" },
    "mobile": { "$first": "$mobile" },
    "leads": {
      "$push": {
        "_id": { "leadName": "$_id.leadName", "leadId": "$_id.leadId" },
        "leadData": "$leadData"
      }
    }
  }}
])