I'm new to mongoDB. Maybe this is a simple question. Kindly help me with this.

There are 2 tables books and bookStocks.

Table Books

{ 
    "_id" : "SHN-E3AE0DB3BC7D-4214-BDAE-59140DA1F850", 
    "nameOfBook" : "test", 
    "nameOfAuthor" : "test authou", 
    "yearOfPublication" : "1998", 
    "publication" : "test", 
    "edition" : "test", 
    "status" : "available", 
    "price" : "test", 
    "addedOn" : ISODate("2021-10-27T08:39:00.147 0000")
}

Table BookStocks

{ 
    "_id" : "SHNC-E1C6CF22A055-4A7E-AB79-CAEDC65AB29C", 
    "bookId" : "SHN-E3AE0DB3BC7D-4214-BDAE-59140DA1F850", 
    "collegeId" : "COL-D0E03FA4803F-4224-A321-1801626A9942", 
    "course" : "bcom", 
    "bookDetails" : "eeff", 
    "status" : "available", 
    "addedOn" : ISODate("2021-11-01T08:26:54.916 0000")
}

The only value I will be having is the collegeId. And this is how I need my result to be

[
  {
    "_id": "SHN-E3AE0DB3BC7D-4214-BDAE-59140DA1F850",
    "nameOfBook": "test",
    "nameOfAuthor": "test authou",
    "yearOfPublication": "1998",
    "publication": "test",
    "status": "available",
    "price": "test",
    "addedOn": "2021-10-27T08:39:00.147Z",
    "bookStocks": [
      {
        "_id": "SHNC-E1C6CF22A055-4A7E-AB79-CAEDC65AB29C",
        "bookId": "SHN-E3AE0DB3BC7D-4214-BDAE-59140DA1F850",
        "collegeId": "COL-D0E03FA4803F-4224-A321-1801626A9942",
        "course": "bcom",
        "bookDetails": "eeff",
        "status": "available",
        "addedOn": ISODate("2021-11-01T08:26:54.916 0000")
      },
      {
        "_id": "SHNC-E1C6CF22A055-4A7E-AB79-CAEDC65AB29D",
        "bookId": "SHN-E3AE0DB3BC7D-4214-BDAE-59140DA1F850",
        "collegeId": "COL-D0E03FA4803F-4224-A321-1801626A9942",
        "course": "bcom",
        "bookDetails": "eeff",
        "status": "available",
        "addedOn": ISODate("2021-11-01T08:26:54.916 0000")
      }
    ]
  }
]

This is the code I'm trying

  const pipeline = [
    { $match: { 'bookStocks.collegeId': collegeId } },
    {
      "$lookup": {
        "from": "bookStocks",
        let: {
          bookId: "$bookId"
        },
        pipeline: [
          {
            $match: {
              $expr: {
                $eq: [
                  "$$bookId",
                  "$bookId"
                ]
              }
            }
          }
        ],
        "as": "bookStocks"
      }
    }
  ];

I'm sure this is not correct. Can anyone help me with this? Thanks in advance.

CodePudding user response：

There are 2 corrections in your query,

• Move collegeId match condition to lookup's pipeline match stage
• Put a $match stage to filter empty/not match books by checking $ne condition

const pipeline = [
  {
    $lookup: {
      from: "bookStocks",
      let: { bookId: "$_id" },
      pipeline: [
        {
          $match: {
            $and: [
              { $expr: { $eq: ["$$bookId", "$bookId"] } },
              { collegeId: collegeId }
            ]
          }
        }
      ],
      as: "bookStocks"
    }
  },
  { $match: { bookStocks: { $ne: [] } } }
];

Playground

CodePudding user response：

you could use lookup and match

db.books.aggregate([
  {
    "$lookup": {
      "from": "bookStocks",
      "localField": "_id",
      "foreignField": "bookId",
      "as": "bookStocks"
    }
  },
  {
    $match: {
      "bookStocks.collegeId": "COL-D0E03FA4803F-4224-A321-1801626A9942"
    }
  },
  
])

https://mongoplayground.net/p/_jOZ1ZM-xgd