Question 118553 in Mongodb Question posted in
The official documentation can be found here.

how to count every matches between two collection in mongodb

i have a collection of category and a collection of products, each product has a categoryId field that has an id of a perticular category. I want to know how many products are there for each category.

const categorySchema = mongoose.Schema({
  name: String,
});

const productSchema = mongoose.Schema({
  name: { type: String, required: true },
  categoryId: [{ type: mongoose.Schema.Types.ObjectId, ref: 'Category' }],
});


categoryCollection = [
  { name: "cat1", _id: id1 },
  { name: "cat2", _id: id2 },
  { name: "cat3", _id: id3 },
  { name: "cat4", _id: id4 },
  { name: "cat5", _id: id5 },
]

productsCollection = [
  {
    name: "product1",
    categoryId: [id1, id2]
  },
  {
    name: "product2",
    categoryId: [id1, id3]
  }
]

i want the result like this result = [{cat1:2,cat2:1,cat3:1,cat4:0,cat5:0}]

i was trying to do it like first getting all the ids from the category collection and matching them with the categoryId field but it returns only the matched products and i am unable to get the count for each product

ids=[id1,id2,id3,id4,id5]

Prodcut.find({categoryId:{$in:ids}})
Tags:

2

Answers


  1. 0

    So you have the right idea. Once you have all the products, you can simply add another function to parse through the returned documents, and count how many products there are.

    If you want the map entirely stored as a single item in the result array (i.e [{id1: 0, id2: 0}]) then do this:

    const grab_products = async(ids= [id1, id2]) =>{
      const all_products = await Prodcut.find({categoryId:{$in:ids}})
      //create a map to separate and categorize all products into their 
       // respective categories
       const id_map = {}
      for(let id of ids){
         id_map[id] = 0
      }
      //go through each product and insert them into the map with their category
      for(let product of all_products){
         const category = id_map[product.categoryId]
         //increment count value
         category++
      }
      //[{id1: product_count, id2: product_count, etc}]
      return [id_map]
}

    If you want you want them as seperate items in the arr like [{id:1},{id: 2}, etc]

    const grab_products = async(ids= [id1, id2]) =>{
      const all_products = await Prodcut.find({categoryId:{$in:ids}})
      //create a map to separate and categorize all products into their 
       // respective categories
       const id_map = {}
      for(let id of ids){
         id_map[id] = []
      }
      //go through each product and insert them into the map with their category
      for(let product of all_products){
         const category = id_map[product.categoryId]
         category.push(product)
      }
      //Now ids_map hold the products categorized, and you can read 
      //each key, and grab the length for every respective category
      //returns product count for category, [id1: product_count, id2: product_count, etc.]
      const result = Object.keys(id_map).map((key) =>{
         return {key: id_map[key].length}
      })
      return result
}
    Login or Signup to reply.
  2. 0

    try using lookup in mongo aggregation.

    Category.aggregate([
  {
  _id: {
     $in:[
        ids //make sure to add it in objectid formats
     ]
    }
  },
  {
    '$lookup': {
      'from': 'product', 
      'localField': '_id', 
      'foreignField': 'categoryId', 
      'as': 'products'
    }
  }, {
    '$addFields': {
      'total': {
        '$size': '$products'
      }
    }
  }, {
    '$project': {
      '_id': 1, 
      'name': 1, 
      'total': 1
    }
  }
])

    this aggregation will generate response like this

    
[{
   "name":"cat1", 
   "total":2 
},
{
   "name":"cat2", 
   "total":1 
},
{
   "name":"cat3", 
   "total":1 
},
{
   "name":"cat4", 
   "total":0
},
{
   "name":"cat5", 
   "total":0
}]

    • if you want to add any match in category field then you can add it before
      the lookup stage.

    • if you need match in lookup then add after lookup stage

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search