I need to aggregate two MongoBD collections

Question

I need to aggregate from two tables into one, so that name from products

and id_item, quantity from customer

here is what node module do I use

  "dependencies": {
    "connect-mongo": "^5.0.0",
    "dotenv": "^16.0.3",
    "ejs": "^3.1.9",
    "express": "^4.18.2",
    "express-ejs-layouts": "^2.5.1",
    "express-flash": "^0.0.2",
    "express-flash-message": "^3.0.2",
    "express-session": "^1.17.3",
    "method-override": "^3.0.0",
    "mongoose": "^7.2.0",
    "passport": "^0.6.0",
    "passport-google-oauth20": "^2.0.0"
  },

Node.js version - 18.15.0.

Here's my pipeline

    const customerOrder = await Customer.aggregate([
      {
        $match: {
          _id: customerId,
        },
      },
      {
        $unwind: "$order",
      },
      {
        $lookup: {
          from: "products",
          localField: "order.id_item",
          foreignField: "_id",
          as: "item_details",
        },
      },
      {
        $project: {
          _id: 0,
          id_item: "$order.id_item",
          quantity: "$order.quantity",
          name: { $arrayElemAt: ["$item_details.name", 0] },
        },
      },
    ]);

product example

{
  "_id": { "$oid": "646fbe20d4372448890db84c" },
  "name": "Apple Magic Mouse",
  "category": "computer_peripherals",
  "type": "accessories",
  "price": { "$numberInt": "150" },
  "quantity": { "$numberInt": "700" }
}

customer example

{
  "_id": { "$oid": "646fb71cd437244889fe7404" },
  "firstName": "test",
  "lastName": "testtest",
  "tel": "test",
  "email": "test",
  "address": {
    "country": "test",
    "city": "test",
    "street": "test",
    "zip": "5489"
  },
  "createdAt": { "$date": { "$numberLong": "1684872074610" } },
  "updatedAt": { "$date": { "$numberLong": "1685046572891" } },
  "order": [
    {
      "id_item": "646fbe20d4372448890db84c",
      "quantity": { "$numberInt": "1" },
      "_id": { "$oid": "646fc52c58af0f7b907aaa1c" }
    },
    {
      "id_item": "646fbe20d4372448890db84d",
      "quantity": { "$numberInt": "2" },
      "_id": { "$oid": "646fc52c58af0f7b907aaa1d" }
    },
    {
      "id_item": "646fbe20d4372448890db84e",
      "quantity": { "$numberInt": "110" },
      "_id": { "$oid": "646fc52c58af0f7b907aaa1e" }
    },
    {
      "id_item": "646fbe20d4372448890db84f",
      "quantity": { "$numberInt": "220" },
      "_id": { "$oid": "646fc52c58af0f7b907aaa1f" }
    },
    {
      "id_item": "646fbe20d4372448890db850",
      "quantity": { "$numberInt": "330" },
      "_id": { "$oid": "646fc52c58af0f7b907aaa20" }
    },
    {
      "id_item": "646fc52c58af0f7b907aaa1a",
      "_id": { "$oid": "646fc52c58af0f7b907aaa21" }
    },
    {
      "id_item": "646fc52c58af0f7b907aaa1b",
      "_id": { "$oid": "646fc52c58af0f7b907aaa22" }
    }
  ]
}

This is what I get

[
  { id_item: '646fbe20d4372448890db84c', quantity: 1 },
  { id_item: '646fbe20d4372448890db84d', quantity: 2 },
  { id_item: '646fbe20d4372448890db84e', quantity: 0 },
  { id_item: '646fbe20d4372448890db84f', quantity: 0 },
  { id_item: '646fbe20d4372448890db850', quantity: 0 },
  { id_item: '646fc52c58af0f7b907aaa1a' },
  { id_item: '646fc52c58af0f7b907aaa1b' }
]

I also tried

    const customerOrder = await Customer.aggregate([
      {
        $lookup: {
          from: "products",
          localField: "order.id_item",
          foreignField: "_id",
          as: "productsNew",
        },
      },
    ]);

the output is

  {
    _id: new ObjectId("646fb71cd437244889fe7404"),
    firstName: 'fasdffasfasf',
    lastName: 'Important',
    tel: 'Important',
    email: 'Important',
    address: {
      country: 'Important',
      city: 'Important',
      street: 'Important',
      zip: '5489'
    },
    createdAt: 2023-05-23T20:01:14.610Z,
    updatedAt: 2023-05-25T20:29:32.891Z,
    order: [
      [Object], [Object],
      [Object], [Object],
      [Object], [Object],
      [Object]
    ],
    productsNew: []
  }

Here is the output I expect

[
  { name: "Apple Magic Mouse", id_item: '646fbe20d4372448890db84c', quantity: 1 },
  { name: "why it is not working??", id_item: '646fbe20d4372448890db84d', quantity: 2 },
  { name: "name name", id_item: '646fbe20d4372448890db84e', quantity: 0 },
  { name: "lorem name", id_item: '646fbe20d4372448890db84f', quantity: 0 },
  { name: "some name", id_item: '646fbe20d4372448890db850', quantity: 0 },
  { id_item: '646fc52c58af0f7b907aaa1a' },
  { id_item: '646fc52c58af0f7b907aaa1b' }
]

I've rewritten this pipeline many times, read the docs, asked chatGPT, notion.so, nothing helped

Answer 1

I have found the solution yesterday. Perhaps this will be useful to someone. the problem was that I was trying to compare String with ObjectId, I just create an id field in the product and everything will work

old product

{
  "_id": { "$oid": "646fbe20d4372448890db84c" },
  "name": "Apple Magic Mouse",
  "category": "computer_peripherals",
  "type": "accessories",
  "price": { "$numberInt": "150" },
  "quantity": { "$numberInt": "700" }
}

new product

{
  "_id": { "$oid": "646fbe20d4372448890db84c" },
  "id": "646fbe20d4372448890db84c",
  "name": "Apple Magic Mouse",
  "category": "computer_peripherals",
  "type": "accessories",
  "price": { "$numberInt": "150" },
  "quantity": { "$numberInt": "700" }
}

And noow I get this output

[

  {
    id_item: '646fbe20d4372448890db84c',
    quantity: 1,
    name: 'Apple Magic Mouse'
  },
  { id_item: '646fbe20d4372448890db84d', quantity: 2 },
  { id_item: '646fbe20d4372448890db84e', quantity: 0 },
  { id_item: '646fbe20d4372448890db84f', quantity: 0 },
  { id_item: '646fbe20d4372448890db850', quantity: 0 },
  { id_item: '646fc52c58af0f7b907aaa1a' },
  { id_item: '646fc52c58af0f7b907aaa1b' }
]

So I need to modify all products.