db.MEMBER.aggregate([
    {
        // Join with BORROWEDBOOKS to see who borrowed what
        $lookup: {
            from: "BORROWEDBOOKS",
            localField: "memberID",
            foreignField: "memberID",
            as: "borrowing_info"
        }
    },
    // Deconstruct the array to output a document for each borrowed book
    { $unwind: "$borrowing_info" },
    {
        // Join with BOOK to get the book titles based on ISBN
        $lookup: {
            from: "BOOK",
            localField: "borrowing_info.ISBN",
            foreignField: "ISBN",
            as: "book_info"
        }
    },
    { $unwind: "$book_info" },
    {
        // Select and format the specific fields requested
        $project: {
            _id: 0,
            memberID: 1,
            firstName: 1,
            lastName: 1,
            title: "$book_info.title",
            dateBorrowed: "$borrowing_info.dateBorrowed",
            dueDate: "$borrowing_info.dueDate",
            returnDate: "$borrowing_info.returnDate",
            fineAmount: "$borrowing_info.fineAmount"
        }
    }
]);