How to use findOne to filter by subdocument field using mongoose

[OlammieAO]

I am using findOne() to filter record base on the field of the nested subdocument but findOne() and find() are returning all the records instead of filtering it by the subdocument field.

WHAT I WANT TO ACHIEVE

I want to return the records that match only the subdocument field used in the query filter.

WHAT I HAVE TRIED

THE RETURNED DATA

  "importedBooks": {
      "_id": "674ed7fd61f952ddf3e4a661",
      "stakeholder": "stakeholder",
      "book_code": "test",
      "book_name": "Germain Marine Inc.",            
      "book_details": [
        {
          "isbn": "723936122",
          "shipper_name": "Timothy Lee",
          "user1": "ABC",
          "user2": "CDE",
          "user3": "FGH",          
        }
        ....
      ],

QUERY 1

const importedBooks = await Book.findOne({
  $and: [{ _id: "674ed7fd61f952ddf3e4a661" }, { "book_details.user2": "CDE" }],
});

QUERY 2

const importedBooks = await Book.findOne({
  $and: [{ _id: "674ed7fd61f952ddf3e4a661" }, { "book_details.user2": "CDE" }],
}).select({
  book_details: {
    $elemMatch: {
      user2,
    },
  },
});

QUERY 3

const importedBooks = await Book.findOne( {      
       $and:[
    {'_id': req.params.bookl_id},     
    {book_details: { $elemMatch: { user2: 'CDE'}}}   
    ]},   
  )   

Query 1 and Query 3 worked but they returned all the records in the subdocuments(user1, user2, user3) rather than just filtering the query by user2 but QUERY 2 did not work at all as it showed syntax error saying that "user2" is not defined(user2 that is referenced in the $elemMatch). I actually wanted to use aggregate but I am populating records from other Models with the find() and findOne() but I do not know how to populate fields from other Model with aggregation.

All the examples i have been seeing are using MongoDB but not Mongoose.

I will appreciate your kind assistance.

[vkarpov15]

Unfortunately not currently possible to filter for all the subdocuments that match. You can filter for just the first subdocument that matches using elemMatch in projection: https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch/

[OlammieAO]

Oh very disappointed to know that I cannot filter by subdocument field using Mongoose. I have tried everything i can to get it done but the filter applied through subdocument field isn't working as expected until I read that Model.find() only works on the first document.

Thank you for your time