Join collection through UUID using mongoose

Question

My project developed in express js. I am facing a problem since long time. I want to join 2 collections by user_id (from products ) and _id (from users). Please note that _id is UUID. It works for others field . But when I try to make relation with _id it returns empty array.

collection name is products

{
  "_id": {
    "$oid": "62414fc1d2564b4554hj0"
  },
  "amountIsFixed": false,
  "amount": 10,
  "active": false,
  "user_id": "62414fc1d24545456d4957ec"
}

collection name is users

{
  "_id": {
    "$oid": "62414fc1d24545456d4957ec"
  },
  "fullname": "Test"
}

My code base

const respPromise = Product.aggregate([
        
      {
        $lookup: {
          from: 'users',
          localField: 'user_id',
          foreignField: '_id',
          as: 'userDetails'
        }
      },
      {
  $unwind: "$userDetails"
   },
    {
        $project: { 
            fullname: "$userDetails.fullname",
            id: "$userDetails.id",
            amount: 1
        }
    }
      ]);

 let resp = await respPromise ;

In my mongo database collection related data exists. But it returns empty. I want to get related data. The reason that I am figured out "_id" . Because If I made relation with other field it's working.

How can I make relation with _id.

Thanks in advance

Answer 1

Your Product.user_id is of type String whereas your User._id is of type ObjectId.

This means that when you do the $lookup the localField: 'user_id' does not match any foreignField: '_id' because there is a type mismatch.

The best option is to redefine your Product schema so that user_id is an ObjectId. However, if you cannot do that then you can add a field in your aggregation that simply converts the user_id to an ObjectId and use that as the localField instead.

It might look like this:

const resp = await Product.aggregate([
  {
    $set: {
      user_ob_id: {
        $toObjectId: "$user_id" //< Convert here
      }
    }
  },
  {
    $lookup: {
      from: "users",
      localField: "user_ob_id", //< Use the new field in the lookup
      foreignField: "_id",
      as: "userDetails"
    }
  },
  {
    $unwind: "$userDetails"
  },
  {
    $project: {
      fullname: "$userDetails.fullname",
      id: "$userDetails.id",
      amount: 1
    }
  }
])

See HERE for a working example.

Note: in your sample data "62414fc1d2564b4554hj0" is not a valid ObjectId. Not sure if that was a typo when you pasted onto here.