CODEWITHSUNDEEP
node.jsmongodbexpress
jecorrales
jecorrales

Reputation: 270

How join two collections between Objects "_id" in MongoDB

regards, I am trying to merge two collections.

The point is that its only relationship is the ObjectId of the corresponding document, see:

PRODUCT COLLECTION

{
    "_id": Object(607a858c2db9a42d1870270f),
    "code":"CODLV001",
    "name":"Product Name",
    "category":"607a63e5778bf40cac75d863",
    "tax":"0",
    "saleValue":"0",
    "status":true
}

CATEGORY COLLECTION

{
    "_id": Object(607a63bf06e84e5240d377de),
    "name": "Diversey Care",
    "status": true
},
{
    "_id": Object(607a63e5778bf40cac75d863),
    "name": "Sani Tisu Profesional",
    "status": true
}

WHAT I'M DOING

.collection(collection)
        .aggregate([
          {
            $lookup: {
              from: 'categories',
              localField: 'products.category',
              foreignField: 'categories._id',
              as: 'category',
            },
          },
        ])
        .toArray();

WHAT AM I GETTING?

{
    _id: 607a858c2db9a42d1870270f,
    code: 'CODLV001',
    name: 'Product Name',
    category: [ [Object], [Object] ],
    tax: '0',
    saleValue: '0'
  }

WHAT I EXPECT?

{
        _id: 607a858c2db9a42d1870270f,
        code: 'CODLV001',
        name: 'Product Name',
        category: [ [Object] ], // or every field from category collection without an array object
        tax: '0',
        saleValue: '0'
      }

But, if I use this way, the category field is an empty array

{
$lookup: {
          from: 'categories',
          localField: 'category',
          foreignField: '_id', 
          as: 'category', 
        },
      },

So, what i'm doing wrong (just in case I'm new in the wolrd of MongoDB)?

Upvotes: 0

Views: 2358

Answers (1)

turivishal
turivishal

Reputation: 36094

There are few fixes in your query,

  • products collection field category is string type and categories field _id is objectId type so we need to convert it to objectId using $toObjectId
  • $lookup, pass localField as category and pass foreignField as _id, there is no need to concat collection name
.collection(collection).aggregate([
  {
    $addFields: {
      category: {
        $toObjectId: "$category"
      }
    }
  },
  {
    $lookup: {
      from: "categories",
      localField: "category",
      foreignField: "_id",
      as: "category"
    }
  }
]).toArray();

Playground

Upvotes: 2

Related Questions