Can Some one help me telling if the below query for multiple accounts is correct or not so that I can fetch the sum of "mt" for multiple accounts in one go from my collection?
I have tried the query as below,
db.getCollection('XYZ').aggregate([
{$match: {
$and:[{
$or: [
{"try.Ind":"A","_id.a":"BB","_id.b":"HXYZ","_id.c":"12345","_id.d":"FG","_id.e": new Date("2013-10-01T00:00:00.000Z")},
{"try.Ind":"A","_id.a":"AB","_id.b":"BBBT","_id.c":"23457","_id.d":"DA","_id.e":new Date("2013-10-01T00:00:00.000Z")}
]
}]
}
},
{$project:
{
"try": 1,
total:{
$sum:{
$let: {
vars: {
array:{
$filter:{
input:"$try",
cond:{$and:[{$eq:["$$this.Ind","A"]},{$gt:["$$this.mt",0.0]}]}
}
}},
"in":"$$array.mt"
}
}
}
}
}
])
MyCollection Data:
/* 1 */
{
"_id" : {
"a" : "NA",
"b" : "HXYZ",
"c" : "12345",
"d" : "CA",
"e" : "2018-03-09",
},
"try" : [
{
"Ind" : "A",
"mt" : 2.0,
},
{
"Ind" : "B",
"mt" : 3.0,
},
{
"Ind" : "A",
"mt" : 4.0,
},
{
"Ind" : "B",
"mt" : 5.0,
},
{
"Ind" : "A",
"mt" : 6.0,
},
{
"Ind" : "B",
"mt" : 7.0,
}
]
}
/* 2*/
{
"_id" : {
"a" : "AA",
"b" : "ACDE",
"c" : "45678",
"d" : "AB",
"e" : "2018-03-09",
},
"try" : [
{
"Ind" : "A",
"mt" : 2.0,
},
{
"Ind" : "B",
"mt" : 3.0,
},
{
"Ind" : "A",
"mt" : 4.0,
},
{
"Ind" : "B",
"mt" : 5.0,
},
{
"Ind" : "A",
"mt" : 6.0,
},
{
"Ind" : "B",
"mt" : 7.0,
}
]
}
/* 3 */
{
"_id" : {
"a" : "BB",
"b" : "BBBY",
"c" : "89012",
"d" : "BA",
"e" : "2004-02-12",
},
"try" : [
{
"Ind" : "A",
"mt" : 2.0,
},
{
"Ind" : "B",
"mt" : 3.0,
},
{
"Ind" : "A",
"mt" : 4.0,
},
{
"Ind" : "B",
"mt" : 5.0,
},
{
"Ind" : "A",
"mt" : 6.0,
},
{
"Ind" : "B",
"mt" : 7.0,
}
]
}
Expected Output:
if Ind="A" and mt>0.0
for account 12345,
2.0+4.0+6.0=12
for account 89012,
2.0+4.0+6.0=12
and so on...
output will be something lyk below,
{
{
"_id": {
"a" : "NA",
"b" : "HXYZ",
"c" : "12345",
"d" : "CA",
"e" : "2018-03-09",
},
"total": 12
},{ "_id": {
"a" : "BB",
"b" : "BBBY",
"c" : "89012",
"d" : "BA",
"e" : "2004-02-12",
},
"total" :12
}
}
Please correct me if the above query works for multiple accounts and get the sum for all accounts in one go from the collection?
I also need corresponding java code using aggregates for the above query especially the match criteria.
Thanks in advance!!!
