Populate + Aggregate on Array
I am using mongoose (5.x.x) with the populate function to populate an array (meals) with refs.
Within that array I need to multiply a price (result of the populate) and a quantity (part of the base Schema).
My populate result as below:
{
"_id": "5bea354235711482876f8fa8",
"meals": [
{
"meal": {
"_id": "5be93c7074488c77b10fba00",
"name": "Chicken Nuggets",
"price": 3
},
"quantity": 12
},
{
"meal": {
"_id": "5be93c9274488c77b10fba01",
"name": "Beef Burger",
"price": 6
},
"quantity": 4
}
],
"__v": 0
}
The goal would be to add a "total price" within this result set but I can't find any elegant way to do so.
I'd like to avoid manipulating data outside of the query.
Thanks for your help,
node.js mongodb mongoose
asked Nov 13 '18 at 2:57
Atheryl
12228
add a comment |
I am using mongoose (5.x.x) with the populate function to populate an array (meals) with refs.
Within that array I need to multiply a price (result of the populate) and a quantity (part of the base Schema).
My populate result as below:
{
"_id": "5bea354235711482876f8fa8",
"meals": [
{
"meal": {
"_id": "5be93c7074488c77b10fba00",
"name": "Chicken Nuggets",
"price": 3
},
"quantity": 12
},
{
"meal": {
"_id": "5be93c9274488c77b10fba01",
"name": "Beef Burger",
"price": 6
},
"quantity": 4
}
],
"__v": 0
}
The goal would be to add a "total price" within this result set but I can't find any elegant way to do so.
I'd like to avoid manipulating data outside of the query.
Thanks for your help,
node.js mongodb mongoose
asked Nov 13 '18 at 2:57
Atheryl
12228
1
You cannot mixpopulate()andaggregate(). There is instead$lookupto use in an aggregation pipeline instead of callingpopulate()altogether. No code here, and without it we can only point you to generic examples.
– Neil Lunn
Nov 13 '18 at 2:59
2
Alternately, just do thepopulate()withlean()and manipulate the result object in JavaScript code. A simpleArray.reduce()over the array content should do the job.
– Neil Lunn
Nov 13 '18 at 3:03
1
I should have mentioned I would like to achieve this through mongodb rather that manipulating in Javascript. I find the code alternative not so elegant.
– Atheryl
Nov 13 '18 at 3:06
add a comment |
I am using mongoose (5.x.x) with the populate function to populate an array (meals) with refs.
Within that array I need to multiply a price (result of the populate) and a quantity (part of the base Schema).
My populate result as below:
{
"_id": "5bea354235711482876f8fa8",
"meals": [
{
"meal": {
"_id": "5be93c7074488c77b10fba00",
"name": "Chicken Nuggets",
"price": 3
},
"quantity": 12
},
{
"meal": {
"_id": "5be93c9274488c77b10fba01",
"name": "Beef Burger",
"price": 6
},
"quantity": 4
}
],
"__v": 0
}
The goal would be to add a "total price" within this result set but I can't find any elegant way to do so.
I'd like to avoid manipulating data outside of the query.
Thanks for your help,
node.js mongodb mongoose
asked Nov 13 '18 at 2:57
Atheryl
12228
I am using mongoose (5.x.x) with the populate function to populate an array (meals) with refs.
Within that array I need to multiply a price (result of the populate) and a quantity (part of the base Schema).
My populate result as below:
{
"_id": "5bea354235711482876f8fa8",
"meals": [
{
"meal": {
"_id": "5be93c7074488c77b10fba00",
"name": "Chicken Nuggets",
"price": 3
},
"quantity": 12
},
{
"meal": {
"_id": "5be93c9274488c77b10fba01",
"name": "Beef Burger",
"price": 6
},
"quantity": 4
}
],
"__v": 0
}
The goal would be to add a "total price" within this result set but I can't find any elegant way to do so.
I'd like to avoid manipulating data outside of the query.
Thanks for your help,
node.js mongodb mongoose
node.js mongodb mongoose
asked Nov 13 '18 at 2:57
Atheryl
12228
asked Nov 13 '18 at 2:57
Atheryl
12228
edited Nov 13 '18 at 3:10
asked Nov 13 '18 at 2:57
Atheryl
12228
asked Nov 13 '18 at 2:57
Atheryl
12228
asked Nov 13 '18 at 2:57
Atheryl
12228
12228
1
You cannot mixpopulate()andaggregate(). There is instead$lookupto use in an aggregation pipeline instead of callingpopulate()altogether. No code here, and without it we can only point you to generic examples.
– Neil Lunn
Nov 13 '18 at 2:59
2
Alternately, just do thepopulate()withlean()and manipulate the result object in JavaScript code. A simpleArray.reduce()over the array content should do the job.
– Neil Lunn
Nov 13 '18 at 3:03
1
I should have mentioned I would like to achieve this through mongodb rather that manipulating in Javascript. I find the code alternative not so elegant.
– Atheryl
Nov 13 '18 at 3:06
add a comment |
1
You cannot mixpopulate()andaggregate(). There is instead$lookupto use in an aggregation pipeline instead of callingpopulate()altogether. No code here, and without it we can only point you to generic examples.
– Neil Lunn
Nov 13 '18 at 2:59
2
Alternately, just do thepopulate()withlean()and manipulate the result object in JavaScript code. A simpleArray.reduce()over the array content should do the job.
– Neil Lunn
Nov 13 '18 at 3:03
1
I should have mentioned I would like to achieve this through mongodb rather that manipulating in Javascript. I find the code alternative not so elegant.
– Atheryl
Nov 13 '18 at 3:06
1
1
You cannot mix
populate() and aggregate(). There is instead $lookup to use in an aggregation pipeline instead of calling populate() altogether. No code here, and without it we can only point you to generic examples.– Neil Lunn
Nov 13 '18 at 2:59
You cannot mix
populate() and aggregate(). There is instead $lookup to use in an aggregation pipeline instead of calling populate() altogether. No code here, and without it we can only point you to generic examples.– Neil Lunn
Nov 13 '18 at 2:59
2
2
Alternately, just do the
populate() with lean() and manipulate the result object in JavaScript code. A simple Array.reduce() over the array content should do the job.– Neil Lunn
Nov 13 '18 at 3:03
Alternately, just do the
populate() with lean() and manipulate the result object in JavaScript code. A simple Array.reduce() over the array content should do the job.– Neil Lunn
Nov 13 '18 at 3:03
1
1
I should have mentioned I would like to achieve this through mongodb rather that manipulating in Javascript. I find the code alternative not so elegant.
– Atheryl
Nov 13 '18 at 3:06
I should have mentioned I would like to achieve this through mongodb rather that manipulating in Javascript. I find the code alternative not so elegant.
– Atheryl
Nov 13 '18 at 3:06
add a comment |
1 Answer
1
active
oldest
votes
So there's a couple of ways to do this as mentioned.
Using $lookup
You basically want to get the "related" data from the other collection and "merge" that with the existing array items. You cannot actually just "target" the existing array since $lookup cannot do that, but it can write another array and then you can "merge" them together:
let result1 = await Order.aggregate([
{ "$lookup": {
"from": Meal.collection.name,
"foreignField": "_id",
"localField": "meals.meal",
"as": "mealitems"
}},
{ "$project": {
"meals": {
"$map": {
"input": "$meals",
"in": {
"meal": {
"$arrayElemAt": [
"$mealitems",
{ "$indexOfArray": [ "$mealitems._id", "$$this.meal" ] }
]
},
"quantity": "$$this.quantity",
"totalPrice": {
"$multiply": [
{ "$arrayElemAt": [
"$mealitems.price",
{ "$indexOfArray": [ "$mealitems._id", "$$this.meal" ] }
]},
"$$this.quantity"
]
}
}
}
}
}},
{ "$addFields": {
"totalOrder": {
"$sum": "$meals.totalPrice"
}
}}
]);
That basically produces another array "mealitems" as the result of $lookup and then uses $map in order to process through the original document array and transpose the returned content array items back into the structure for each item.
You do that in combination with $arrayElemAt and $indexOfArray to find the matched items to transpose here.
There is also some "math" for the other computed elements using $multiply, and even an additional $addFields stage using $sum to "add those up" to give an overall "order total" for the document.
You "could" just do all that math in the $project stage ( which is used because we don't want the "mealitems" content. But that's a little more involved and you probably want to use $let for the array matching so you don't repeat your code so much.
You can even use the "sub-pipeline" form of $lookup if you really want to. Instead of using $map as the operations to alter the returned documents are done "inside" the returned array before the results are returned, by transposing the initial document array into the result documents via it's let argument:
// Aggregate with $lookup - sub-pipeline
let result2 = await Order.aggregate([
{ "$lookup": {
"from": Meal.collection.name,
"let": { "meals": "$meals" },
"pipeline": [
{ "$match": {
"$expr": {
"$in": [ "$_id", "$$meals.meal" ]
}
}},
{ "$replaceRoot": {
"newRoot": {
"meal": "$$ROOT",
"quantity": {
"$arrayElemAt": [
"$$meals.quantity",
{ "$indexOfArray": [ "$$meals.meal", "$_id" ] }
]
},
"totalPrice": {
"$multiply": [
{ "$arrayElemAt": [
"$$meals.quantity",
{ "$indexOfArray": [ "$$meals.meal", "$_id" ] }
]},
"$price"
]
}
}
}}
],
"as": "meals"
}},
{ "$addFields": {
"totalOrder": {
"$sum": "$meals.totalPrice"
}
}}
]);
In either form, that's basically an allegory for what populate() is doing under the hood by "merging" the content, but of course that uses separate database requests where the $lookup aggregation is just one request.
Using populate()
Alternately you can just manipulate the resulting structure in JavaScript. It's already there, and all you really need is the lean() in order to be able to alter the resulting objects:
// Populate and manipulate
let result3 = await Order.find().populate('meals.meal').lean();
result3 = result3.map(r =>
({
...r,
meals: r.meals.map( m =>
({
...m,
totalPrice: m.meal.price * m.quantity
})
),
totalOrder: r.meals.reduce((o, m) =>
o + (m.meal.price * m.quantity), 0
)
})
);
It looks pretty simple and is basically the same thing, with the exceptions that the "merging" was already done for you and that of course this is two requests to the server in order to return all the data.
As a reproducible full listing:
const { Schema } = mongoose = require('mongoose');
// Connection
const uri = 'mongodb://localhost:27017/menu';
const opts = { useNewUrlParser: true };
// Sensible defaults
mongoose.Promise = global.Promise;
mongoose.set('useFindAndModify', false);
mongoose.set('useCreateIndex', true);
mongoose.set('debug', true);
// Schema defs
const mealSchema = new Schema({
name: String,
price: Number
});
const orderSchema = new Schema({
meals: [
{
meal: { type: Schema.Types.ObjectId, ref: 'Meal' },
quantity: Number
}
]
});
const Meal = mongoose.model('Meal', mealSchema);
const Order = mongoose.model('Order', orderSchema);
// log helper
const log = data => console.log(JSON.stringify(data, undefined, 2));
// main
(async function() {
try {
const conn = await mongoose.connect(uri, opts);
// clean models
await Promise.all(
Object.entries(conn.models).map(([k,m]) => m.deleteMany())
);
// Set up data
let [Chicken, Beef] = await Meal.insertMany(
[
{ name: "Chicken Nuggets", price: 3 },
{ name: "Beef Burger", price: 6 }
]
);
let order = await Order.create({
meals: [
{ meal: Chicken, quantity: 12 },
{ meal: Beef, quantity: 4 }
]
});
// Aggregate with $lookup - traditional
let result1 = await Order.aggregate([
{ "$lookup": {
"from": Meal.collection.name,
"foreignField": "_id",
"localField": "meals.meal",
"as": "mealitems"
}},
{ "$project": {
"meals": {
"$map": {
"input": "$meals",
"in": {
"meal": {
"$arrayElemAt": [
"$mealitems",
{ "$indexOfArray": [ "$mealitems._id", "$$this.meal" ] }
]
},
"quantity": "$$this.quantity",
"totalPrice": {
"$multiply": [
{ "$arrayElemAt": [
"$mealitems.price",
{ "$indexOfArray": [ "$mealitems._id", "$$this.meal" ] }
]},
"$$this.quantity"
]
}
}
}
}
}},
{ "$addFields": {
"totalOrder": {
"$sum": "$meals.totalPrice"
}
}}
]);
log(result1);
// Aggregate with $lookup - sub-pipeline
let result2 = await Order.aggregate([
{ "$lookup": {
"from": Meal.collection.name,
"let": { "meals": "$meals" },
"pipeline": [
{ "$match": {
"$expr": {
"$in": [ "$_id", "$$meals.meal" ]
}
}},
{ "$replaceRoot": {
"newRoot": {
"meal": "$$ROOT",
"quantity": {
"$arrayElemAt": [
"$$meals.quantity",
{ "$indexOfArray": [ "$$meals.meal", "$_id" ] }
]
},
"totalPrice": {
"$multiply": [
{ "$arrayElemAt": [
"$$meals.quantity",
{ "$indexOfArray": [ "$$meals.meal", "$_id" ] }
]},
"$price"
]
}
}
}}
],
"as": "meals"
}},
{ "$addFields": {
"totalOrder": {
"$sum": "$meals.totalPrice"
}
}}
]);
log(result2);
// Populate and manipulate
let result3 = await Order.find().populate('meals.meal').lean();
result3 = result3.map(r =>
({
...r,
meals: r.meals.map( m =>
({
...m,
totalPrice: m.meal.price * m.quantity
})
),
totalOrder: r.meals.reduce((o, m) =>
o + (m.meal.price * m.quantity), 0
)
})
);
log(result3);
} catch(e) {
console.error(e);
} finally {
mongoose.disconnect();
}
})()
Which returns results like:
Mongoose: meals.deleteMany({}, {})
Mongoose: orders.deleteMany({}, {})
Mongoose: meals.insertMany([ { _id: 5bea4c8f6edcd22d385a13bf, name: 'Chicken Nuggets', price: 3, __v: 0 }, { _id: 5bea4c8f6edcd22d385a13c0, name: 'Beef Burger', price: 6, __v: 0 } ], {})
Mongoose: orders.insertOne({ _id: ObjectId("5bea4c8f6edcd22d385a13c1"), meals: [ { _id: ObjectId("5bea4c8f6edcd22d385a13c3"), meal: ObjectId("5bea4c8f6edcd22d385a13bf"), quantity: 12 }, { _id: ObjectId("5bea4c8f6edcd22d385a13c2"), meal: ObjectId("5bea4c8f6edcd22d385a13c0"), quantity: 4 } ], __v: 0 })
Mongoose: orders.aggregate([ { '$lookup': { from: 'meals', foreignField: '_id', localField: 'meals.meal', as: 'mealitems' } }, { '$project': { meals: { '$map': { input: '$meals', in: { meal: { '$arrayElemAt': [ '$mealitems', { '$indexOfArray': [ '$mealitems._id', '$$this.meal' ] } ] }, quantity: '$$this.quantity', totalPrice: { '$multiply': [ { '$arrayElemAt': [ '$mealitems.price', { '$indexOfArray': [Array] } ] }, '$$this.quantity' ] } } } } } }, { '$addFields': { totalOrder: { '$sum': '$meals.totalPrice' } } } ], {})
[
{
"_id": "5bea4c8f6edcd22d385a13c1",
"meals": [
{
"meal": {
"_id": "5bea4c8f6edcd22d385a13bf",
"name": "Chicken Nuggets",
"price": 3,
"__v": 0
},
"quantity": 12,
"totalPrice": 36
},
{
"meal": {
"_id": "5bea4c8f6edcd22d385a13c0",
"name": "Beef Burger",
"price": 6,
"__v": 0
},
"quantity": 4,
"totalPrice": 24
}
],
"totalOrder": 60
}
]
Mongoose: orders.aggregate([ { '$lookup': { from: 'meals', let: { meals: '$meals' }, pipeline: [ { '$match': { '$expr': { '$in': [ '$_id', '$$meals.meal' ] } } }, { '$replaceRoot': { newRoot: { meal: '$$ROOT', quantity: { '$arrayElemAt': [ '$$meals.quantity', { '$indexOfArray': [ '$$meals.meal', '$_id' ] } ] }, totalPrice: { '$multiply': [ { '$arrayElemAt': [ '$$meals.quantity', [Object] ] }, '$price' ] } } } } ], as: 'meals' } }, { '$addFields': { totalOrder: { '$sum': '$meals.totalPrice' } } } ], {})
[
{
"_id": "5bea4c8f6edcd22d385a13c1",
"meals": [
{
"meal": {
"_id": "5bea4c8f6edcd22d385a13bf",
"name": "Chicken Nuggets",
"price": 3,
"__v": 0
},
"quantity": 12,
"totalPrice": 36
},
{
"meal": {
"_id": "5bea4c8f6edcd22d385a13c0",
"name": "Beef Burger",
"price": 6,
"__v": 0
},
"quantity": 4,
"totalPrice": 24
}
],
"__v": 0,
"totalOrder": 60
}
]
Mongoose: orders.find({}, { projection: {} })
Mongoose: meals.find({ _id: { '$in': [ ObjectId("5bea4c8f6edcd22d385a13bf"), ObjectId("5bea4c8f6edcd22d385a13c0") ] } }, { projection: {} })
[
{
"_id": "5bea4c8f6edcd22d385a13c1",
"meals": [
{
"_id": "5bea4c8f6edcd22d385a13c3",
"meal": {
"_id": "5bea4c8f6edcd22d385a13bf",
"name": "Chicken Nuggets",
"price": 3,
"__v": 0
},
"quantity": 12,
"totalPrice": 36
},
{
"_id": "5bea4c8f6edcd22d385a13c2",
"meal": {
"_id": "5bea4c8f6edcd22d385a13c0",
"name": "Beef Burger",
"price": 6,
"__v": 0
},
"quantity": 4,
"totalPrice": 24
}
],
"__v": 0,
"totalOrder": 60
}
]
answered Nov 13 '18 at 3:43
Neil Lunn
97.1k22170181
Absolutely perfectly clear answer. Thanks a lot for all the details.
– Atheryl
Nov 13 '18 at 4:14
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53273127%2fpopulate-aggregate-on-array%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
So there's a couple of ways to do this as mentioned.
Using $lookup
You basically want to get the "related" data from the other collection and "merge" that with the existing array items. You cannot actually just "target" the existing array since $lookup cannot do that, but it can write another array and then you can "merge" them together:
let result1 = await Order.aggregate([
{ "$lookup": {
"from": Meal.collection.name,
"foreignField": "_id",
"localField": "meals.meal",
"as": "mealitems"
}},
{ "$project": {
"meals": {
"$map": {
"input": "$meals",
"in": {
"meal": {
"$arrayElemAt": [
"$mealitems",
{ "$indexOfArray": [ "$mealitems._id", "$$this.meal" ] }
]
},
"quantity": "$$this.quantity",
"totalPrice": {
"$multiply": [
{ "$arrayElemAt": [
"$mealitems.price",
{ "$indexOfArray": [ "$mealitems._id", "$$this.meal" ] }
]},
"$$this.quantity"
]
}
}
}
}
}},
{ "$addFields": {
"totalOrder": {
"$sum": "$meals.totalPrice"
}
}}
]);
That basically produces another array "mealitems" as the result of $lookup and then uses $map in order to process through the original document array and transpose the returned content array items back into the structure for each item.
You do that in combination with $arrayElemAt and $indexOfArray to find the matched items to transpose here.
There is also some "math" for the other computed elements using $multiply, and even an additional $addFields stage using $sum to "add those up" to give an overall "order total" for the document.
You "could" just do all that math in the $project stage ( which is used because we don't want the "mealitems" content. But that's a little more involved and you probably want to use $let for the array matching so you don't repeat your code so much.
You can even use the "sub-pipeline" form of $lookup if you really want to. Instead of using $map as the operations to alter the returned documents are done "inside" the returned array before the results are returned, by transposing the initial document array into the result documents via it's let argument:
// Aggregate with $lookup - sub-pipeline
let result2 = await Order.aggregate([
{ "$lookup": {
"from": Meal.collection.name,
"let": { "meals": "$meals" },
"pipeline": [
{ "$match": {
"$expr": {
"$in": [ "$_id", "$$meals.meal" ]
}
}},
{ "$replaceRoot": {
"newRoot": {
"meal": "$$ROOT",
"quantity": {
"$arrayElemAt": [
"$$meals.quantity",
{ "$indexOfArray": [ "$$meals.meal", "$_id" ] }
]
},
"totalPrice": {
"$multiply": [
{ "$arrayElemAt": [
"$$meals.quantity",
{ "$indexOfArray": [ "$$meals.meal", "$_id" ] }
]},
"$price"
]
}
}
}}
],
"as": "meals"
}},
{ "$addFields": {
"totalOrder": {
"$sum": "$meals.totalPrice"
}
}}
]);
In either form, that's basically an allegory for what populate() is doing under the hood by "merging" the content, but of course that uses separate database requests where the $lookup aggregation is just one request.
Using populate()
Alternately you can just manipulate the resulting structure in JavaScript. It's already there, and all you really need is the lean() in order to be able to alter the resulting objects:
// Populate and manipulate
let result3 = await Order.find().populate('meals.meal').lean();
result3 = result3.map(r =>
({
...r,
meals: r.meals.map( m =>
({
...m,
totalPrice: m.meal.price * m.quantity
})
),
totalOrder: r.meals.reduce((o, m) =>
o + (m.meal.price * m.quantity), 0
)
})
);
It looks pretty simple and is basically the same thing, with the exceptions that the "merging" was already done for you and that of course this is two requests to the server in order to return all the data.
As a reproducible full listing:
const { Schema } = mongoose = require('mongoose');
// Connection
const uri = 'mongodb://localhost:27017/menu';
const opts = { useNewUrlParser: true };
// Sensible defaults
mongoose.Promise = global.Promise;
mongoose.set('useFindAndModify', false);
mongoose.set('useCreateIndex', true);
mongoose.set('debug', true);
// Schema defs
const mealSchema = new Schema({
name: String,
price: Number
});
const orderSchema = new Schema({
meals: [
{
meal: { type: Schema.Types.ObjectId, ref: 'Meal' },
quantity: Number
}
]
});
const Meal = mongoose.model('Meal', mealSchema);
const Order = mongoose.model('Order', orderSchema);
// log helper
const log = data => console.log(JSON.stringify(data, undefined, 2));
// main
(async function() {
try {
const conn = await mongoose.connect(uri, opts);
// clean models
await Promise.all(
Object.entries(conn.models).map(([k,m]) => m.deleteMany())
);
// Set up data
let [Chicken, Beef] = await Meal.insertMany(
[
{ name: "Chicken Nuggets", price: 3 },
{ name: "Beef Burger", price: 6 }
]
);
let order = await Order.create({
meals: [
{ meal: Chicken, quantity: 12 },
{ meal: Beef, quantity: 4 }
]
});
// Aggregate with $lookup - traditional
let result1 = await Order.aggregate([
{ "$lookup": {
"from": Meal.collection.name,
"foreignField": "_id",
"localField": "meals.meal",
"as": "mealitems"
}},
{ "$project": {
"meals": {
"$map": {
"input": "$meals",
"in": {
"meal": {
"$arrayElemAt": [
"$mealitems",
{ "$indexOfArray": [ "$mealitems._id", "$$this.meal" ] }
]
},
"quantity": "$$this.quantity",
"totalPrice": {
"$multiply": [
{ "$arrayElemAt": [
"$mealitems.price",
{ "$indexOfArray": [ "$mealitems._id", "$$this.meal" ] }
]},
"$$this.quantity"
]
}
}
}
}
}},
{ "$addFields": {
"totalOrder": {
"$sum": "$meals.totalPrice"
}
}}
]);
log(result1);
// Aggregate with $lookup - sub-pipeline
let result2 = await Order.aggregate([
{ "$lookup": {
"from": Meal.collection.name,
"let": { "meals": "$meals" },
"pipeline": [
{ "$match": {
"$expr": {
"$in": [ "$_id", "$$meals.meal" ]
}
}},
{ "$replaceRoot": {
"newRoot": {
"meal": "$$ROOT",
"quantity": {
"$arrayElemAt": [
"$$meals.quantity",
{ "$indexOfArray": [ "$$meals.meal", "$_id" ] }
]
},
"totalPrice": {
"$multiply": [
{ "$arrayElemAt": [
"$$meals.quantity",
{ "$indexOfArray": [ "$$meals.meal", "$_id" ] }
]},
"$price"
]
}
}
}}
],
"as": "meals"
}},
{ "$addFields": {
"totalOrder": {
"$sum": "$meals.totalPrice"
}
}}
]);
log(result2);
// Populate and manipulate
let result3 = await Order.find().populate('meals.meal').lean();
result3 = result3.map(r =>
({
...r,
meals: r.meals.map( m =>
({
...m,
totalPrice: m.meal.price * m.quantity
})
),
totalOrder: r.meals.reduce((o, m) =>
o + (m.meal.price * m.quantity), 0
)
})
);
log(result3);
} catch(e) {
console.error(e);
} finally {
mongoose.disconnect();
}
})()
Which returns results like:
Mongoose: meals.deleteMany({}, {})
Mongoose: orders.deleteMany({}, {})
Mongoose: meals.insertMany([ { _id: 5bea4c8f6edcd22d385a13bf, name: 'Chicken Nuggets', price: 3, __v: 0 }, { _id: 5bea4c8f6edcd22d385a13c0, name: 'Beef Burger', price: 6, __v: 0 } ], {})
Mongoose: orders.insertOne({ _id: ObjectId("5bea4c8f6edcd22d385a13c1"), meals: [ { _id: ObjectId("5bea4c8f6edcd22d385a13c3"), meal: ObjectId("5bea4c8f6edcd22d385a13bf"), quantity: 12 }, { _id: ObjectId("5bea4c8f6edcd22d385a13c2"), meal: ObjectId("5bea4c8f6edcd22d385a13c0"), quantity: 4 } ], __v: 0 })
Mongoose: orders.aggregate([ { '$lookup': { from: 'meals', foreignField: '_id', localField: 'meals.meal', as: 'mealitems' } }, { '$project': { meals: { '$map': { input: '$meals', in: { meal: { '$arrayElemAt': [ '$mealitems', { '$indexOfArray': [ '$mealitems._id', '$$this.meal' ] } ] }, quantity: '$$this.quantity', totalPrice: { '$multiply': [ { '$arrayElemAt': [ '$mealitems.price', { '$indexOfArray': [Array] } ] }, '$$this.quantity' ] } } } } } }, { '$addFields': { totalOrder: { '$sum': '$meals.totalPrice' } } } ], {})
[
{
"_id": "5bea4c8f6edcd22d385a13c1",
"meals": [
{
"meal": {
"_id": "5bea4c8f6edcd22d385a13bf",
"name": "Chicken Nuggets",
"price": 3,
"__v": 0
},
"quantity": 12,
"totalPrice": 36
},
{
"meal": {
"_id": "5bea4c8f6edcd22d385a13c0",
"name": "Beef Burger",
"price": 6,
"__v": 0
},
"quantity": 4,
"totalPrice": 24
}
],
"totalOrder": 60
}
]
Mongoose: orders.aggregate([ { '$lookup': { from: 'meals', let: { meals: '$meals' }, pipeline: [ { '$match': { '$expr': { '$in': [ '$_id', '$$meals.meal' ] } } }, { '$replaceRoot': { newRoot: { meal: '$$ROOT', quantity: { '$arrayElemAt': [ '$$meals.quantity', { '$indexOfArray': [ '$$meals.meal', '$_id' ] } ] }, totalPrice: { '$multiply': [ { '$arrayElemAt': [ '$$meals.quantity', [Object] ] }, '$price' ] } } } } ], as: 'meals' } }, { '$addFields': { totalOrder: { '$sum': '$meals.totalPrice' } } } ], {})
[
{
"_id": "5bea4c8f6edcd22d385a13c1",
"meals": [
{
"meal": {
"_id": "5bea4c8f6edcd22d385a13bf",
"name": "Chicken Nuggets",
"price": 3,
"__v": 0
},
"quantity": 12,
"totalPrice": 36
},
{
"meal": {
"_id": "5bea4c8f6edcd22d385a13c0",
"name": "Beef Burger",
"price": 6,
"__v": 0
},
"quantity": 4,
"totalPrice": 24
}
],
"__v": 0,
"totalOrder": 60
}
]
Mongoose: orders.find({}, { projection: {} })
Mongoose: meals.find({ _id: { '$in': [ ObjectId("5bea4c8f6edcd22d385a13bf"), ObjectId("5bea4c8f6edcd22d385a13c0") ] } }, { projection: {} })
[
{
"_id": "5bea4c8f6edcd22d385a13c1",
"meals": [
{
"_id": "5bea4c8f6edcd22d385a13c3",
"meal": {
"_id": "5bea4c8f6edcd22d385a13bf",
"name": "Chicken Nuggets",
"price": 3,
"__v": 0
},
"quantity": 12,
"totalPrice": 36
},
{
"_id": "5bea4c8f6edcd22d385a13c2",
"meal": {
"_id": "5bea4c8f6edcd22d385a13c0",
"name": "Beef Burger",
"price": 6,
"__v": 0
},
"quantity": 4,
"totalPrice": 24
}
],
"__v": 0,
"totalOrder": 60
}
]
answered Nov 13 '18 at 3:43
Neil Lunn
97.1k22170181
Absolutely perfectly clear answer. Thanks a lot for all the details.
– Atheryl
Nov 13 '18 at 4:14
add a comment |
So there's a couple of ways to do this as mentioned.
Using $lookup
You basically want to get the "related" data from the other collection and "merge" that with the existing array items. You cannot actually just "target" the existing array since $lookup cannot do that, but it can write another array and then you can "merge" them together:
let result1 = await Order.aggregate([
{ "$lookup": {
"from": Meal.collection.name,
"foreignField": "_id",
"localField": "meals.meal",
"as": "mealitems"
}},
{ "$project": {
"meals": {
"$map": {
"input": "$meals",
"in": {
"meal": {
"$arrayElemAt": [
"$mealitems",
{ "$indexOfArray": [ "$mealitems._id", "$$this.meal" ] }
]
},
"quantity": "$$this.quantity",
"totalPrice": {
"$multiply": [
{ "$arrayElemAt": [
"$mealitems.price",
{ "$indexOfArray": [ "$mealitems._id", "$$this.meal" ] }
]},
"$$this.quantity"
]
}
}
}
}
}},
{ "$addFields": {
"totalOrder": {
"$sum": "$meals.totalPrice"
}
}}
]);
That basically produces another array "mealitems" as the result of $lookup and then uses $map in order to process through the original document array and transpose the returned content array items back into the structure for each item.
You do that in combination with $arrayElemAt and $indexOfArray to find the matched items to transpose here.
There is also some "math" for the other computed elements using $multiply, and even an additional $addFields stage using $sum to "add those up" to give an overall "order total" for the document.
You "could" just do all that math in the $project stage ( which is used because we don't want the "mealitems" content. But that's a little more involved and you probably want to use $let for the array matching so you don't repeat your code so much.
You can even use the "sub-pipeline" form of $lookup if you really want to. Instead of using $map as the operations to alter the returned documents are done "inside" the returned array before the results are returned, by transposing the initial document array into the result documents via it's let argument:
// Aggregate with $lookup - sub-pipeline
let result2 = await Order.aggregate([
{ "$lookup": {
"from": Meal.collection.name,
"let": { "meals": "$meals" },
"pipeline": [
{ "$match": {
"$expr": {
"$in": [ "$_id", "$$meals.meal" ]
}
}},
{ "$replaceRoot": {
"newRoot": {
"meal": "$$ROOT",
"quantity": {
"$arrayElemAt": [
"$$meals.quantity",
{ "$indexOfArray": [ "$$meals.meal", "$_id" ] }
]
},
"totalPrice": {
"$multiply": [
{ "$arrayElemAt": [
"$$meals.quantity",
{ "$indexOfArray": [ "$$meals.meal", "$_id" ] }
]},
"$price"
]
}
}
}}
],
"as": "meals"
}},
{ "$addFields": {
"totalOrder": {
"$sum": "$meals.totalPrice"
}
}}
]);
In either form, that's basically an allegory for what populate() is doing under the hood by "merging" the content, but of course that uses separate database requests where the $lookup aggregation is just one request.
Using populate()
Alternately you can just manipulate the resulting structure in JavaScript. It's already there, and all you really need is the lean() in order to be able to alter the resulting objects:
// Populate and manipulate
let result3 = await Order.find().populate('meals.meal').lean();
result3 = result3.map(r =>
({
...r,
meals: r.meals.map( m =>
({
...m,
totalPrice: m.meal.price * m.quantity
})
),
totalOrder: r.meals.reduce((o, m) =>
o + (m.meal.price * m.quantity), 0
)
})
);
It looks pretty simple and is basically the same thing, with the exceptions that the "merging" was already done for you and that of course this is two requests to the server in order to return all the data.
As a reproducible full listing:
const { Schema } = mongoose = require('mongoose');
// Connection
const uri = 'mongodb://localhost:27017/menu';
const opts = { useNewUrlParser: true };
// Sensible defaults
mongoose.Promise = global.Promise;
mongoose.set('useFindAndModify', false);
mongoose.set('useCreateIndex', true);
mongoose.set('debug', true);
// Schema defs
const mealSchema = new Schema({
name: String,
price: Number
});
const orderSchema = new Schema({
meals: [
{
meal: { type: Schema.Types.ObjectId, ref: 'Meal' },
quantity: Number
}
]
});
const Meal = mongoose.model('Meal', mealSchema);
const Order = mongoose.model('Order', orderSchema);
// log helper
const log = data => console.log(JSON.stringify(data, undefined, 2));
// main
(async function() {
try {
const conn = await mongoose.connect(uri, opts);
// clean models
await Promise.all(
Object.entries(conn.models).map(([k,m]) => m.deleteMany())
);
// Set up data
let [Chicken, Beef] = await Meal.insertMany(
[
{ name: "Chicken Nuggets", price: 3 },
{ name: "Beef Burger", price: 6 }
]
);
let order = await Order.create({
meals: [
{ meal: Chicken, quantity: 12 },
{ meal: Beef, quantity: 4 }
]
});
// Aggregate with $lookup - traditional
let result1 = await Order.aggregate([
{ "$lookup": {
"from": Meal.collection.name,
"foreignField": "_id",
"localField": "meals.meal",
"as": "mealitems"
}},
{ "$project": {
"meals": {
"$map": {
"input": "$meals",
"in": {
"meal": {
"$arrayElemAt": [
"$mealitems",
{ "$indexOfArray": [ "$mealitems._id", "$$this.meal" ] }
]
},
"quantity": "$$this.quantity",
"totalPrice": {
"$multiply": [
{ "$arrayElemAt": [
"$mealitems.price",
{ "$indexOfArray": [ "$mealitems._id", "$$this.meal" ] }
]},
"$$this.quantity"
]
}
}
}
}
}},
{ "$addFields": {
"totalOrder": {
"$sum": "$meals.totalPrice"
}
}}
]);
log(result1);
// Aggregate with $lookup - sub-pipeline
let result2 = await Order.aggregate([
{ "$lookup": {
"from": Meal.collection.name,
"let": { "meals": "$meals" },
"pipeline": [
{ "$match": {
"$expr": {
"$in": [ "$_id", "$$meals.meal" ]
}
}},
{ "$replaceRoot": {
"newRoot": {
"meal": "$$ROOT",
"quantity": {
"$arrayElemAt": [
"$$meals.quantity",
{ "$indexOfArray": [ "$$meals.meal", "$_id" ] }
]
},
"totalPrice": {
"$multiply": [
{ "$arrayElemAt": [
"$$meals.quantity",
{ "$indexOfArray": [ "$$meals.meal", "$_id" ] }
]},
"$price"
]
}
}
}}
],
"as": "meals"
}},
{ "$addFields": {
"totalOrder": {
"$sum": "$meals.totalPrice"
}
}}
]);
log(result2);
// Populate and manipulate
let result3 = await Order.find().populate('meals.meal').lean();
result3 = result3.map(r =>
({
...r,
meals: r.meals.map( m =>
({
...m,
totalPrice: m.meal.price * m.quantity
})
),
totalOrder: r.meals.reduce((o, m) =>
o + (m.meal.price * m.quantity), 0
)
})
);
log(result3);
} catch(e) {
console.error(e);
} finally {
mongoose.disconnect();
}
})()
Which returns results like:
Mongoose: meals.deleteMany({}, {})
Mongoose: orders.deleteMany({}, {})
Mongoose: meals.insertMany([ { _id: 5bea4c8f6edcd22d385a13bf, name: 'Chicken Nuggets', price: 3, __v: 0 }, { _id: 5bea4c8f6edcd22d385a13c0, name: 'Beef Burger', price: 6, __v: 0 } ], {})
Mongoose: orders.insertOne({ _id: ObjectId("5bea4c8f6edcd22d385a13c1"), meals: [ { _id: ObjectId("5bea4c8f6edcd22d385a13c3"), meal: ObjectId("5bea4c8f6edcd22d385a13bf"), quantity: 12 }, { _id: ObjectId("5bea4c8f6edcd22d385a13c2"), meal: ObjectId("5bea4c8f6edcd22d385a13c0"), quantity: 4 } ], __v: 0 })
Mongoose: orders.aggregate([ { '$lookup': { from: 'meals', foreignField: '_id', localField: 'meals.meal', as: 'mealitems' } }, { '$project': { meals: { '$map': { input: '$meals', in: { meal: { '$arrayElemAt': [ '$mealitems', { '$indexOfArray': [ '$mealitems._id', '$$this.meal' ] } ] }, quantity: '$$this.quantity', totalPrice: { '$multiply': [ { '$arrayElemAt': [ '$mealitems.price', { '$indexOfArray': [Array] } ] }, '$$this.quantity' ] } } } } } }, { '$addFields': { totalOrder: { '$sum': '$meals.totalPrice' } } } ], {})
[
{
"_id": "5bea4c8f6edcd22d385a13c1",
"meals": [
{
"meal": {
"_id": "5bea4c8f6edcd22d385a13bf",
"name": "Chicken Nuggets",
"price": 3,
"__v": 0
},
"quantity": 12,
"totalPrice": 36
},
{
"meal": {
"_id": "5bea4c8f6edcd22d385a13c0",
"name": "Beef Burger",
"price": 6,
"__v": 0
},
"quantity": 4,
"totalPrice": 24
}
],
"totalOrder": 60
}
]
Mongoose: orders.aggregate([ { '$lookup': { from: 'meals', let: { meals: '$meals' }, pipeline: [ { '$match': { '$expr': { '$in': [ '$_id', '$$meals.meal' ] } } }, { '$replaceRoot': { newRoot: { meal: '$$ROOT', quantity: { '$arrayElemAt': [ '$$meals.quantity', { '$indexOfArray': [ '$$meals.meal', '$_id' ] } ] }, totalPrice: { '$multiply': [ { '$arrayElemAt': [ '$$meals.quantity', [Object] ] }, '$price' ] } } } } ], as: 'meals' } }, { '$addFields': { totalOrder: { '$sum': '$meals.totalPrice' } } } ], {})
[
{
"_id": "5bea4c8f6edcd22d385a13c1",
"meals": [
{
"meal": {
"_id": "5bea4c8f6edcd22d385a13bf",
"name": "Chicken Nuggets",
"price": 3,
"__v": 0
},
"quantity": 12,
"totalPrice": 36
},
{
"meal": {
"_id": "5bea4c8f6edcd22d385a13c0",
"name": "Beef Burger",
"price": 6,
"__v": 0
},
"quantity": 4,
"totalPrice": 24
}
],
"__v": 0,
"totalOrder": 60
}
]
Mongoose: orders.find({}, { projection: {} })
Mongoose: meals.find({ _id: { '$in': [ ObjectId("5bea4c8f6edcd22d385a13bf"), ObjectId("5bea4c8f6edcd22d385a13c0") ] } }, { projection: {} })
[
{
"_id": "5bea4c8f6edcd22d385a13c1",
"meals": [
{
"_id": "5bea4c8f6edcd22d385a13c3",
"meal": {
"_id": "5bea4c8f6edcd22d385a13bf",
"name": "Chicken Nuggets",
"price": 3,
"__v": 0
},
"quantity": 12,
"totalPrice": 36
},
{
"_id": "5bea4c8f6edcd22d385a13c2",
"meal": {
"_id": "5bea4c8f6edcd22d385a13c0",
"name": "Beef Burger",
"price": 6,
"__v": 0
},
"quantity": 4,
"totalPrice": 24
}
],
"__v": 0,
"totalOrder": 60
}
]
answered Nov 13 '18 at 3:43
Neil Lunn
97.1k22170181
Absolutely perfectly clear answer. Thanks a lot for all the details.
– Atheryl
Nov 13 '18 at 4:14
add a comment |
So there's a couple of ways to do this as mentioned.
Using $lookup
You basically want to get the "related" data from the other collection and "merge" that with the existing array items. You cannot actually just "target" the existing array since $lookup cannot do that, but it can write another array and then you can "merge" them together:
let result1 = await Order.aggregate([
{ "$lookup": {
"from": Meal.collection.name,
"foreignField": "_id",
"localField": "meals.meal",
"as": "mealitems"
}},
{ "$project": {
"meals": {
"$map": {
"input": "$meals",
"in": {
"meal": {
"$arrayElemAt": [
"$mealitems",
{ "$indexOfArray": [ "$mealitems._id", "$$this.meal" ] }
]
},
"quantity": "$$this.quantity",
"totalPrice": {
"$multiply": [
{ "$arrayElemAt": [
"$mealitems.price",
{ "$indexOfArray": [ "$mealitems._id", "$$this.meal" ] }
]},
"$$this.quantity"
]
}
}
}
}
}},
{ "$addFields": {
"totalOrder": {
"$sum": "$meals.totalPrice"
}
}}
]);
That basically produces another array "mealitems" as the result of $lookup and then uses $map in order to process through the original document array and transpose the returned content array items back into the structure for each item.
You do that in combination with $arrayElemAt and $indexOfArray to find the matched items to transpose here.
There is also some "math" for the other computed elements using $multiply, and even an additional $addFields stage using $sum to "add those up" to give an overall "order total" for the document.
You "could" just do all that math in the $project stage ( which is used because we don't want the "mealitems" content. But that's a little more involved and you probably want to use $let for the array matching so you don't repeat your code so much.
You can even use the "sub-pipeline" form of $lookup if you really want to. Instead of using $map as the operations to alter the returned documents are done "inside" the returned array before the results are returned, by transposing the initial document array into the result documents via it's let argument:
// Aggregate with $lookup - sub-pipeline
let result2 = await Order.aggregate([
{ "$lookup": {
"from": Meal.collection.name,
"let": { "meals": "$meals" },
"pipeline": [
{ "$match": {
"$expr": {
"$in": [ "$_id", "$$meals.meal" ]
}
}},
{ "$replaceRoot": {
"newRoot": {
"meal": "$$ROOT",
"quantity": {
"$arrayElemAt": [
"$$meals.quantity",
{ "$indexOfArray": [ "$$meals.meal", "$_id" ] }
]
},
"totalPrice": {
"$multiply": [
{ "$arrayElemAt": [
"$$meals.quantity",
{ "$indexOfArray": [ "$$meals.meal", "$_id" ] }
]},
"$price"
]
}
}
}}
],
"as": "meals"
}},
{ "$addFields": {
"totalOrder": {
"$sum": "$meals.totalPrice"
}
}}
]);
In either form, that's basically an allegory for what populate() is doing under the hood by "merging" the content, but of course that uses separate database requests where the $lookup aggregation is just one request.
Using populate()
Alternately you can just manipulate the resulting structure in JavaScript. It's already there, and all you really need is the lean() in order to be able to alter the resulting objects:
// Populate and manipulate
let result3 = await Order.find().populate('meals.meal').lean();
result3 = result3.map(r =>
({
...r,
meals: r.meals.map( m =>
({
...m,
totalPrice: m.meal.price * m.quantity
})
),
totalOrder: r.meals.reduce((o, m) =>
o + (m.meal.price * m.quantity), 0
)
})
);
It looks pretty simple and is basically the same thing, with the exceptions that the "merging" was already done for you and that of course this is two requests to the server in order to return all the data.
As a reproducible full listing:
const { Schema } = mongoose = require('mongoose');
// Connection
const uri = 'mongodb://localhost:27017/menu';
const opts = { useNewUrlParser: true };
// Sensible defaults
mongoose.Promise = global.Promise;
mongoose.set('useFindAndModify', false);
mongoose.set('useCreateIndex', true);
mongoose.set('debug', true);
// Schema defs
const mealSchema = new Schema({
name: String,
price: Number
});
const orderSchema = new Schema({
meals: [
{
meal: { type: Schema.Types.ObjectId, ref: 'Meal' },
quantity: Number
}
]
});
const Meal = mongoose.model('Meal', mealSchema);
const Order = mongoose.model('Order', orderSchema);
// log helper
const log = data => console.log(JSON.stringify(data, undefined, 2));
// main
(async function() {
try {
const conn = await mongoose.connect(uri, opts);
// clean models
await Promise.all(
Object.entries(conn.models).map(([k,m]) => m.deleteMany())
);
// Set up data
let [Chicken, Beef] = await Meal.insertMany(
[
{ name: "Chicken Nuggets", price: 3 },
{ name: "Beef Burger", price: 6 }
]
);
let order = await Order.create({
meals: [
{ meal: Chicken, quantity: 12 },
{ meal: Beef, quantity: 4 }
]
});
// Aggregate with $lookup - traditional
let result1 = await Order.aggregate([
{ "$lookup": {
"from": Meal.collection.name,
"foreignField": "_id",
"localField": "meals.meal",
"as": "mealitems"
}},
{ "$project": {
"meals": {
"$map": {
"input": "$meals",
"in": {
"meal": {
"$arrayElemAt": [
"$mealitems",
{ "$indexOfArray": [ "$mealitems._id", "$$this.meal" ] }
]
},
"quantity": "$$this.quantity",
"totalPrice": {
"$multiply": [
{ "$arrayElemAt": [
"$mealitems.price",
{ "$indexOfArray": [ "$mealitems._id", "$$this.meal" ] }
]},
"$$this.quantity"
]
}
}
}
}
}},
{ "$addFields": {
"totalOrder": {
"$sum": "$meals.totalPrice"
}
}}
]);
log(result1);
// Aggregate with $lookup - sub-pipeline
let result2 = await Order.aggregate([
{ "$lookup": {
"from": Meal.collection.name,
"let": { "meals": "$meals" },
"pipeline": [
{ "$match": {
"$expr": {
"$in": [ "$_id", "$$meals.meal" ]
}
}},
{ "$replaceRoot": {
"newRoot": {
"meal": "$$ROOT",
"quantity": {
"$arrayElemAt": [
"$$meals.quantity",
{ "$indexOfArray": [ "$$meals.meal", "$_id" ] }
]
},
"totalPrice": {
"$multiply": [
{ "$arrayElemAt": [
"$$meals.quantity",
{ "$indexOfArray": [ "$$meals.meal", "$_id" ] }
]},
"$price"
]
}
}
}}
],
"as": "meals"
}},
{ "$addFields": {
"totalOrder": {
"$sum": "$meals.totalPrice"
}
}}
]);
log(result2);
// Populate and manipulate
let result3 = await Order.find().populate('meals.meal').lean();
result3 = result3.map(r =>
({
...r,
meals: r.meals.map( m =>
({
...m,
totalPrice: m.meal.price * m.quantity
})
),
totalOrder: r.meals.reduce((o, m) =>
o + (m.meal.price * m.quantity), 0
)
})
);
log(result3);
} catch(e) {
console.error(e);
} finally {
mongoose.disconnect();
}
})()
Which returns results like:
Mongoose: meals.deleteMany({}, {})
Mongoose: orders.deleteMany({}, {})
Mongoose: meals.insertMany([ { _id: 5bea4c8f6edcd22d385a13bf, name: 'Chicken Nuggets', price: 3, __v: 0 }, { _id: 5bea4c8f6edcd22d385a13c0, name: 'Beef Burger', price: 6, __v: 0 } ], {})
Mongoose: orders.insertOne({ _id: ObjectId("5bea4c8f6edcd22d385a13c1"), meals: [ { _id: ObjectId("5bea4c8f6edcd22d385a13c3"), meal: ObjectId("5bea4c8f6edcd22d385a13bf"), quantity: 12 }, { _id: ObjectId("5bea4c8f6edcd22d385a13c2"), meal: ObjectId("5bea4c8f6edcd22d385a13c0"), quantity: 4 } ], __v: 0 })
Mongoose: orders.aggregate([ { '$lookup': { from: 'meals', foreignField: '_id', localField: 'meals.meal', as: 'mealitems' } }, { '$project': { meals: { '$map': { input: '$meals', in: { meal: { '$arrayElemAt': [ '$mealitems', { '$indexOfArray': [ '$mealitems._id', '$$this.meal' ] } ] }, quantity: '$$this.quantity', totalPrice: { '$multiply': [ { '$arrayElemAt': [ '$mealitems.price', { '$indexOfArray': [Array] } ] }, '$$this.quantity' ] } } } } } }, { '$addFields': { totalOrder: { '$sum': '$meals.totalPrice' } } } ], {})
[
{
"_id": "5bea4c8f6edcd22d385a13c1",
"meals": [
{
"meal": {
"_id": "5bea4c8f6edcd22d385a13bf",
"name": "Chicken Nuggets",
"price": 3,
"__v": 0
},
"quantity": 12,
"totalPrice": 36
},
{
"meal": {
"_id": "5bea4c8f6edcd22d385a13c0",
"name": "Beef Burger",
"price": 6,
"__v": 0
},
"quantity": 4,
"totalPrice": 24
}
],
"totalOrder": 60
}
]
Mongoose: orders.aggregate([ { '$lookup': { from: 'meals', let: { meals: '$meals' }, pipeline: [ { '$match': { '$expr': { '$in': [ '$_id', '$$meals.meal' ] } } }, { '$replaceRoot': { newRoot: { meal: '$$ROOT', quantity: { '$arrayElemAt': [ '$$meals.quantity', { '$indexOfArray': [ '$$meals.meal', '$_id' ] } ] }, totalPrice: { '$multiply': [ { '$arrayElemAt': [ '$$meals.quantity', [Object] ] }, '$price' ] } } } } ], as: 'meals' } }, { '$addFields': { totalOrder: { '$sum': '$meals.totalPrice' } } } ], {})
[
{
"_id": "5bea4c8f6edcd22d385a13c1",
"meals": [
{
"meal": {
"_id": "5bea4c8f6edcd22d385a13bf",
"name": "Chicken Nuggets",
"price": 3,
"__v": 0
},
"quantity": 12,
"totalPrice": 36
},
{
"meal": {
"_id": "5bea4c8f6edcd22d385a13c0",
"name": "Beef Burger",
"price": 6,
"__v": 0
},
"quantity": 4,
"totalPrice": 24
}
],
"__v": 0,
"totalOrder": 60
}
]
Mongoose: orders.find({}, { projection: {} })
Mongoose: meals.find({ _id: { '$in': [ ObjectId("5bea4c8f6edcd22d385a13bf"), ObjectId("5bea4c8f6edcd22d385a13c0") ] } }, { projection: {} })
[
{
"_id": "5bea4c8f6edcd22d385a13c1",
"meals": [
{
"_id": "5bea4c8f6edcd22d385a13c3",
"meal": {
"_id": "5bea4c8f6edcd22d385a13bf",
"name": "Chicken Nuggets",
"price": 3,
"__v": 0
},
"quantity": 12,
"totalPrice": 36
},
{
"_id": "5bea4c8f6edcd22d385a13c2",
"meal": {
"_id": "5bea4c8f6edcd22d385a13c0",
"name": "Beef Burger",
"price": 6,
"__v": 0
},
"quantity": 4,
"totalPrice": 24
}
],
"__v": 0,
"totalOrder": 60
}
]
answered Nov 13 '18 at 3:43
Neil Lunn
97.1k22170181
So there's a couple of ways to do this as mentioned.
Using $lookup
You basically want to get the "related" data from the other collection and "merge" that with the existing array items. You cannot actually just "target" the existing array since $lookup cannot do that, but it can write another array and then you can "merge" them together:
let result1 = await Order.aggregate([
{ "$lookup": {
"from": Meal.collection.name,
"foreignField": "_id",
"localField": "meals.meal",
"as": "mealitems"
}},
{ "$project": {
"meals": {
"$map": {
"input": "$meals",
"in": {
"meal": {
"$arrayElemAt": [
"$mealitems",
{ "$indexOfArray": [ "$mealitems._id", "$$this.meal" ] }
]
},
"quantity": "$$this.quantity",
"totalPrice": {
"$multiply": [
{ "$arrayElemAt": [
"$mealitems.price",
{ "$indexOfArray": [ "$mealitems._id", "$$this.meal" ] }
]},
"$$this.quantity"
]
}
}
}
}
}},
{ "$addFields": {
"totalOrder": {
"$sum": "$meals.totalPrice"
}
}}
]);
That basically produces another array "mealitems" as the result of $lookup and then uses $map in order to process through the original document array and transpose the returned content array items back into the structure for each item.
You do that in combination with $arrayElemAt and $indexOfArray to find the matched items to transpose here.
There is also some "math" for the other computed elements using $multiply, and even an additional $addFields stage using $sum to "add those up" to give an overall "order total" for the document.
You "could" just do all that math in the $project stage ( which is used because we don't want the "mealitems" content. But that's a little more involved and you probably want to use $let for the array matching so you don't repeat your code so much.
You can even use the "sub-pipeline" form of $lookup if you really want to. Instead of using $map as the operations to alter the returned documents are done "inside" the returned array before the results are returned, by transposing the initial document array into the result documents via it's let argument:
// Aggregate with $lookup - sub-pipeline
let result2 = await Order.aggregate([
{ "$lookup": {
"from": Meal.collection.name,
"let": { "meals": "$meals" },
"pipeline": [
{ "$match": {
"$expr": {
"$in": [ "$_id", "$$meals.meal" ]
}
}},
{ "$replaceRoot": {
"newRoot": {
"meal": "$$ROOT",
"quantity": {
"$arrayElemAt": [
"$$meals.quantity",
{ "$indexOfArray": [ "$$meals.meal", "$_id" ] }
]
},
"totalPrice": {
"$multiply": [
{ "$arrayElemAt": [
"$$meals.quantity",
{ "$indexOfArray": [ "$$meals.meal", "$_id" ] }
]},
"$price"
]
}
}
}}
],
"as": "meals"
}},
{ "$addFields": {
"totalOrder": {
"$sum": "$meals.totalPrice"
}
}}
]);
In either form, that's basically an allegory for what populate() is doing under the hood by "merging" the content, but of course that uses separate database requests where the $lookup aggregation is just one request.
Using populate()
Alternately you can just manipulate the resulting structure in JavaScript. It's already there, and all you really need is the lean() in order to be able to alter the resulting objects:
// Populate and manipulate
let result3 = await Order.find().populate('meals.meal').lean();
result3 = result3.map(r =>
({
...r,
meals: r.meals.map( m =>
({
...m,
totalPrice: m.meal.price * m.quantity
})
),
totalOrder: r.meals.reduce((o, m) =>
o + (m.meal.price * m.quantity), 0
)
})
);
It looks pretty simple and is basically the same thing, with the exceptions that the "merging" was already done for you and that of course this is two requests to the server in order to return all the data.
As a reproducible full listing:
const { Schema } = mongoose = require('mongoose');
// Connection
const uri = 'mongodb://localhost:27017/menu';
const opts = { useNewUrlParser: true };
// Sensible defaults
mongoose.Promise = global.Promise;
mongoose.set('useFindAndModify', false);
mongoose.set('useCreateIndex', true);
mongoose.set('debug', true);
// Schema defs
const mealSchema = new Schema({
name: String,
price: Number
});
const orderSchema = new Schema({
meals: [
{
meal: { type: Schema.Types.ObjectId, ref: 'Meal' },
quantity: Number
}
]
});
const Meal = mongoose.model('Meal', mealSchema);
const Order = mongoose.model('Order', orderSchema);
// log helper
const log = data => console.log(JSON.stringify(data, undefined, 2));
// main
(async function() {
try {
const conn = await mongoose.connect(uri, opts);
// clean models
await Promise.all(
Object.entries(conn.models).map(([k,m]) => m.deleteMany())
);
// Set up data
let [Chicken, Beef] = await Meal.insertMany(
[
{ name: "Chicken Nuggets", price: 3 },
{ name: "Beef Burger", price: 6 }
]
);
let order = await Order.create({
meals: [
{ meal: Chicken, quantity: 12 },
{ meal: Beef, quantity: 4 }
]
});
// Aggregate with $lookup - traditional
let result1 = await Order.aggregate([
{ "$lookup": {
"from": Meal.collection.name,
"foreignField": "_id",
"localField": "meals.meal",
"as": "mealitems"
}},
{ "$project": {
"meals": {
"$map": {
"input": "$meals",
"in": {
"meal": {
"$arrayElemAt": [
"$mealitems",
{ "$indexOfArray": [ "$mealitems._id", "$$this.meal" ] }
]
},
"quantity": "$$this.quantity",
"totalPrice": {
"$multiply": [
{ "$arrayElemAt": [
"$mealitems.price",
{ "$indexOfArray": [ "$mealitems._id", "$$this.meal" ] }
]},
"$$this.quantity"
]
}
}
}
}
}},
{ "$addFields": {
"totalOrder": {
"$sum": "$meals.totalPrice"
}
}}
]);
log(result1);
// Aggregate with $lookup - sub-pipeline
let result2 = await Order.aggregate([
{ "$lookup": {
"from": Meal.collection.name,
"let": { "meals": "$meals" },
"pipeline": [
{ "$match": {
"$expr": {
"$in": [ "$_id", "$$meals.meal" ]
}
}},
{ "$replaceRoot": {
"newRoot": {
"meal": "$$ROOT",
"quantity": {
"$arrayElemAt": [
"$$meals.quantity",
{ "$indexOfArray": [ "$$meals.meal", "$_id" ] }
]
},
"totalPrice": {
"$multiply": [
{ "$arrayElemAt": [
"$$meals.quantity",
{ "$indexOfArray": [ "$$meals.meal", "$_id" ] }
]},
"$price"
]
}
}
}}
],
"as": "meals"
}},
{ "$addFields": {
"totalOrder": {
"$sum": "$meals.totalPrice"
}
}}
]);
log(result2);
// Populate and manipulate
let result3 = await Order.find().populate('meals.meal').lean();
result3 = result3.map(r =>
({
...r,
meals: r.meals.map( m =>
({
...m,
totalPrice: m.meal.price * m.quantity
})
),
totalOrder: r.meals.reduce((o, m) =>
o + (m.meal.price * m.quantity), 0
)
})
);
log(result3);
} catch(e) {
console.error(e);
} finally {
mongoose.disconnect();
}
})()
Which returns results like:
Mongoose: meals.deleteMany({}, {})
Mongoose: orders.deleteMany({}, {})
Mongoose: meals.insertMany([ { _id: 5bea4c8f6edcd22d385a13bf, name: 'Chicken Nuggets', price: 3, __v: 0 }, { _id: 5bea4c8f6edcd22d385a13c0, name: 'Beef Burger', price: 6, __v: 0 } ], {})
Mongoose: orders.insertOne({ _id: ObjectId("5bea4c8f6edcd22d385a13c1"), meals: [ { _id: ObjectId("5bea4c8f6edcd22d385a13c3"), meal: ObjectId("5bea4c8f6edcd22d385a13bf"), quantity: 12 }, { _id: ObjectId("5bea4c8f6edcd22d385a13c2"), meal: ObjectId("5bea4c8f6edcd22d385a13c0"), quantity: 4 } ], __v: 0 })
Mongoose: orders.aggregate([ { '$lookup': { from: 'meals', foreignField: '_id', localField: 'meals.meal', as: 'mealitems' } }, { '$project': { meals: { '$map': { input: '$meals', in: { meal: { '$arrayElemAt': [ '$mealitems', { '$indexOfArray': [ '$mealitems._id', '$$this.meal' ] } ] }, quantity: '$$this.quantity', totalPrice: { '$multiply': [ { '$arrayElemAt': [ '$mealitems.price', { '$indexOfArray': [Array] } ] }, '$$this.quantity' ] } } } } } }, { '$addFields': { totalOrder: { '$sum': '$meals.totalPrice' } } } ], {})
[
{
"_id": "5bea4c8f6edcd22d385a13c1",
"meals": [
{
"meal": {
"_id": "5bea4c8f6edcd22d385a13bf",
"name": "Chicken Nuggets",
"price": 3,
"__v": 0
},
"quantity": 12,
"totalPrice": 36
},
{
"meal": {
"_id": "5bea4c8f6edcd22d385a13c0",
"name": "Beef Burger",
"price": 6,
"__v": 0
},
"quantity": 4,
"totalPrice": 24
}
],
"totalOrder": 60
}
]
Mongoose: orders.aggregate([ { '$lookup': { from: 'meals', let: { meals: '$meals' }, pipeline: [ { '$match': { '$expr': { '$in': [ '$_id', '$$meals.meal' ] } } }, { '$replaceRoot': { newRoot: { meal: '$$ROOT', quantity: { '$arrayElemAt': [ '$$meals.quantity', { '$indexOfArray': [ '$$meals.meal', '$_id' ] } ] }, totalPrice: { '$multiply': [ { '$arrayElemAt': [ '$$meals.quantity', [Object] ] }, '$price' ] } } } } ], as: 'meals' } }, { '$addFields': { totalOrder: { '$sum': '$meals.totalPrice' } } } ], {})
[
{
"_id": "5bea4c8f6edcd22d385a13c1",
"meals": [
{
"meal": {
"_id": "5bea4c8f6edcd22d385a13bf",
"name": "Chicken Nuggets",
"price": 3,
"__v": 0
},
"quantity": 12,
"totalPrice": 36
},
{
"meal": {
"_id": "5bea4c8f6edcd22d385a13c0",
"name": "Beef Burger",
"price": 6,
"__v": 0
},
"quantity": 4,
"totalPrice": 24
}
],
"__v": 0,
"totalOrder": 60
}
]
Mongoose: orders.find({}, { projection: {} })
Mongoose: meals.find({ _id: { '$in': [ ObjectId("5bea4c8f6edcd22d385a13bf"), ObjectId("5bea4c8f6edcd22d385a13c0") ] } }, { projection: {} })
[
{
"_id": "5bea4c8f6edcd22d385a13c1",
"meals": [
{
"_id": "5bea4c8f6edcd22d385a13c3",
"meal": {
"_id": "5bea4c8f6edcd22d385a13bf",
"name": "Chicken Nuggets",
"price": 3,
"__v": 0
},
"quantity": 12,
"totalPrice": 36
},
{
"_id": "5bea4c8f6edcd22d385a13c2",
"meal": {
"_id": "5bea4c8f6edcd22d385a13c0",
"name": "Beef Burger",
"price": 6,
"__v": 0
},
"quantity": 4,
"totalPrice": 24
}
],
"__v": 0,
"totalOrder": 60
}
]
answered Nov 13 '18 at 3:43
Neil Lunn
97.1k22170181
edited Nov 13 '18 at 4:18
answered Nov 13 '18 at 3:43
Neil Lunn
97.1k22170181
answered Nov 13 '18 at 3:43
Neil Lunn
97.1k22170181
answered Nov 13 '18 at 3:43
Neil Lunn
97.1k22170181
97.1k22170181
Absolutely perfectly clear answer. Thanks a lot for all the details.
– Atheryl
Nov 13 '18 at 4:14
add a comment |
Absolutely perfectly clear answer. Thanks a lot for all the details.
– Atheryl
Nov 13 '18 at 4:14
Absolutely perfectly clear answer. Thanks a lot for all the details.
– Atheryl
Nov 13 '18 at 4:14
Absolutely perfectly clear answer. Thanks a lot for all the details.
– Atheryl
Nov 13 '18 at 4:14
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53273127%2fpopulate-aggregate-on-array%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
You cannot mix
populate()andaggregate(). There is instead$lookupto use in an aggregation pipeline instead of callingpopulate()altogether. No code here, and without it we can only point you to generic examples.– Neil Lunn
Nov 13 '18 at 2:59
2
Alternately, just do the
populate()withlean()and manipulate the result object in JavaScript code. A simpleArray.reduce()over the array content should do the job.– Neil Lunn
Nov 13 '18 at 3:03
1
I should have mentioned I would like to achieve this through mongodb rather that manipulating in Javascript. I find the code alternative not so elegant.
– Atheryl
Nov 13 '18 at 3:06