optimization of MongoDB query

Question

I have many collections in my MongoDB

• products
• sizes
• colors
• shops
• taxes
• suppliers
• clients etc...

My query is work's but it's take many many time. ~15 seconds when I get 20 records.

In my query I have ~11 lookups

is it possible that turning on indexing in collections (_id, appId) (<-- appId using in every collection) will help me to speed up this query?

or something is wrong in my query? thanks!

My query is:

const invoices = await Invoice.aggregate([
  { $match: query },
  { $unwind: "$items" },
  //products
  {
    $lookup: {
      from: "products",
      localField: "items.itemId",
      foreignField: "_id",
      as: "itemId",
    },
  },
  //colors
  {
    $lookup: {
      from: "colors",
      localField: "items.itemColor",
      foreignField: "_id",
      as: "itemColor",
    },
  },
  //sizes
  {
    $lookup: {
      from: "sizes",
      localField: "items.itemSize",
      foreignField: "_id",
      as: "itemSize",
    },
  },
  //taxes
  {
    $lookup: {
      from: "taxes",
      localField: "items.itemTax",
      foreignField: "_id",
      as: "itemTax",
    },
  },
  //shops
  {
    $lookup: {
      from: "shops",
      localField: "shop",
      foreignField: "_id",
      as: "shop",
    },
  },
  {
    $unwind: { path: "$shop", preserveNullAndEmptyArrays: true },
  },
  //shopTo
  {
    $lookup: {
      from: "shops",
      localField: "shopTo",
      foreignField: "_id",
      as: "shopTo",
    },
  },
  {
    $unwind: { path: "$shopTo", preserveNullAndEmptyArrays: true },
  },
  //moves_sup
  {
    $lookup: {
      from: "suppliers",
      localField: "supplier",
      foreignField: "_id",
      as: "moves_sup",
    },
  },
  {
    $unwind: { path: "$moves_sup", preserveNullAndEmptyArrays: true },
  },
  //moves_client
  {
    $lookup: {
      from: "clients",
      localField: "supplier",
      foreignField: "_id",
      as: "moves_client",
    },
  },
  {
    $unwind: { path: "$moves_client", preserveNullAndEmptyArrays: true },
  },
  //user
  {
    $lookup: {
      from: "users",
      localField: "user",
      foreignField: "_id",
      as: "user",
    },
  },
  {
    $unwind: { path: "$user", preserveNullAndEmptyArrays: true },
  },
  //editedBy
  {
    $lookup: {
      from: "users",
      localField: "editedBy",
      foreignField: "_id",
      as: "editedBy",
    },
  },
  {
    $unwind: { path: "$editedBy", preserveNullAndEmptyArrays: true },
  },
  //cat
  {
    $lookup: {
      from: "moneycategories",
      localField: "category",
      foreignField: "_id",
      as: "cat",
    },
  },
  {
    $unwind: { path: "$cat", preserveNullAndEmptyArrays: true },
  },
  {
    $addFields: {
      agentName: {
        $cond: [
          { $eq: [{ $type: "$moves_sup" }, "object"] },
          "$moves_sup.name",
          {
            $cond: [
              { $eq: [{ $type: "$moves_client" }, "object"] },
              "$moves_client.name",
              null,
            ],
          },
        ],
      },
      moves_client: "$$REMOVE",
      moves_sup: "$$REMOVE",
      "items.itemColorName": { $arrayElemAt: ["$itemColor.colorName", 0] },
      "items.itemSizeName": { $arrayElemAt: ["$itemSize.sizeName", 0] },
      "items.itemTaxName": { $arrayElemAt: ["$itemTax.name", 0] },
    },
  },
  {
    $group: {
      _id: "$_id",
      invoiceName: { $first: "$invoiceName" },
      invoiceNumber: { $first: "$invoiceNumber" },
      purchaseNumber: { $first: "$purchaseNumber" },
      date: { $first: "$date" },
      type: { $first: "$type" },
      // prMove: { $push: "$prMove" },
      agentName: { $first: "$agentName" },
      shopName: { $first: "$shop.shopName" },
      shopTo: {
        $first: "$shopTo.shopName",
      },
      supplier: { $first: "$supplier" },
      user: { $first: "$user.name" },
      category: { $first: "$cat" },
      categoryName: { $first: "$cat.categoryName" },
      editedBy: { $first: "$editedDate" },
      editedDate: { $first: "$editedDate" },
      comment: { $first: "comment" },
      addDate: { $first: "addDate" },
      items: { $push: "$items" },
    },
  },
])
  .sort({ date: -1 })
  .skip(+req.query.offset)
  .limit(+req.query.limit);

Answer 1

If the lookup collections are not too big, you can try to replace them by using variables. For example stage

{
   $lookup: {
      from: "colors",
      localField: "items.itemColor",
      foreignField: "_id",
      as: "itemColor",
   }
}

could be converted to

const colors = await  db.colors.find({<maybe some filters if applicable>}).toArray();

{
   $set: {
      itemColor: {
         $filter: {
            input: colors,
            as: "color",
            cond: { $eq: ["$$color._id", "$items.itemColor"] }
         }
      }
   }
}

You could also try to skip some of the $unwind stages. Then above stage would be like this:

{
   $set: {
      items: {
         $map: {
            input: "$items",
            as: "item",
            in: {
               $mergeObjects: [
                  "$$item",
                  {
                     itemColor: {
                        $filter: {
                           input: colors,
                           as: "color",
                           cond: { $eq: ["$$color._id", "$$item.itemColor"] }
                        }
                     }
                  }
               ]
            }
         }
      }
   }
}