Combining result of two different Queries from two different Model MongoDB-CodePudding

So first I have a query that finds books that has been borrowed by user, it will search using the Borrow model

const bookqueryinitial = await Borrow.find({borrower_Id : String(_id), borrowStatus: req.query.status}).sort({"borrowDate": -1 }).skip(skip).limit(pageSize);

it will return results like this

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

next is I will map through the borrowedbook_Ids of the result and store them in an array

const booksinsidequery = bookqueryinitial.map(bookids=>{
      return bookids.borrowedbook_Id
    })

then I will search the ids that is stored in array and search for those ids in the Book model

 const bookquery = await Book.find({ '_id': { $in: booksinsidequery } });

\\and the result is somethign like this

 [
   {
    _id: new ObjectId("62710ac63ad1bfc6d170314d"),
     title: "Girl who kicked the Hornet's Nest",
     author: 'Larsson, Steig',
     genre: [ 'fiction' ],
     publisher: '',
     dateOfPublication: 2017-10-25T00:00:00.000Z,
     noOfCopies: 14,
     type: 'Article',
     form: 'Fiction',
     isbn: '978-69793-4824559-56755-9',
     dateAdded: 2003-04-23T00:00:00.000Z,
     noOfBookmarks: [ [Object] ],
     noOfLikes: [],

   },
   {
     _id: new ObjectId("62710ac63ad1bfc6d1703162"),
     title: 'We the Nation',
     author: 'Palkhivala',
     genre: [ 'philosophy' ],
     publisher: '',
     dateOfPublication: 2011-11-22T00:00:00.000Z,
     noOfCopies: 94,
     type: 'Book',
     form: 'Non-fiction',
     isbn: '978-65685-4156343-802140-8',
     dateAdded: 2010-06-08T00:00:00.000Z,
     noOfLikes: [],
     noOfBookmarks: []
   }
 ]

Now before sending the result of the query to the client side, I want to bind my initial queries from Borrow model to my Book model and the final result should be like this

 [
   {
    _id: new ObjectId("62710ac63ad1bfc6d170314d"),
     title: "Girl who kicked the Hornet's Nest",
     author: 'Larsson, Steig',
     genre: [ 'fiction' ],
     publisher: '',
     dateOfPublication: 2017-10-25T00:00:00.000Z,
     noOfCopies: 14,
     type: 'Article',
     form: 'Fiction',
     isbn: '978-69793-4824559-56755-9',
     dateAdded: 2003-04-23T00:00:00.000Z,
     noOfBookmarks: [ [Object] ],
     noOfLikes: [],

     //added properties based on matched condition  (Borrow.borrowedbook_Id === Book._id)
     borrowStatus: 'pending',
     borrowDate: 2022-05-25T03:01:31.416Z,
   },
   {
     _id: new ObjectId("62710ac63ad1bfc6d1703162"),
     title: 'We the Nation',
     author: 'Palkhivala',
     genre: [ 'philosophy' ],
     publisher: '',
     dateOfPublication: 2011-11-22T00:00:00.000Z,
     noOfCopies: 94,
     type: 'Book',
     form: 'Non-fiction',
     isbn: '978-65685-4156343-802140-8',
     dateAdded: 2010-06-08T00:00:00.000Z,
     noOfLikes: [],
     noOfBookmarks: [],
     
   //added properties based on matched condition  (Borrow.borrowedbook_Id === Book._id)
     borrowStatus: 'pending',
     borrowDate: 2022-05-25T23:33:21.849Z,
   }
 ]

How can I attain these results?

CodePudding user response：

You can achieve this with the aggregation framework that MongoDB provides.

Based on your data here is an example: https://mongoplayground.net/p/DQJIbcqBDKM

Lookup : the operator helps you to join data between different collections and the matched data will be in an array. In this case, called borrows
Unwind : will create a document per item on a specified array
Addfields : Allows you to create new attributes, in this case, the two that you wanted "borrowStatus" and "borrowDate"
Project : this operator allows you to hide or show data in the next stage. In this case, 0 means that we will hide a specific attribute