My Json:
[
{
"_id":{
"$oid":"60f5c60fbc43cb00965ac641"
},
"groupBy":{
"$oid":"60f5c60fbc43cb00965ac641"
},
"employer":{
"$oid":"60d0e4001a6ccd764cb26df4"
},
"parameters":{
"begin":"2020-01-01",
"end":"2020-12-31"
},
"recordCount":74,
"errorFields":[
{
"field":"employeeStatus",
"recordCount":62,
"errorDetails":[
{
"recordIds":[
"10000986",
"10000990",
"10001020"
],
"detail":"Active employees should not have term date. Termed employees should have term date.",
"recordCount":3
},
{
"recordIds":[
"10001032"
],
"detail":"Stale pay period data (no new check for over 30 days from queried end date)",
"recordCount":1
}
]
},
{
"field":"ytdGrossWages.ytdTotalGrossWages",
"recordCount":8,
"errorDetails":[
{
"recordIds":[
"10001211",
"10001269",
"10001328",
"10001395"
],
"detail":"YTD total does not equal sum of YTD wage buckets",
"recordCount":4
}
]
}
],
"timestamp":{
"$date":"2021-07-19T18:35:59.031Z"
},
"__v":0
},
{
"_id":{
"$oid":"60f5c615bc43cb00965ac647"
},
"groupBy":{
"$oid":"60f5c60fbc43cb00965ac641"
},
"employer":{
"$oid":"60d0e4001a6ccd764cb26df4"
},
"parameters":{
"begin":"2020-01-01",
"end":"2020-12-31"
},
"recordCount":11,
"errorFields":[
{
"field":"employeeStatus",
"recordCount":11,
"errorDetails":[
{
"recordIds":[
"10003644",
"10003680"
],
"detail":"Active employees should not have term date. Termed employees should have term date.",
"recordCount":2
},
{
"recordIds":[
"10003667",
"10003694",
"10003807",
"10003789"
],
"detail":"Stale pay period data (no new check for over 30 days from queried end date)",
"recordCount":4
}
]
},
{
"field":"ssn",
"recordCount":2,
"errorDetails":[
{
"recordIds":[
"10003667"
],
"detail":"The ssn field is required.",
"recordCount":1
},
{
"recordIds":[
"10003694"
],
"detail":"The ssn must be 9 digits.",
"recordCount":1
}
]
},
{
"field":"employeeHomeAddressCountry",
"recordCount":1,
"errorDetails":[
{
"recordIds":[
"10003694"
],
"detail":"The employeeHomeAddressCountry field is required.",
"recordCount":1
}
]
}
],
"timestamp":{
"$date":"2021-07-19T18:36:05.135Z"
},
"__v":0
}
]
I want output like below
{
"_id" : ObjectId("60f5c60fbc43cb00965ac641"),
"errorFields" : [
{
"field" : "employeeStatus",
"recordCount" : 73,
"errorDetails" : [
{
"recordIds" : [
"10001032",
"10003667",
"10003694",
"10003807",
"10003789"
],
"detail" : "Stale pay period data (no new check for over 30 days from queried end date)",
"recordCount" : 5
},
{
"recordIds" : [
"10000986",
"10000990",
"10001020",
"10001031",
"10001035"
],
"detail" : "Active employees should not have term date. Termed employees should have term date.",
"recordCount" : 5
}
]
},
{
"field" : "ytdGrossWages.ytdTotalGrossWages",
"recordCount" : 8,
"errorDetails" : [
{
"recordIds" : [
"10001211",
"10001269",
"10001328",
"10001395"
],
"detail" : "YTD total does not equal sum of YTD wage buckets",
"recordCount" : 8
}
]
},
{
"field" : "ssn",
"recordCount" : 2,
"errorDetails" : [
{
"recordIds" : [
"10003667"
],
"detail" : "The ssn field is required.",
"recordCount" : 1
},
{
"recordIds" : [
"10003694"
],
"detail" : "The ssn must be 9 digits.",
"recordCount" : 1
}
]
},
{
"field":"employeeHomeAddressCountry",
"recordCount":1,
"errorDetails":[
{
"recordIds":[
"10003694"
],
"detail":"The employeeHomeAddressCountry field is required.",
"recordCount":1
}
]
}
]
Here is the my code
db.collection.aggregate([
{ $match: { groupBy: ObjectId("60f5c60fbc43cb00965ac641") } },
{ "$project": {
"_id": "$groupBy",
"errorFields": { "$map": {
"input": "$errorFields",
"as": "ef",
"in": {
"field": "$ef.field",
"recordCount": {
$sum:"$ef.recordCount"
},
"errorDetails": { "$map": {
"input": "$ef.errorDetails",
"as": "ed",
"in": {
"detail": "$ed.detail",
"recordIds": { "$map": {
"input": "$ed.recordIds",
"as": "ri",
"in": {
$concat: [ "$ri"]
}
}},
"recordCount": {
$size:"$ed.recordIds"
}
}
}}
}
}}
}}
]).pretty()