How to join 2 collections if the ID types in 1st collection is ObjectId and the foreign key type is string in mongodb

Question

I have 2 collections,1st is:

{
        "_id" : ObjectId("62eb5713ac2dccfb0a75d6c0"),
        "title" : "Agile Web Development with Rails",
        "categoryId" : ObjectId("62eb5713ac2dccfb0a75d6bf"),
        "subtitle" : "Dive into ES6 and the Future of JavaScript",
        "author" : "Sam Ruby, Dave Thomas, David Heinemeier Hansson",
        "published" : 2010,
        "publisher" : "O'Reilly Media",
        "isActive" : true,
        "isDelete" : false,
        "__v" : 0
}
{
        "_id" : ObjectId("62eb5777ac2dccfb0a75d6c3"),
        "title" : "Eloquent JavaScript, Third Edition",
        "categoryId" : ObjectId("62eb5777ac2dccfb0a75d6c2"),
        "subtitle" : "A Modern Introduction to Programming",
        "author" : "Marijn Haverbeke",
        "published" : 2018,
        "publisher" : "No Starch Press",
        "isActive" : true,
        "isDelete" : false,
        "__v" : 0
}
{
        "_id" : ObjectId("62eb5aa0e45707fec304e115"),
        "title" : "Eloquent JavaScript, Third Edition",
        "categoryId" : ObjectId("62eb41f088b1bc88e8a416db"),
        "subtitle" : "A Modern Introduction to Programming",
        "author" : "Marijn Haverbeke",
        "published" : 2018,
        "publisher" : "No Starch Press",
        "isActive" : true,
        "isDelete" : false,
        "__v" : 0
}
{
        "_id" : ObjectId("62ebaf20de65e74cd055565a"),
        "title" : "Rethinking Productivity in Software Engineering",
        "categoryId" : ObjectId("62eb420b88b1bc88e8a416df"),
        "subtitle" : "Everything you neeed to know about Gi",
        "author" : "Caitlin Sadowski, Thomas Zimmermann",
        "published" : 2019,
        "publisher" : "Apress",
        "isActive" : true,
        "isDelete" : false,
        "__v" : 0
}
{
        "_id" : ObjectId("62f08a12e0346e06e4bb7b06"),
        "categoryId" : ObjectId("62f08a12e0346e06e4bb7b05"),
        "isActive" : true,
        "isDelete" : false,
        "__v" : 0
}

2nd collection is:

{
        "_id" : ObjectId("62f3377166def37dee13f400"),
        "book_id" : "62eb5713ac2dccfb0a75d6c0",
        "description" : "for Agile Web..."
}
{
        "_id" : ObjectId("62f3383566def37dee13f401"),
        "book_id" : "62ebaf20de65e74cd055565a",
        "description" : "for Rethinking Productivity..."
}
{
        "_id" : ObjectId("62f3388666def37dee13f402"),
        "book_id" : "62eb5aa0e45707fec304e115",
        "description" : "for Eloquent JavaScript..."
}

I want to join 2 collections by use of aggregate $lookup,$match,$and,$ecpr,$eq and get the data in Mongo compass by using 'pipe line from text'. My code is:

[{$lookup:
({
  from: "books",
  let: {
    "bkid": new mongoose.Types.ObjectId('book_id')
  },
  pipeline: [{
    $match: {
      $and: {
        $expr: {
          $eq: ['$_id', '$$bkid']
        }
      }
    }
  }],
 as: "res"
})
}]

What is wrong here why its not showing output? I am doing this code in Mongodbaggregation -> CREATE -> Pipeline for Text. Thanks in advance.

Answer 1

    It can be possible by two ways i.e,
    1.by converting id type ObjectId to string

    db.books.aggregate({
        "$project": {
          "_id": {
            "$toString": "$_id"
          }
        }
      },
      {
        "$lookup": {
          "from": "description",
          "localField": "_id",
          "foreignField": "book_id",
          "as": "result"
        }
      })

    2.by converting string to ObjectId

db.description.aggregate([
    {
        $lookup: {
            from: "books",
            let: { bkid: {$convert: {input: '$book_id', to : 'objectId', onError: 'error',onNull: 'null'}} },
            pipeline: [{
                $match: {
                    $and: [
                        { $expr: { $eq: ['$_id', '$$bkid'] } }
                    ],
                }
            }],
            as: "result"
        }
    }
])