I am working on a nodeJS application using MongoDB and I am struggling to construct a query. I have gone through the MongoDB documentation and the aggregation concept, but still failing to manage it.

So, I have a collection of Item documents like this:

Item schema -> { id: number, status: string, category: string, subCategory: string }

E.g.:

[
...
{
  id: 38219388214034,
  status: 'inStock',
  category: 'Food',
  subCategory: 'Sweet'
},
{
  id: 18371917455611,
  status: 'pending',
  category: 'Accessories',
  subCategory: 'Other'
}
...
]

The status can have only one of the three states: inStock, outOfStock, and pending. The subCategory has the following relation with the category (which is another collection, the Category collection):

Category schema -> { category: string, subCategory: Array<string> }

E.g.:

[
  ...
  { category: 'Food', subCategory: ['Sour', 'Sweet', 'Other'] },
  { category: 'Sports', subCategory: ['T-Shirt', 'Shorts', 'Ball', 'Other'] },
  { category: 'Accessories', subCategory: ['Drill Bits', 'Screws', 'Fluids', 'Other'] }
  ...    
 ]

What I am trying to achieve is to create a query that returns an array of objects that contain the count of each subcategory related to its category, like:

My closest attempt to that, but still wrong, is the following:

const categories = await Categories.find();
let toMatch = [];

categories.forEach(category => {
    toMatch.push({ $and: [{ categoryName: category.categoryName }, { subCategories: category.subCategories }] });
});

const itemsCount = await Item.aggregate([
  { $match: toMatch },
  { $group: { _id: '$status', count: { $sum: 1 }} }
]);

Any help is more than appreciated!

CodePudding user response：

One option is to use $group twice for this, once to group by both subCategory and status and the second time to group only by subCategory, so the results are one document with all status options per each subCategory:

db.items.aggregate([
  {$group: {
      _id: {status: "$status", subCategory: "$subCategory"},
      category: {$first: "$category"},
      count: {$sum: 1}
  }},
  {$group: {
      _id: "$_id.subCategory",
      category: {$first: "$category"},
      totalCount: {$sum: "$count"},
      pending: {$sum: {$cond: [{$eq: ["$_id.status", "pending"]}, "$count", 0]}},
      inStock: {$sum: {$cond: [{$eq: ["$_id.status", "inStock"]}, "$count", 0]}},
      outOfStock: {$sum: {$cond: [{$eq: ["$_id.status", "outOfStock"]}, "$count", 0]}}
    }
  }
])

See how it works on the playground example