List documents from a collection and count their occurrence in another collection-CodePudding

I am new to MongoDB aggregations and I am scratching my head on how to do something similar to this with Mongo:

Example SQL Query (if this was a relational DB):

select id, name, (select max(createdAt) from events where user_id=u.id) 
from users u

Or..

select users.id, users.name, max(events.created_at) 
from users inner join events on users.id=events.user_id
group by users.id, users.name

The end result is the same. I'd like to list all users, together with a max date of their latest event.

How to accomplish this on Mongo?

Assuming I have a users and events collection with same fields.

I am figuring I should start with a $lookup, which brings me the events together with the user document.

[{
    $match: {
        accountId: '629a251af534a3600aa1a150'
    }
}, {
    $lookup: {
        from: 'productevents',
        localField: 'id',
        foreignField: 'userId',
        as: 'userEvents'
    }
}, {}]

Just not sure what to do next in order to count the get max(created_at) from the remaining events collection.

Sample data:

Users:

{
  "id": "1",
  "accountId": "629a251af534a3600aa1a150",
  "name": "Some User",
  "createdAt": {
    "$date": {
      "$numberLong": "1654269244479"
    }
  },
  "properties": {
    "age": "39"
  },
  "__v": 0
}

ProductEvents:

{
  "name": "login",
  "accountId": "629a251af534a3600aa1a150",
  "userId": "1",
  "groupId": "1",
  "properties": {
    "client": "mobile"
  },
  "createdAt": {
    "$date": {
      "$numberLong": "1654269289432"
    }
  },
  "__v": 0
}

CodePudding user response：

Found a way to do it (although not sure it's the optimal one).

[
  {
    '$lookup': {
      'from': 'productevents', 
      'localField': 'id', 
      'foreignField': 'userId', 
      'as': 'events'
    }
  }, {
    '$unwind': {
      'path': '$events', 
      'preserveNullAndEmptyArrays': true
    }
  }, {
    '$group': {
      '_id': {
        'id': '$id', 
        'name': '$name', 
        '_id': '$_id', 
        'createdAt': '$createdAt', 
        'properties': '$properties'
      }, 
      'lastActivity': {
        '$max': '$events.createdAt'
      }
    }
  }, {
    '$project': {
      '_id': '$_id._id', 
      'id': '$_id.id', 
      'createdAt': '$_id.createdAt', 
      'properties': '$_id.properties', 
      'lastActivity': 1
    }
  }
]

CodePudding user response：

You can use the $lookup pipeline to optimize, by getting only what you need form the productevents collection:

db.users.aggregate([
  {
    $lookup: {
      from: "productevents",
      let: {id: "$id"},
      pipeline: [
        {$match: {$expr: {$eq: ["$userId", "$$id"]}}}
        {$sort: {createdAt: -1}},
        {$limit: 1},
        {$project: {createdAt: 1, _id: 0}}
      ],
      as: "lastActivity"
    }
  },
  {
    $set: {lastActivity: {$arrayElemAt: ["$lastActivity", 0]}}
  },
  {
    $project: {
      id: 1,
      createdAt: 1,
      properties: 1,
      lastActivity: "$lastActivity.createdAt"
    }
  }
])

Playground example