db.getCollection('raw_attom_recorder').aggregate([
{
$match: {"RecordingDate": {
$gte: ISODate("2018-01-01T00:00:00.000+0000")
}}
} ,
{ $sort : { AttomId : 1, RecordingDate: -1 }},
{ $group:{"_id":"$AttomId","recdata": { $first : "$$ROOT" }}
},
{
$lookup:
{
from: "raw_attom_taxassessor",
let: { attom_id: "$recdata.AttomId", rdate: "$recdata.RecordingDate" },
pipeline: [
{
$match:
{
$expr:
{
$and:
[
{ $eq: ["$AttomId", "$$attom_id"] }
]
}
}
}
],
as: "assessor"
}
},
{"$unwind": {
"path": "$assessor",
"preserveNullAndEmptyArrays": true
}
},
{ $sort : { "recdata.AttomId" : 1, "assessor.AssessorLastSaleDate" : -1 }},
{ $group:{"_id":"_id","data": { $first : "$$ROOT" }}},
{$project:{_id:0}},
{$out:"test_assessor"}
],{allowDiskUse:true})
I am joining two collections in mongo using $lookup. The 2nd collection has multiple records for each id in the first collection, therefore I want to sort the second collection by date and lookup only the record with latest date for every id in the first collection.
I have tried sorting and grouping the 2nd collection but I end up getting only one document after the entire query is run over both collections
I expect the output to have the same number of documents as in the first collection