mongodb query based on multiple properties of the document which returns an array of objects that contain the count of each property

Question

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:

subcategory	category	inStock	outOfStock	pending	items in total
Sour	Food	12	1	3	16
Other	Food	20	8	12	40
Other	Accessories	42	12	17	71

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!

Answer 1

One option is to use $group by both category and subCategory and for this, so the results are one document with all status options per each category and subCategory pair:

EDIT:

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

See how it works on the playground example