Get only the document if a property exist in other document using Aggregate-CodePudding

So for example i have these items in my collection named borrow:

[
   {
      _id: ObjectId("628ebcc10944a1223397b057"),
      borrower_Id: "6278d1b6b4b7659470572e19",
      borrowedbook_Id: "62710ac63ad1bfc6d1703162",
      borrowStatus: "pending",
      borrowDate: ISODate("2022-05-25T23:33:21.849Z"),
      
    },
    {
      _id: ObjectId("628d9c0b9a3dc72f4aa72f1a"),
      borrower_Id: "6278d1b6b4b7659470572e19",
      borrowedbook_Id: "62710ac63ad1bfc6d170314d",
      borrowStatus: "pending",
      borrowDate: ISODate("2022-05-25T03:01:31.416Z"),
      
    }
 ]

and in this document i want to only get the items where the borrowedbook_Id is existing in other collection named books, How can i achieve this with aggregate method, I want to make sure that I only get the borrow records on books that is existing

books collection:

[
    {
        "_id": "62710ac63ad1bfc6d1703162",
        "title": "Birth of a Theorem",
        "author": "Villani, Cedric",
        "genre": "mathematics",
        "publisher": "Bodley Head",
        "dateOfPublication": "2002-02-28T00:00:00.000Z",
        "noOfCopies": 16,
        "type": "Book",
        "form": "Non-fiction",
        "isbn": "979-81202-479229-867673-6",
        "dateAdded": "2002-11-28T00:00:00.000Z"
    },
    {
        "_id": "62710ac63ad1bfc6d1703108",
        "title": "All the President's Men",
        "author": "Woodward, Bob",
        "genre": "history",
        "publisher": "Random House",
        "dateOfPublication": "2018-02-19T00:00:00.000Z",
        "noOfCopies": 56,
        "type": "Book",
        "form": "Non-fiction",
        "isbn": "978-41428-6606587-937631-",
        "dateAdded": "2011-02-23T00:00:00.000Z"
    },
]

if I try to query borrow.find({}) , the result should only be

 {
        "_id": "62710ac63ad1bfc6d1703162",
        "title": "Birth of a Theorem",
        "author": "Villani, Cedric",
        "genre": "mathematics",
        "publisher": "Bodley Head",
        "dateOfPublication": "2002-02-28T00:00:00.000Z",
        "noOfCopies": 16,
        "type": "Book",
        "form": "Non-fiction",
        "isbn": "979-81202-479229-867673-6",
        "dateAdded": "2002-11-28T00:00:00.000Z"
    }

since the other borrow record has its borrowedbook_Id missing on other collection

CodePudding user response：

It is not clear of you want to get the borrow record (written) or the book record (expected result example), but in both cases you can use a $lookup:

db.borrow.aggregate([
  {
    $lookup: {
      from: "books",
      let: {book_id: "$borrowedbook_Id"},
      pipeline: [
        {$match: {$expr: {$eq: ["$_id", "$$book_id"]}}},
        {$project: {_id: 1}}
      ],
      as: "existingBook"
    }
  },
  {
    $match: {$expr: {$gte: [{ $size: "$existingBook"}, 1]}}
  },
  {
    $unset: "existingBook"
  }
])

See how it works on the playground example