My collection of documents contains information about users, their sessions and CRUD operations they performed during these sessions:

{
    user_id: '1',
    sessions: [
        {
            actions: [
                {
                    type: 'create',
                    created_at: ISODate('2020-01-01T00:00:00'),
                },
                {
                    type: 'read',
                    created_at: ISODate('2022-01-01T00:00:00'),
                },
                {
                    type: 'read',
                    created_at: ISODate('2021-01-01T00:00:00'),
                }
            ],
        }
    ]
}

I need to get a summary for each user, which includes the amount of CRUD operations and the date of the last one:

{
    user_id: '1',
    actions: [
        {
            type: 'create',
            last: ISODate('2020-01-01T00:00:00'),
            count: 1,
        },
        {
            type: 'read',
            last: ISODate('2022-01-01T00:00:00'),
            count: 2,
        },
        // Problematic part:
        {
            type: 'update',
            last: null,
            count: 0,
        },
        {
            type: 'delete',
            last: null,
            count: 0,
        },
    ]
}

I came up with this solution:

db.users.aggregate([
    {$unwind:'$sessions'},
    {$unwind:'$sessions.actions'},
    {
        $group:{
            _id:{user_id:'$user_id', type:'$sessions.actions.type'},
            last:{$max:'$sessions.actions.created_at'},
            count:{$sum:1},
        }
    },
    {
        $group:{
            _id:{user_id:'$_id.user_id'},
            actions:{$push:{type:'$_id.type', last:'$last', count:'$count'}}
        }
    },
    {
        $project:{
            _id:0,
            user_id: '$_id.user_id',
            actions: '$actions'
        }
    }
])

The problem here is that I cannot figure out, how can I add missing actions, like in 'update' and 'delete' in the example above

CodePudding user response：

Try this,

db.collection.aggregate([
  {
    $unwind: "$sessions"
  },
  {
    $unwind: "$sessions.actions"
  },
  {
    $group: {
      _id: {
        user_id: "$user_id",
        type: "$sessions.actions.type"
      },
      last: {
        $max: "$sessions.actions.created_at"
      },
      count: {
        $sum: 1
      },
      
    }
  },
  {
    $group: {
      _id: {
        user_id: "$_id.user_id"
      },
      actions: {
        $push: {
          type: "$_id.type",
          last: "$last",
          count: "$count"
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      user_id: "$_id.user_id",
      actions: {
        "$function": {
          "body": "function(doc) { const ops = {read:0, delete:0, update: 0, create: 0}; const actions = doc.actions; actions.forEach(action => { ops[action.type] = 1 }); Object.keys(ops).filter(key => ops[key] === 0).forEach(key => actions.push({count: 0, last: null, type: key})); return actions }",
          "args": [
            "$$ROOT"
          ],
          "lang": "js"
        }
      },
    }
  },
])

Here, we use $function and provide a small JS function to populate the missing entries.

Playground link.