mongoDB, mongoose - aggregation an array of objects

Question

I have 3 collections to aggregate.

1st is colors collection

{ { _id: 1, <- mongoose objectId name: red }, { _id: 2, <- mongoose objectId name: green } }

2nd is products

{ { _id: Id777, <- mongoose objectId productName: test prod 777 }, { _id: Id888, <- mongoose objectId productName: test prod 888 } }

and 3rd it move collection

{ ....other fields here items: [ { _id: an mongoose id, itemId: Id777 <- in products collection, itemColor: 1 <- id in colors collection, coutn: 7, ....other fields }, { _id: an mongoose id, itemId: Id888 <- in products collection, itemColor: 2 <- id in colors collection cout: 10 ....other fields } ] }

I need to have an output like this:

{ ////information from collection items: [ { itemId: test prod 777, itemColor: red, count: 7 }, { itemId: test prod 888, itemColor: green, count: 10 } ] }

My code is:

 const moves = await ProductMoves.aggregate([ { $match: query }, // this is my query { $lookup: { from: 'products', localField: 'items.itemId', foreignField: '_id', as: 'productName' } }, { $unwind: { path: "$productName" , preserveNullAndEmptyArrays: true } }, { $lookup: { from: 'colors', localField: 'items.itemColor', foreignField: '_id', as: 'cName' } }, { $unwind: { path: "$cName" , preserveNullAndEmptyArrays: true } }, { $addFields: { mItems: { prName: "$productName.productName", prColor: "$cName.colorName" }, productName: 0, cName: 0 } } ]) .sort({addedDate: -1}) .skip(+req.query.offset) .limit(+req.query.limit)

but it returns only 1 element from the object array. probably I need something like a for loop, but i couldn't do it.

thank you for your responses, and have a good day!

turivishal · Accepted Answer · 2021-06-06 05:47:49Z

$unwind deconstruct items array
$lookup with products collection
$lookup with colors collection
$addFields, $arrayElemAt to get first element from lookup result
$group by _id and reconstruct items array and pass other fields as well
there is no external methods in an aggregate function, you have to use stages for sort, skip and limit like below
$sort by addedDate in descending order
$skip and $limit result

const moves = await ProductMoves.aggregate([ { $match: query }, // this is my query { $unwind: "$items" }, { $lookup: { from: "products", localField: "items.itemId", foreignField: "_id", as: "itemId" } }, { $lookup: { from: "colors", localField: "items.itemColor", foreignField: "_id", as: "itemColor" } }, { $addFields: { "items.itemId": { $arrayElemAt: ["$itemId.productName", 0] }, "items.itemColor": { $arrayElemAt: ["$itemColor.name", 0] } } }, { $group: { _id: "$_id", items: { $push: "$items" }, addedDate: { $first: "$addedDate" } // add other fields that you want in result like "addedDate" } }, { $sort: { addedDate: -1 } }, { $skip: +req.query.offset }, { $limit: +req.query.limit } ])

Playground

Collectives™ on Stack Overflow

mongoDB, mongoose - aggregation an array of objects

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related