Replace Mongo Queries with an Mongo aggregation pipeline for products and product-categories collection

Question

I want to write a mongo aggregation pipeline for set of collections i.e and products and product-categories. I want to create an API which when given the following product and product-category attributes should find the matching product. Here are the collection structures. The search criteria for the products is as follows: I looked at the model and the product-categories collection is not in the products collection. Only the categoryName is in the products collection. That is why lookup will not work and I saw the unionWith.

 manufacturer
 category
 categoryName 
 factoryLocation

This search can return multiple products since there can be multiple products produced by the same manufacturer in the same location. The goal is to find and return the product with its name and other attributes. My bad I forgot to add the product id field. category is not the primary key in the products collection it is just an attribute which happens to be the _id in the product-categories field. I looked up $unionWith but I do not quite understand how to use it.

{
  "products": [
     {
      "_id": 90888, 
      "manufacturer": "CCComps",
      "factoryLocation": "China",
      "category": 56514,
      "categoryName": "Electronics",
      "price": 550,
      "model": "CC-1000",
      "quantity": 10
    },
    { 
      "_id": 922229,  
      "manufacturer": "CCComps",
      "factoryLocation": "China",
      "category": 56514,
      "categoryName": "Electrical",
      "price": 700,
      "model": "CM-1500",
      "quantity": 10
    }
  ]
}

The second collection is a lookup for the products based on the category in products and the categoryName. Product category matches the _id in the product-categories collection. Here is the structure of the product-categories collection. I am fairly new the Mongodb aggregation pipeline. I tried solving it by multiple Mongodb queries but it is slow because the product size is large and I have to make multiple queries. I'd appreciate help in providing a simple aggregation pipeline to solve this problem.

[{
  "_id": 22322,
  "product-categories": [
    {
      "productName": "Camera",
      "categoryName": "Electronics",
      "categoryId": 56514
    },
    {
      "productName": "Samsung Smart TV",
      "categoryName": "Electricals",
      "categoryId": 56514
    }
  ]
}]

I have some portions of the pipeline but putting it all together is the challenge:

db.getCollection("product-categories").aggregate([
    {
        "$match": {
            
            "_id": {
                "$eq": 56541
            }
        }
    },
    {
        "$project":{
            productName:1,
            categoryId: 1,
            categoryName:1
        }
    },
])

And this portion:

db.getCollection("products").aggregate([
    {
     "$match": {
          "manufacturer": {
             "$eq": "CCComps"
          },
          "factoryLocation": {
             "$eq": "China"
          },
          "category": {
              "$eq": 56541
          }
        
        }
    },  
])

How do I combine these to get the results? The output should be:

Searching with

_id: 22322,
categoryName: "Electronics"
categoryId": 56514
manufacturer: "CCComps",
factoryLocation: "China"

Should return

{
  "_id": 90888,
  "productName": "Camera",
  "productCategoryId": 56541,
  "category": 56541,
  "categoryName": "Electronics",
  "price": 550,
  "model": "CC-1000",
  "quantity": 10
}

Answer 1

All four search fields are in the 'products' collection, and you additionally only need productName from 'product-categories'.

1. First match on products with the search terms
2. Then use a lookup on product-categories of the _id
3. Within the lookup, match on the categoryId first to reduce the total number of records, then unwind and match again for the categoryName.
   • For MongoDB versions >= 5.0, that second match can be done as a pipeline for the lookup.
4. Then rename and unset fields as needed for the final output.

db.products.aggregate([
  {
    $match: {
      // search terms here
      category: 56514,
      categoryName: "Electronics",
      manufacturer: "CCComps",
      factoryLocation: "China"
    }
  },
  {
    $lookup: {
      from: "product-categories",
      as: "prodCatInfo",
      let: {
        catName: "$categoryName",
        catId: "$category"
      },
      pipeline: [
        {
          // reduce number of records before unwind
          $match: {
            $expr: {
              $in: ["$$catId", "$product-categories.categoryId"]
            }
          }
        },
        { $unwind: "$product-categories" },
        {
          $match: {
            $expr: {
              $eq: ["$product-categories.categoryName", "$$catName"]
            }
          }
        }
      ]
    }
  },
  {
    $set: {
      productName: { $first: "$prodCatInfo.product-categories.productName" },
      productCategoryId: "$category"
    }
  },
  { $unset: ["factoryLocation", "manufacturer", "prodCatInfo"] }
])

Mongo Playground