Query different product prices where the reference id is the same in mongoose and nodejs

Question

I have documents (of a model called Products) with the following structure:

    {
       "_id" : ObjectId("646ebcd4c24c4fd5d050c5be"),
       "name" : "PRODUCT TEST",
    }

And I have other documents (of a model called Prices) with the following structure:

Note that the id in the above document matches the "productId" field in the following documents.

    {
      "_id" : ObjectId("646f8be5c86433269b82fc54"),
      "unitMeasurementId" : ObjectId("646eca3e2fb2ca526a322523"),
      "price" : 100,
      "productId" : ObjectId("646ebcd4c24c4fd5d050c5be")
    }

    {
      "_id" : ObjectId("646f8be5c86433269b82fc55"),
      "unitMeasurementId" : ObjectId("646eca3e2fb2ca526a322524"),
      "price" : 200,
      "productId" : ObjectId("646ebcd4c24c4fd5d050c5be")
    }

    {
      "_id" : ObjectId("646f8be5c86433269b82fc56"),
      "unitMeasurementId" : ObjectId("646eca3e2fb2ca526a322525"),
      "price" : 300,
      "productId" : ObjectId("646ebcd4c24c4fd5d050c5be")
    }

Lastly, I have these documents (of a model called Units of Measure) with the following structure:

Note that the "unitMeasurementId" field of the above documents matches the "_id" field (respectively) of the following documents.

  {
      "_id" : ObjectId("646eca3e2fb2ca526a322523"),
      "name" : "UNIT"
  }

  {
      "_id" : ObjectId("646eca3e2fb2ca526a322524"),
      "name" : "DOZEN"
  }

  {
      "_id" : ObjectId("646eca3e2fb2ca526a322525"),
      "name" : "HUNDRED"
  }

What I'm trying to do is query, in the Price model, all the documents where the "productId" field matches the "_id" of the first document displayed above (the product model document) and display them in the order of the documents in the Price model. model "Units of measure", that is, first the price that has the "unitMeasurementId" equal to the id: "646eca3e2fb2ca526a322523" (unit of measure "UNIT"), then the second price with the "unitMeasurementId" equal to the id: "646eca3e2fb2ca526a322524 " (unit of measure "DOZEN") and finally the price that has the "unitMeasurementId" equal to the id: "646eca3e2fb2ca526a322525" (unit of measure "HUNDRED").

However, there will be documents of the "Products" model that do not have a price with a previous unit of measure. For example, a product only has two prices with two units of measure, Unit and Hundred, so it does not have a record with the Unit of measure dozen, in that case, I would like to fill that space when querying, with NULL.

In the end, the result I want to get is the following:

"products": [
       {
            "_id": "646ebcd4c24c4fd5d050c5be",
            "name": "PRODUCT TEST",
            "prices": [
                {
                    "_id": "646f8be5c86433269b82fc54",
                    "unitMeasurementId": {
                        "_id": "646eca3e2fb2ca526a322523",
                        "name": "UNIT",
                    },
                    "price": 100,
                    "productId" : ObjectId("646ebcd4c24c4fd5d050c5be")
                },
                {
                    "_id": "646f8be5c86433269b82fc55",
                    "unitMeasurementId": {
                        "_id": "646eca3e2fb2ca526a322524",
                        "name": "DOZEN",
                    },
                    "price": 200,
                    "productId" : ObjectId("646ebcd4c24c4fd5d050c5be")
                },
                {
                    "_id": "646f8be5c86433269b82fc56",
                    "unitMeasurementId": {
                        "_id": "646eca3e2fb2ca526a322525",
                        "name": "HUNDRED",
                    },
                    "price": 300,
                    "productId" : ObjectId("646ebcd4c24c4fd5d050c5be")
                }
            ]
        },
        {
            "_id": "646ebcd4c24c4fd5d050c5bf",
            "name": "PRODUCT TEST 2",
            "prices": [
                {
                    "_id": "646f8be5c86433269b82fc57",
                    "unitMeasurementId": {
                        "_id": "646eca3e2fb2ca526a322523",
                        "name": "UNIT",
                    },
                    "price": 400,
                    "productId" : ObjectId("646ebcd4c24c4fd5d050c5be")
                },
               null   <----- Place null in case there is no price with that unit of measure
                {
                    "_id": "646f8be5c86433269b82fc59",
                    "unitMeasurementId": {
                        "_id": "646eca3e2fb2ca526a322525",
                        "name": "HUNDRED",
                    },
                    "price": 500,
                    "productId" : ObjectId("646ebcd4c24c4fd5d050c5be")
                }
            ]
        },
  ]

Place null in case there is no price with that unit of measure

I have tried the following, using the FOR statement, however, as the documents increase, the response time is higher, so I would like to know how to do it with pure queries with mongoose:

   const getPriceDetails = async (req, res) => {
    try {
        const productsIds = await PriceDetails.find({}).distinct('productId');

        const products = await Product.find({ _id: { $in: productsIds } }, { _id: true, name: true}).lean();

        const unitMeasurements = await UnitMeasurement.find({}).lean();


        for (const product of products) {
            const prices = await Prices.find({ productId: product._id, active: true }, { price: true, unitMeasurementId: true }).sort({ productId: 1 }).populate('unitMeasurementId');

            const c = unitMeasurements.map((i) => prices.find((j) => {
                return j.unitMeasurementId.name === i.name
            }));


            product.prices = c;
        }


        res.send({ products });
    } catch (e) {
        serverError(e, res);
    }

}

I attach an image of the frontend and how I handle said information:

Answer 1

Advanced joins inside mongoDB should be made using the mongoDB aggregation pipeline.

These perform better than doing the joins manually inside your normal code (as you did in your function getPriceDetails).

Here is a aggregation pipeline which returns the data you want:

{
   "aggregate": "products",
   "pipeline": [
      {
         "$lookup": {
            "from": "prices",
            "let": {
               "i": "$_id"
            },
            "pipeline": [
               {
                  "$match": {
                     "$expr": {
                        "$eq": [
                           "$productId",
                           "$$i"
                        ]
                     }
                  }
               },
               {
                  "$project": {
                     "unitMeasurementId": 1,
                     "price": 1
                  }
               }
            ],
            "as": "prices"
         }
      },
      {
         "$unwind": {
            "path": "$prices"
         }
      },
      {
         "$lookup": {
            "from": "units",
            "localField": "prices.unitMeasurementId",
            "foreignField": "_id",
            "as": "prices.unit"
         }
      },
      {
         "$unwind": {
            "path": "$prices.unit"
         }
      },
      {
         "$project": {
            "name": 1,
            "prices._id": 1,
            "prices.price": 1,
            "prices.unit": {
               "name": "$prices.unit.name",
               "_id": "$prices.unit._id"
            }
         }
      },
      {
         "$group": {
            "_id": {
               "name": "$name",
               "_id": "$_id"
            },
            "prices": {
               "$push": "$prices"
            }
         }
      },
      {
         "$project": {
            "_id": "$_id._id",
            "name": "$_id.name",
            "prices": "$prices"
         }
      },
      {
         "$limit": 10
      }
   ]
}

You will need to adapt the params limit and skip so you don't fetch all DB data at once for every request, but only what is needed.