MongoDB - Aggregate: How to group by depending on query

Question

I want to aggregate over a collection where a type is given. The types come from query string and the can be day, month or year. Depending on what type the users chooses I want to group by. For example: If the user chooses "month" I want to group by month.

Event.aggregate([
    {
      $lookup: { from: Product.collection.name, localField: 'product', foreignField: '_id', as: 'product' }
    },
    {
      $group: {
        _id: { $month: { date: "$date" } },
        price: { $sum: "$price" },
        result: { $mergeObjects: { name: "$product.name", _id: "$product._id" } },
        count: { $sum: 1 }
      },
    },
  ]).then(response => {
    console.log(response)
    res.send(response)
  })

I can not figure it out how to find a clean solution. So far the only way I found was to use if conditional before Model.aggregate([])...

  if (req.query.dateAvailability && req.query.dateAvailability === 'month') {
    Event.aggregate([
      {
        $lookup: { from: Product.collection.name, localField: 'product', foreignField: '_id', as: 'product' }
      },
      {
        $group: {
          _id: { $month: { date: "$date" } },
          price: { $sum: "$price" },
          result: { $mergeObjects: { name: "$product.name", _id: "$product._id" } },
          count: { $sum: 1 }
        },
      },
    ]).then(response => {
      console.log(response)
      res.send(response)
    })
  } else if (req.query.dateAvailability && req.query.dateAvailability === 'day') {
    Event.aggregate([
      {
        $lookup: { from: Product.collection.name, localField: 'product', foreignField: '_id', as: 'product' }
      },
      {
        $group: {
          _id: { $dateToString: { format: "%d-%m-%Y", date: "$date" } },
          price: { $sum: "$price" },
          result: { $mergeObjects: { name: "$product.name", _id: "$product._id" } },
          count: { $sum: 1 }
        },
      },
    ]).then(response => {
      console.log(response)
      res.send(response)
    })
  } else if (req.query.dateAvailability && req.query.dateAvailability === 'year') {
    Event.aggregate([
      {
        $lookup: { from: Product.collection.name, localField: 'product', foreignField: '_id', as: 'product' }
      },
      {
        $group: {
          _id: { $year: { date: "$date" } },
          price: { $sum: "$price" },
          result: { $mergeObjects: { name: "$product.name", _id: "$product._id" } },
          count: { $sum: 1 }
        },
      },
    ]).then(response => {
      console.log(response)
      res.send(response)
    })
  }

Model Event:

const EventSchema = new Schema({

  client: {
    type: [{
      type: Schema.Types.ObjectId,
      ref: 'Client'
    }]
  },

  product: {
    type: [{
      type: Schema.Types.ObjectId,
      ref: 'Product'
    }]
  },

  date: {
    type: Date,
    maxlength: 64,
    lowercase: true,
    trim: true
  },

  place: {
    type: String,
    maxlength: 1200,
    minlength: 1,
  },

  price: {
    type: Number
  },

  comment: {
    type: String,
    maxlength: 12000,
    minlength: 1,
  },

  status: {
    type: Number,
    min: 0,
    max: 1,
    default: 0,
    validate: {
      validator: Number.isInteger,
      message: '{VALUE} is not an integer value'
    }
  },
},
  {
    toObject: { virtuals: true },
    toJSON: { virtuals: true }
  },
  {
    timestamps: true
  },
);

Answer 1

There is no magic bullet to your problem the logic has to happen somewhere. Either with an if statement outside the query or a $switch operator inside the query if you are using a version of mongodb 3.4 or greater.

{"$group": {
  "_id":{ 
    "$switch": { 
      "branches": [
        { "case":{ "$eq": [ { "$literal": "day" }, { "$literal": req.query.dateAvailability } ] },
         "then": { $dateToString: { format: "%d-%m-%Y", date: "$date" } } },
        { "case":{ "$eq": [ { "$literal": "month" }, { "$literal": req.query.dateAvailability } ] },
         "then": { $month: { date: "$date" } } },
        { "case":{ "$eq": [ { "$literal": "year" }, { "$literal": req.query.dateAvailability } ] },
         "then": { $year: { date: "$date" } } }
      ], 
      "default": { ... default logic for when dateAvailability isn't set ... } 
    } 
  }
  ... rest of the group operation
} }

Answer 2

There's no magic solution to remove the use of logic, In cases like this it will always be required.

However we can make the code a little sexier:

let groupCond;
if (req.query.dateAvailability && req.query.dateAvailability === 'month') {
    groupCond = { $month: { date: "$date" } };
} else if (req.query.dateAvailability && req.query.dateAvailability === 'day') {
    groupCond = { $dateToString: { format: "%d-%m-%Y", date: "$date" } };
} else if (req.query.dateAvailability && req.query.dateAvailability === 'year') {
    groupCond =  { $year: { date: "$date" } };
}

Event.aggregate([
    {
        $lookup: { from: Product.collection.name, localField: 'product', foreignField: '_id', as: 'product' }
    },
    {
        $group: {
            _id: groupCond,
            price: { $sum: "$price" },
            result: { $mergeObjects: { name: "$product.name", _id: "$product._id" } },
            count: { $sum: 1 }
        },
    },
]).then(response => {
    console.log(response)
    res.send(response)
})