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

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
}

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
}

• mongodb
• aggregation-framework

Share Improve this question Follow edited Feb 1 at 7:07 BreenDeen asked Jan 31 at 20:44 BreenDeenBreenDeen 71233 gold badges2020 silver badges6565 bronze badges 5

• Perhaps you are looking for $lookup stage? Demo – Yong Shun Commented Feb 1 at 2:21
• 1 Please share the example for the expected output dicuments. – Yong Shun Commented Feb 1 at 4:09
• In your current edit, why shouldn't the 2nd product also match? It has the same categoryName, manufacturer, factoryLocation as the 1st product. Adding in the categoryId will add that info to both products. – aneroid Commented Feb 1 at 6:12
• Also, you don't have any link between the product and product-categories. And in your 'product-categories' data, the second record has a categoryId but the first does not. Please fix your example data. So even doing a unionWith will still include both products with the Electronics data from category 56541. – aneroid Commented Feb 1 at 6:23
• For your updated current edit - so the product-categories collection has documents in it which repeat the field "product-categories" ? Instead of this back-and-forth about your data, just prepare it in Mongo Playground and then update your question example. – aneroid Commented Feb 1 at 7:26

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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