Using conditions for both collections (original and foreign) in lookup $match-CodePudding

I'm not sure if it is a real problem or just lack of documentations. You can put conditions for documents in foreign collection in a lookup $match. You can also put conditions for the documents of original collection in a lookup $match with $expr.

But when I want to use both of those features, it doesn't work. This is sample lookup in aggregation

  { $lookup:
    {
      from: 'books',
      localField: 'itemId',
      foreignField: '_id',
      let: { "itemType": "$itemType" },
      pipeline: [
         { $match: { $expr: { $eq: ["$$itemType", "book"] } }}
      ],
      as: 'bookData'
    }
  }

$expr is putting condition for original documents. But what if I want to get only foreign documents with status: 'OK' ? Something like:

{ $match: { status: "OK", $expr: { $eq: ["$$itemType", "book"] } }}

Does not work.

CodePudding user response：

I tried to play with the situation you provided. Try to put $expr as the first key of $match object. And it should do the thing.

   { $lookup:
    {
      from: 'books',
      localField: 'itemId',
      foreignField: '_id',
      let: { "itemType": "$itemType" },
      pipeline: [
         { $match: { $expr: { $eq: ["$$itemType", "book"] }, status: 'OK' }}
      ],
      as: 'bookData'
    }
  }