I have many collections in my MongoDB database. Aggregation work's good but I can't push some fields to the output I needed,
collection A is:
{
_id: some mongodbID
//...fields
items: [
{
_id: someId,
color: someId <---- Im aggregate this with lookup
neededFieldToPush: 123
},
{
_id: someId,
color: someId <---- Im aggregate this with lookup
neededFieldToPush: 566
}
]
}
my query is:
await Invoice.aggregate([
{ $match: query },
{ $unwind: "$items" },
//colors
{
$lookup: {
from: "colors",
localField: "items.itemColor",
foreignField: "_id",
as: "itemColor"
}
},
{
$addFields: {
"prMove.itemColor": { $arrayElemAt: ["$itemColor.colorName", 0] },
}
},
{
$group: {
_id: "$_id",
items: { $push: "$items" }, <-- original items
prMove: { $push: "$prMove" },
}
},
])
.sort({date: -1})
.skip(+req.query.offset)
.limit(+req.query.limit)
I need to have output like this:
_id: someId,
items: [//original items],
prMove: [
{
itemColor: some color name, <--- it's works fine
neededFieldToPush: 123
},
{
itemColor: some color name, <--- it's works fine
neededFieldToPush: 566
},
]
so, how I can push neededFieldToPush field into prMove object?
thank you
The implementation of your query is expensive because you have used $unwind stage and $group stage, it will impact performance,
The second thing is the $sort, $skip and $limit functions will not work on the aggregate function of mongoose, you have use stages for that,
The third thing is to use sort, $skip and $limit stages immediately after $match stage, so it will improve performance while you create an index on required fields.
Improved query,
$match your query$sort sort stage$skip stage to pass offset$limit stage to limit documents$lookup with colors collection and pass items.color as localField$addFields to edit items array with color name in items$map to iterate loop of items array$reduce to iterate loop of itemColor array result from lookup, check condition if _id match then get color name and set value in color field$mergeObjects to merge new color field and current fields of the item object$$REMOVE to remove itemColor array because it is not needed nowlet offset = req.query.offset;
let limit = req.query.limit;
await Invoice.aggregate([
{ $match: query },
{ $sort: { date: -1 } },
{ $skip: offset },
{ $limit: limit },
{
$lookup: {
from: "colors",
localField: "items.color",
foreignField: "_id",
as: "itemColor"
}
},
{
$addFields: {
items: {
$map: {
input: "$items",
as: "i",
in: {
$mergeObjects: [
"$$i",
{
color: {
$reduce: {
input: "$itemColor",
initialValue: "",
in: {
$cond: [
{ $eq: ["$$this._id", "$$i._id"] },
"$$this.colorName",
"$$value"
]
}
}
}
}
]
}
}
},
itemColor: "$$REMOVE"
}
}
])