How to make aggregation and group by specific condition in Mongoose, node.js

Working with Data Node.js Frameworks
,
1

Hi,

This is my first topic here. Sorry if it is in bad category.

I write application for learning foreign words (node.js and mongoose), and now i need to generate statictics about users studying.

For that i’m using aggregation. I would check which study has the most notLearnedWords and show category which they come from. Could you help me with that?. What logic should i apply and which operators should i use?

My models:

const studySchema = new mongoose.Schema({
  name: {
    type: String,
  },
  studyDate: {
    type: Date,
    default: Date.now(),
  },
  studyTime: {
    type: Number,
  },
  learnedWords: [String],
  notLearnedWords: [String],
  learnedWordSet: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'WordSet',
  },
  user: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'User',
  },
});


const Study = mongoose.model('Study', studySchema );

WordSet

const wordSetSchema = new mongoose.Schema({
  name: {
    type: String,
  },
  language: {
    type: String,
  },
  category: {
    type: [
      {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'Category',
        required: true,
      },
    ],
  },
  user: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'User',
  },
  words: {
    type: [
      {
        name: {
          type: String,
        },
        translation: {
          type: String,
        },
      },
    ],
  },
});

Category Model:

const categorySchema = new mongoose.Schema({
  name: {
    type: String,
  },
});

And finally agregarion:

exports.getStudyStats = async (req, res) => {
  try {
    const currentUserId = req.user.id;
    const stats = await Study.aggregate([
      {
        $project: {
          _id: 1,
          studyTime: 1,
          notLearnedWords: 1,
          learnedWords: 1,
          user: 1,
          learnedWordSet: 1,
          learnedWords_size: { $size: '$learnedWords' },
          notLearnedWords_size: { $size: '$notLearnedWords' },
        },
      },
      {
        $lookup: {
          from: 'User',
          localField: 'user',
          foreignField: '_id',
          as: 'currentUser',
        },
      },
      {
        $lookup: {
          from: 'WordSet',
          localField: 'learnedWordSet',
          foreignField: '_id',
          as: 'wordSet',
        },
      },
      {
        $match: { user: new ObjectID(currentUserId) },
      },
      {
        $group: {
          _id: null, // calculate for all
          numberOfStudies: { $sum: 1 },
          averageStudyTime: { $avg: '$studyTime' },
          allStudyTime: { $sum: '$studyTime' },
          longestStudy: { $max: '$studyTime' },
          allLearnedWords: { $sum: { $size: '$learnedWords' } },
        },
      },
    ]);
    res.status(200).json({
      status: 'succes',
      data: {
        stats,
      },
    });
  } catch (error) {
    res.status(404).json({
      status: 'fail',
      message: error,
    });
  }
};