I am trying to use a combination of the $group and $project operators (unless there is a better alternative) to generate two values.
Consider a document data structure that looks like this:
[
{
_id: 1,
openBalance: 22,
customerResponsible: true
},
{
_id: 2,
openBalance: 16,
customerResponsible: false
},
{
_id: 3,
openBalance: 10,
customerResponsible: true
},
{
_id: 4,
openBalance: 20,
customerResponsible: false
},
]
What I'm trying to do is output a document with a value customerOpenBalance, which represents the total openBalance for documents where "customerResponsible" is true. And a value agencyOpenBalance, which represents the total openBalance where "customerResponsible" is set to false.
Now, I can do this in the $group stage:
"customerResponsibleOpenBalance" : { "$sum" : { "$cond" : [ "$customerResponsibile", 1, 0 ] }},
And then in a later $project stage do this:
"customerResponsibleOpenBalance" : { "$sum": "$customerResponsibleOpenBalance" }
But what this gives me is the $sum of the instances, rather than what I actually want, which is the $sum of the values found in openBalance, where, for the case of customerResponsibleOpenBalance, the boolean value customerResponsible I'm targeting is true.
How can I adjust this aggregation to get the $sum of the values found in $openBalance, rather than the $sum of the instances?
My full aggregation pipeline looks like this (the key stages are the $group in stage 6, and the $project, in stage 18:
db.transactions.aggregate(
// Pipeline
[
// Stage 1
{
$match: {
"openBalance": {
"$ne": 0.0
}
}
},
// Stage 2
{
$lookup: {
"from": "customers",
"localField": "customer.id",
"foreignField": "_id",
"as": "customer"
}
},
// Stage 3
{
$unwind: {
"path": "$customer"
}
},
// Stage 4
{
$lookup: {
"from": "visits",
"localField": "visit.id",
"foreignField": "_id",
"as": "visit"
}
},
// Stage 5
{
$unwind: {
"path": "$visit"
}
},
// Stage 6
{
$group: {
"_id": "$customer._id",
"submissions": {
"$push": "$submissions"
},
"staffMember": {
"$first": "$staffMember.id"
},
"visit": {
"$first": "$visit"
},
"openBalance": {
"$sum": "$openBalance"
},
"customerResponsibleOpenBalance": {
"$sum": {
"$cond": ["$visit.customerResponsibility", 1, 0]
}
},
"agencyResponsibleOpenBalance": {
"$sum": {
"$cond": ["$visit.customerResponsibility", 0, 1]
}
},
"openClaims": {
"$sum": {
"$cond": {
"if": {
"$gt": ["$submissions.0.responses.balance", 0.0]
},
"then": 1.0,
"else": 0.0
}
}
}
}
},
// Stage 7
{
$addFields: {
"payerInfo": "$submissions.0.details.agency._id"
}
},
// Stage 8
{
$lookup: {
"from": "agencies",
"localField": "submissions.0.details.agency._id",
"foreignField": "_id",
"as": "agency"
}
},
// Stage 9
{
$unwind: {
"path": "$agency"
}
},
// Stage 10
{
$addFields: {
"claimSupervisor": "$agency.claims.supervisor"
}
},
// Stage 11
{
$lookup: {
"from": "staffmembers",
"localField": "claimSupervisor",
"foreignField": "_id",
"as": "claimSupervisor"
}
},
// Stage 12
{
$unwind: {
"path": "$claimSupervisor"
}
},
// Stage 13
{
$lookup: {
"from": "customers",
"localField": "_id",
"foreignField": "_id",
"as": "customer"
}
},
// Stage 14
{
$unwind: {
"path": "$customer"
}
},
// Stage 15
{
$lookup: {
"from": "locales",
"localField": "customer.locale",
"foreignField": "_id",
"as": "locale"
}
},
// Stage 16
{
$unwind: {
"path": "$locale"
}
},
// Stage 17
{
$lookup: {
"from": "agencies",
"localField": "agencies",
"foreignField": "_id",
"as": "agencies"
}
},
// Stage 18
{
$project: {
"customer": {
"_id": 1.0,
"name": 1.0
},
"agency": {
"_id": 1.0,
"name": 1.0,
"transactionType": 1.0
},
"visit": {
"_id": 1.0,
"customerResponsibility": 1.0
},
"openBalance": 1.0,
"openClaims": 1.0,
"customerResponsibleOpenBalance": {
"$sum": "$customerResponsibleOpenBalance"
},
"agencyResponsibleOpenBalance": {
"$sum": "$agencyResponsibleOpenBalance"
},
"locale": {
"_id": 1.0,
"name": 1.0
},
"firstSubmission": {
"$arrayElemAt": ["$submissions", 0.0]
},
"lastSubmission": {
"$arrayElemAt": ["$submissions", -1.0]
},
"claimSupervisor": {
"_id": 1.0,
"name": 1.0
}
}
},
// Stage 19
{
$unwind: {
"path": "$firstSubmission"
}
},
// Stage 20
{
$unwind: {
"path": "$lastSubmission"
}
},
// Stage 21
{
$project: {
"firstSubmission": {
"details": 0.0,
"responses": 0.0
},
"lastSubmission": {
"details": 0.0,
"responses": 0.0
}
}
},
],
);
