My data looks like:
[
{
"_id": ObjectId("6213baa5a013b7c5f1232e23"),
"birthDate": ISODate("1973-05-01T13:30:15Z"),
"surname": "Johnson",
"name": "Emma",
"registerDate": ISODate("1900-06-11T17:30:15Z"),
"children": [
{
"birthDate": ISODate("1993-05-21T16:30:15Z"),
"surname": "Johnson",
"name": "Liam"
},
{
"birthDate": ISODate("1994-01-21T15:30:15Z"),
"surname": "Johnson",
"name": "Olivia",
"registerDate": ISODate("2019-09-14T12:30:15Z")
}
],
"city": "Houston"
}
]
I want to calculate registration ages of persons who have registerDate. In this example, I should get average of registration ages of Emma(parent) and Olivia(child). I tried this.
I calculated the registration ages and added them as a field. I got Olivia's registration age. However, I have duplicate registration ages of Emma. I want to use one of them. How can I get average of registration ages which belong to Emma and Olivia. I mean (5 1)/2=3
. You can see these values in the link that added.
CodePudding user response:
You can first use $map
to get ages array. Then use $avg
to get average age.
$avg
ignores non-numeric values, including missing values. If all of the operands for the average are non-numeric,$avg
returnsnull
since the average of zero values isundefined
.
[
{
"$addFields": {
"ages": {
"$map": {
"input": "$children",
"as": "child",
"in": {
$dateDiff: {
startDate: "$$child.registerDate",
endDate: "$$NOW",
unit: "year"
}
}
}
}
}
},
{
"$addFields": {
averageAge: {
"$avg": {
"$concatArrays": [
"$ages",
[
{
$dateDiff: {
startDate: "$registerDate",
endDate: "$$NOW",
unit: "year"
}
}
]
]
}
}
}
}
]