Mongoose Aggregate & Sum conditonally

user_13541914 0
Viewed 33

I've struggled with this question for the whole day, and so I come to you with it now.

I have a set of products defined liked that :

// Product schema
{
 brand: String,
 size: String,
 colour: String,
 ...
}

I am looking for all items from one brand and with the following filter :

// Standard filtered search
const match = {
 brand: 'brand1'
}
Product.find(match)

It returns me two results :

// Result set
{ brand: 'brand1', size: 'M', colour: 'RED', ... },
{ brand: 'brand1', size: 'S', colour: 'GREEN', ... }

Now to the problem i face :
I would like to get out, for each filter (brand, size, colour, ...), a list of grouped possible values (red, green, M, S, brand1, brand2, ...), and their corresponding sum in the actual filtered result set.
I don't know how to explain it correctly, but here is the result I would like :

[{
    "brand": [
        {
            "_id": "brand1",
            "count": 2
        }, {
            "_id": "brand2",
            "count": 0
        }, {
            "_id": "brand3",
            "count": 0
        }
    ],
    "size": [
        {
            "_id": "M",
            "count": 1
        }, {
            "_id": "S",
            "count": 1
        }, {
            "_id": "L",
            "count": 0
        }
    ],
    "colour": [
        {
            "_id": "RED",
            "count": 1
        }, {
            "_id": "GREEN",
            "count": 1
        }, {
            "_id": "ORANGE",
            "count": 0
        }
    ],
}]

What I tried I manage to get this structure with the following aggregate query ; It groups all the keys, but sums them all up, representing the global database, and not the filtered one. If I add a { $match: ... } before the $facet, it groups only the available keys of the filter, and omits the ones that should be zeroed ...

// Doesn't work : All the keys with sum 0 are ignored
Product.aggregate([
    { 
        $facet: {
          brand: [{ $sortByCount: "$brand" }],
          size: [{ $sortByCount: "$size" }],
          colour: [{ $sortByCount: "$colour" }],
          ...
        }
    }    
])

// Doesn't work : All keys are present, but the sums are not right
Product.aggregate([
    {
        $match:  { brand: "brand1" }
    },
    { 
        $facet: {
          brand: [{ $sortByCount: "$brand" }],
          size: [{ $sortByCount: "$size" }],
          colour: [{ $sortByCount: "$colour" }],
          ...
        }
    }    
])

I hope It was clear, I did the best I could to explain my problem, and I would be gratful if someone could bring an idea to the table :)

If you need more information, please just ask.

mongoose
1 Answers
  • $set to create a field like searchBrand, check your condition if brand is your search branch then set it to 1 otherwise 0
  • $group by specific field in facet and count that searchBrand field
  • $sort by count in descending order
db.collection.aggregate([
  {
    $set: {
      searchBrand: {
        $cond: [{ $eq: ["$brand", "brand1"] }, 1, 0]
      }
    }
  },
  {
    $facet: {
      brand: [
        {
          $group: {
            _id: "$brand",
            count: { $sum: "$searchBrand" }
          }
        },
        { $sort: { count: -1 } }
      ],
      size: [
        {
          $group: {
            _id: "$size",
            count: { $sum: "$searchBrand" }
          }
        },
        { $sort: { count: -1 } }
      ],
      colour: [
        {
          $group: {
            _id: "$colour",
            count: { $sum: "$searchBrand" }
          }
        },
        { $sort: { count: -1 } }
      ]
    }
  }
])

Playground

user_89871280
Related