In MongoDB I have:
Collection customers
with embedded array orders
:
db.customers.insert([
{_id: 1, name: "John", orders: [
{articleId: 1, quantity: 10},
{articleId: 2, quantity: 5}
]}]);
Collection articles
db.articles.insert([
{_id: 1, name: "computer"},
{_id: 2, name: "bike"}
]);
This question is not about design. I investigated a lot into the design, so that is fixed for this question. The collections customers
, orders
, articles
is not my real case. I just took it as an example.
I want this result:
{_id: 1, name: "John", orders: [
{articleId: 1, name: "computer", quantity: 10},
{articleId: 2, name: "bike", quantity: 5}
]}
I can get this result with the following query:
db.customers.aggregate([
{ $match: { "_id": 1 } },
{ $unwind: "$orders" },
{
$lookup: {
from: "articles",
localField: "orders.articleId",
foreignField: "_id",
as: "article"
}
},
{ $unwind: "$article" },
{ $addFields: { "orders.name": "$article.name" } },
{ $project: { article: 0 } },
{
$group: {
_id: "$_id",
name: { "$first": "$name" },
orders: { "$push": "$orders" }
}
}
]);
Is this the best possible query?
There are much more properties for every collection. And in case of a collection of 20 million customers
. customers
have an average of 50 orders
per customer
. And let say there are 80 articles
. Also this query is running every minute. So I need to use as less resources as possible.
Or is there a smarter query that has a better performance using less resources?