MongoDB joining collection to itself with lookup and pipeline, based on date: no results in joined r-CodePudding

I'm trying to join a collection to itself so in one query I can receive the document and the document(s) immediately preceding it. I'm attempting to do this using aggregate and `$lookup'.

Here is some example data:

[
  {
    "_id": {
      "$oid": "61e40955cb65f4a8edec3461"
    },
    "ImageName": "2022-01-16-120228.png",
    "Start": {
      "$date": "2022-01-16T12:02:29.437Z"
    }
  },
  {
    "_id": {
      "$oid": "61e40957cb65f4a8edec3463"
    },
    "ImageName": "2022-01-16-120230.png",
    "Start": {
      "$date": "2022-01-16T12:02:31.443Z"
    }
  },
  {
    "_id": {
      "$oid": "61e4095acb65f4a8edec3469"
    },
    "ImageName": "2022-01-16-120233.png",
    "Start": {
      "$date": "2022-01-16T12:02:33.189Z"
    }
  },
  {
    "_id": {
      "$oid": "61e4095ccb65f4a8edec346b"
    },
    "ImageName": "2022-01-16-120235.png",
    "Start": {
      "$date": "2022-01-16T12:02:35.288Z"
    }
  }]

With each document in this collection I want to bring back the document immediately preceding it when ordered by the Start date, like this:

db.Captures.aggregate([{
  $lookup: {
    from: 'Captures',
    as: 'precedingCaptures',
    let: {
      'start': '$Start'
    },
    pipeline: [
      {
        $sort: {
          'Start': -1
        }
      },
      { $match: {
        'Start': {$lt: '$$start'}
      }},
      { '$limit': 1 }
    ]
  }
}])

However in my resulting document precedingCaptures is empty. What am I doing wrong?

CodePudding user response：

You cannot use the "simple" form of $match with pipeline $lookup. You must use the $expr form as noted here: https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/

This will work:

db.foo.aggregate([
    {$lookup: {
        from: 'foo',
        as: 'precedingCaptures',
        let: { 'start': '$Start'},
        pipeline: [
            { $match: {$expr: {$lt:['$Start','$$start']} }},  // $expr form
            { $sort: {'Start': -1}},
            { $limit: 1 }
        ]
    }}
]);