How to supplement the information in the arrays of one collection with the information obtained from another collection?

[Gleb_Ivanov]

I have a storages collection and a products collection.
I need to supplement the information in the arrays with the products of the storages collection with the information obtained from the products collection.
Thanks in advance!

Products collection

[
{
  "_id": "6414ac1101f297b14f36ea42",
  "name": "Mens Casual Premium Slim Fit T-Shirts",
  "image": "https://fakestoreapi.com/img/71-3HjGNDUL._AC_SY879._SX._UX._SY._UY_.jpg",
  "category": "Men's clothing",
  "manufacturer": "Guleb Inc.",
  "manufacturerPrice": 22,
},
{
  "_id": "6414ad1601f297b14f36ea53",
  "name": "Fjallraven - Foldsack No. 1 Backpack, Fits 15 Laptops",
  "image": "https://fakestoreapi.com/img/81fPKd-2AYL._AC_SL1500_.jpg",
  "category": "Men's clothing",
  "manufacturer": "Guleb Inc.",
  "manufacturerPrice": 109,
},
{
  "_id": "6414ad2b01f297b14f36ea55",
  "name": "Mens Cotton Jacket",
  "image": "https://fakestoreapi.com/img/71li-ujtlUL._AC_UX679_.jpg",
  "category": "Men's clothing",
  "manufacturer": "Guleb Inc.",
  "manufacturerPrice": 55,
}
]

Storages collection

[
{
  "_id": "641364bf9cb7c37fab3b1f8f",
  "name": "Based",
  "products": [
    {
      "buyingPrice": 30,
      "sellingPrice": 40,
      "totalAmount": 5,
      "_id": "6414ac1101f297b14f36ea42"
    },
    {
      "buyingPrice": 120,
      "sellingPrice": 140,
      "totalAmount": 10,
      "_id": "6414ad1601f297b14f36ea53"
    }
  ],
]

Expected output

[
{
  "_id": "641364bf9cb7c37fab3b1f8f",
  "name": "Based",
  "products": [
    {
      "buyingPrice": 30,
      "sellingPrice": 40,
      "totalAmount": 5,
      "_id": "6414ac1101f297b14f36ea42",
      "name": "Mens Casual Premium Slim Fit T-Shirts",
      "image": "https://fakestoreapi.com/img/71-3HjGNDUL._AC_SY879._SX._UX._SY._UY_.jpg",
      "category": "Men's clothing",
      "manufacturer": "Guleb Inc.",
      "manufacturerPrice": 22,
    },
    {
      "buyingPrice": 120,
      "sellingPrice": 140,
      "totalAmount": 10,
      "_id": "6414ad1601f297b14f36ea53",
      "name": "Fjallraven - Foldsack No. 1 Backpack, Fits 15 Laptops",
      "image": "https://fakestoreapi.com/img/81fPKd-2AYL._AC_SL1500_.jpg",
      "category": "Men's clothing",
      "manufacturer": "Guleb Inc.",
      "manufacturerPrice": 109,
    }
  ],
]

[turivishal (Vishal Turi)]

Hello @Gleb_Ivanov, Welcome to the MongoDB community forum,

You can try something like this,

• $lookup to join with products collections, it will return all matching products in productDetails
• $project to show required properties
• $map to iterate loop of products array property
• $filter to iterate loop of productDetails array and find the matching product by checking _id property
• $first to get the first element from above filtered result, because it will result in the array, alternatively you can also use $arrayElemAt operator
• $mergeObjects to merge the current properties of the product and product details properties

db.storages.aggregate([
  {
    $lookup: {
      from: "products",
      localField: "products._id",
      foreignField: "_id",
      as: "productDetails"
    }
  },
  {
    $project: {
      name: 1,
      products: {
        $map: {
          input: "$products",
          as: "product",
          in: {
            $mergeObjects: [
              "$$product",
              {
                $first: {
                  $filter: {
                    input: "$productDetails",
                    cond: { $eq: ["$$this._id", "$$product._id"] }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

[Gleb_Ivanov]

Thank you very much for your answer! I spent a lot of time trying to do this, but I couldn’t. You are a true master at this.