How to perform aggregate operation with $ cond to test for null values in one array when grouping
This is my document,
{
"_id": "58aecaa3758fbff4176db088",
"actualEndDate": "2017-02-27T00:00:00.000Z",
"Details": [
{
"projectId": "58a585f6758fbff4176dadb9",
}
]
},
{
"_id": "58aecac8758fbff4176db08b",
"actualEndDate": "2017-03-12T00:00:00.000Z",
"Details": [
{
"projectId": "58a585f6758fbff4176dadb9",
}
]
},
{
"_id": "58aecac8758fbff4176db08c",
"actualEndDate": null,
"Details": [
{
"projectId": "58a585f6758fbff4176dadb9",
}
]
}
I need to group them and in my query, I need to find $max
of "actualEndDate" with the condition if all fields of "actualEndDate" are not null.
I have tried something like this,
{
$group:
{
_id: '$Details.projectId',
actualEndDate:
{
$cond: {
if: { $ne: [ $actualEndDate, null] },
then: $actualEndDate, else: null
}
}
}
}
to get the expected result like
{
"projectId": "58a585f6758fbff4176dadb9",
"actualEndDate": null,
}
If the sample does not contain an entry { "_id": "58aecac8758fbff4176db08c" }
, I expect the result to have actualEndDate
at most two other documents
{
"projectId": "58a585f6758fbff4176dadb9",
"actualEndDate": "2017-03-12T00:00:00.000Z",
}
Any help is appreciated.
source to share
You can $sort
your field actualDate
, $unwind
your array Details
, and then $group
accepting only $first
, will ensure that this is the most recent date:
db.data.aggregate([{
$sort: {
"actualEndDate": -1
}
}, {
$unwind: "$Details"
}, {
$group: {
_id: "$Details.projectId",
actualEndDate: {
$first: "$actualEndDate"
}
}
}]);
If you want to return null date
if at least one date is null for the specified one projectId
, the following will do the job:
db.data.aggregate([{
$sort: {
"actualEndDate": -1
}
}, {
$unwind: "$Details"
}, {
$group: {
_id: "$Details.projectId",
dates: {
$push: "$actualEndDate"
}
}
}, {
$project: {
projectId: "$_id",
actualEndDate: {
$cond: {
if: {
$setIsSubset: [
[null], "$dates"
]
},
then: null,
else: { $arrayElemAt: ["$dates", 0] }
}
}
}
}]);
The idea is to $push
put all dates into a new array and make a projection to return null
when null
seen inside the array, otherwise it returns the first element of the array (with values sorted in the first step)
source to share