aggregation framework - Creating a view with non aggregate field in mongodb without putting it in _id field

Question

I have a collection with data given below

{
    "_id" : ObjectId("5fc8a58257f6526c6a520725"),
    "job_id" : ObjectId("5fc8a5822aed5820240c2893"),
    "access_id" : ObjectId("5eda0b2495b51c1d746db8b2"),
    "campaign_num" : "488707373",
    "partner_id" : ObjectId("5a94e02bb0445b1cc742d735"),
    "parent_partner" : ObjectId("5a8d70c4b0445b6a883fd395"),
    "status" : "A",
    "route" : "SR",
    "otp" : true,
    "trans" : false,
    "job_payload" : {
        "senderid" : "36",
        "msisdn" : "9671660999",
        "script" : "Your verification code is : 166297",
        "pingbackurl" : "https://xyztesting.com/sms/v1/notify/pr/index.php",
        "templateid" : "769",
        "smstype" : "2"
    },
    "track" : {
        "added" : {
            "date" : ISODate("2020-12-03T08:44:50.000Z")
        },
        "routed" : ISODate("2020-12-03T08:44:50.000Z")
    },
    "message_parts" : 1,
    "provider_status" : {
        "job_id" : "",
        "count" : {
            "total" : 1,
            "sent" : 0,
            "delivered" : 0,
            "failed" : 0
        },
        "delivery" : []
    }
}

I wanted to count all the totals,sent,delivered,failed where campaign_num and date were same . ie using the same campaign_num and on the same date. I created the view using below query

var pipeline = [
   {
      $group: {
         _id: {
            campaign: "$campaign_num",
            date: { $dateToString: { format: "%Y-%d-%m", date: "$track.added.date" } }
         },
         total: { $sum: "$provider_status.count.total" },
         sent: { $sum: "$provider_status.count.sent" },
         delivered: { $sum: "$provider_status.count.delivered" },
         failed: { $sum: "$provider_status.count.failed" }
      }
   }
] 
db.createView("sms_daily_test","col_outbox",pipeline)

I am getting the output as shown below

 "_id" : {
        "campaign" : "488707373",
        "date" : "2021-19-01"
    },
    "total" : 3,
    "sent" : 3,
    "delivered" : 0,
    "failed" : 0
}

Now the main problem is that I want to include partner_id and access_id fields also but not inside the _id. ie I want output to be something like this

"_id" : {
            "campaign" : "488707373",
            "date" : "2021-19-01"
        },
        "total" : 3,
        "sent" : 3,
        "delivered" : 0,
        "failed" : 0,
        "partner_id":ObjectId("5a94e02bb0445b1cc742d735"),
        "access_id":ObjectId("5a94e02bb0445b1cc742d635")
    }

I am using the below code for the purpose

var pipeline = [
   {
      $group: {
         _id: {
            campaign: "$campaign_num",
            date: { $dateToString: { format: "%Y-%d-%m", date: "$track.added.date" } }
         },
         partner_id: "$partner_id",
         access_id: "$access_id",
         total: { $sum: "$provider_status.count.total" },
         sent: { $sum: "$provider_status.count.sent" },
         delivered: { $sum: "$provider_status.count.delivered" },
         failed: { $sum: "$provider_status.count.failed" }
      }
   }
]   
db.createView("sms_daily_testing2","col_outbox",pipeline)

When I run it I get the following error message: The field 'partner_id' must be an accumulator object.

The information about this field is that a particular campaign will have only one partner_id and one access_id. Although a partner_id can have multiple campaigns and a access_id can also have multiple campaigns.

I need to group the data by campaigns and date field which I have done. I just need to know how to add these two fields without puting it in the inside the _id field.

question from:https://stackoverflow.com/questions/65840159/creating-a-view-with-non-aggregate-field-in-mongodb-without-putting-it-in-id-fi

Answer 1

You have written that a given campaign and date will have a unique partner_id and access_id. And we must use accumulator object outside of _id field. Since we have only one partner and access_id we can use $first or $last as shown :

 var pipeline=[{$group:{_id:{campaign:"$campaign_num",date:{$dateToString:{format:"%Y-%m-%d",date:"$track.added.date"}}},
        partner_id:{$first:"$partner_id"},
        access_id:{$first:"$access_id"},
        total: {$sum: "$provider_status.count.total"},
        sent:{$sum:"$provider_status.count.sent"},
        delivered:{$sum:"$provider_status.count.delivered"},
        failed:{$sum:"$provider_status.count.failed"}}
              }]