1

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

Manasa Tallam
  • 33
  • 1
  • 4

0 Answers0