I have a collection in which the documents sometimes contain a status field and sometimes don't. There is always a data field. I'm trying to form a query to get the latest value for both, but when using the $last operator, I get the values from the latest document and results in status sometimes being null. How can I get it to retrieve the latest defined status value, while still keeping the data value from the latest document?
Current aggregration:
const project = await collection.aggregate([
{
$match: {
projectId: id
}
},
{
$group: {
_id: '$projectId',
status: {
$last: '$status'
},
data: {
$last: '$data'
}
}
}
]).toArray();
You can use $facet and perform multiple query in the parallel on the same set of documents.
db.collection.aggregate([
{
$facet: {
last_status: [
{
"$match": {
status: {
$ne: null
}
}
},
{
"$sort": {
_id: -1
}
},
{
"$limit": 1
}
],
last_data: [
{
"$match": {
data: {
$ne: null
}
}
},
{
"$sort": {
_id: -1
}
},
{
"$limit": 1
}
]
}
},
{
"$project": {
other_fields: {
$first: "$last_data"
},
status: {
$first: "$last_status.status"
}
}
}
])