Mongoose aggregate by date and values

Question

I cannot solve a problem I am having on a project.

First of all, I have a Schema called Emotions, shown below:

import mongoose from 'mongoose';

const EmotionSchema = new mongoose.Schema({
  classification: {
    type: String,
    required: true,
  },
  probability: {
    type: Number,
    required: true,
  },
  user: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'User',
  }
}, {
  timestamps: true,
});

export default mongoose.model('Emotion', EmotionSchema);

And some data like this:

[
  {
    "_id": "61144a393c532f066725bd24",
    "classification": "Feliz",
    "probability": 0.98,
    "user": "60eecfeba0810013e750cdbc",
    "createdAt": "2021-08-11T22:07:53.331Z",
    "updatedAt": "2021-08-11T22:07:53.331Z",
    "__v": 0
  },
  {
    "_id": "61144a46d30bd006fa2be702",
    "classification": "Feliz",
    "probability": 0.98,
    "user": "60eecfeba0810013e750cdbc",
    "createdAt": "2021-08-11T22:08:06.618Z",
    "updatedAt": "2021-08-11T22:08:06.618Z",
    "__v": 0
  },
  {
    "_id": "611541dd62a7f214afab3ceb",
    "classification": "Triste",
    "probability": 0.9,
    "user": "60eecfeba0810013e750cdbc",
    "createdAt": "2021-08-12T15:44:29.150Z",
    "updatedAt": "2021-08-12T15:44:29.150Z",
    "__v": 0
  },
  {
    "_id": "611541f762a7f214afab3cf5",
    "classification": "Raiva",
    "probability": 0.86,
    "user": "60eecfeba0810013e750cdbc",
    "createdAt": "2021-08-12T15:44:55.909Z",
    "updatedAt": "2021-08-12T15:44:55.909Z",
    "__v": 0
  },
  {
    "_id": "6115420362a7f214afab3cf7",
    "classification": "Neutro",
    "probability": 0.99,
    "user": "60eecfeba0810013e750cdbc",
    "createdAt": "2021-08-12T15:45:07.297Z",
    "updatedAt": "2021-08-12T15:45:07.297Z",
    "__v": 0
  },
  {
    "_id": "6115420462a7f214afab3cf9",
    "classification": "Neutro",
    "probability": 0.99,
    "user": "60eecfeba0810013e750cdbc",
    "createdAt": "2021-08-12T15:45:08.002Z",
    "updatedAt": "2021-08-12T15:45:08.002Z",
    "__v": 0
  },
  {
    "_id": "611543d252be9a187c380e5d",
    "classification": "Feliz",
    "probability": 0.91,
    "user": "60eecfeba0810013e750cdbc",
    "createdAt": "2021-08-12T15:52:50.599Z",
    "updatedAt": "2021-08-12T15:52:50.599Z",
    "__v": 0
  }
]

What I want is:

First group each of these records by day. Afterwards, count the values by classification, but separating these classifications.

Something like that:

[
  {
    "_id": "12/08/2021",
    "feliz": 1,
    "triste": 1,
    "raiva": 1,
    "neutro": 2,
    "surpreso": 0,

  },
  {
    "_id": "11/08/2021",
    "feliz": 2,
    "triste": 0,
    "raiva": 0,
    "neutro": 0,
    "surpreso": 0,
  }
]

I did something like that, but it's only working for a value, eg "Feliz". In code:

Emotion.aggregate([
      { $match: 
        {
          user: user._id,
          classification: "Feliz"
        }
      },
      { $group: {
        _id : { $dateToString: { format: "%d/%m/%Y", date: "$createdAt" } },
        feliz: { $sum: 1 }
      }},
    ], function(err, results) {
      if (err) throw err;
      return res.json(results);
    })

And it returns:

[
  {
    "_id": "12/08/2021",
    "feliz": 1
  },
  {
    "_id": "11/08/2021",
    "feliz": 4
  }
]

One more question: From the Client-side, I always get values for "classification" like: "Feliz", "Triste", "Surpreso", "Raiva", "Neutro". So is it better to add an enum to my schema "Emotion"?

(Sorry for my English, i hope you understand).

user_8987128 · Answer

$group by createdAt date and classification, count sum
$group by createdAt only and construct the array of classification and count in key-value pair
$arrayToObject to convert above key-value pair array of object to an object format

Emotion.aggregate([
  { 
    $match: {
      user: user._id,
      classification: "Feliz"
    }
  },
  {
    $group: {
      _id: {
        createdAt: {
          $dateToString: {
            format: "%d/%m/%Y",
            date: "$createdAt"
          }
        },
        classification: "$classification"
      },
      count: { $sum: 1 }
    }
  },
  {
    $group: {
      _id: "$_id.createdAt",
      classifications: {
        $push: {
          k: "$_id.classification",
          v: "$count"
        }
      }
    }
  },
  {
    $addFields: {
      classifications: {
        $arrayToObject: "$classifications"
      }
    }
  }
], 
function(err, results) {
  if (err) throw err;
  return res.json(results);
})

Playground

Note: this will not return classification when count is 0! You need to do this after query in js.

One more question: From the Client-side, I always get values for "classification" like: "Feliz", "Triste", "Surpreso", "Raiva", "Neutro". So is it better to add an enum to my schema "Emotion"?

It is up to your project requirement, you can set these values in enum to restrict the input.

Mongoose aggregate by date and values

1 Answers