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

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related