How is it possible to merge the following "clicks" collection into "sessions" collection:

clicks:

{session_id: 2, time: 12}, 
{session_id: 2, time: 12.1}, 
{session_id: 3, time: 13},

sessions:

{session_id:1, start_time: 1, clicks: 1},
{session_id:2, start_time: 2, clicks: 1}

so that collection "sessions" becomes:

{session_id: 1, start_time: 1, clicks: 1},  // "old" sessions data remains as is 
{session_id: 2, start_time: 2, clicks: 3}, // start_time is still 2, clicks have incremented
{session_id: 3, start_time: 13, clicks: 1} // a session with a new session_id is added

and so that it will be possible to repeat the process when more clicks are added to "clicks" collection filtering it on {time: { $gt: 13 }} (to avoid processing the same clicks again).

CodePudding user response：

Assuming session_id is a unique field(enforced by unique index), you can $group to group the clicks record by session_id first. Then, $merge into sessions collection.

db.clicks.aggregate([
  {
    $group: {
      _id: "$session_id",
      start_time: {
        $min: "$time"
      },
      clicks: {
        $sum: 1
      }
    }
  },
  {
    $project: {
      _id: 0,
      session_id: "$_id",
      start_time: 1,
      clicks: 1
    }
  },
  {
    "$merge": {
      "into": "sessions",
      "let": {
        delta: "$clicks"
      },
      "on": "session_id",
      "whenMatched": [
        {
          $set: {
            clicks: {
              $add: [
                "$clicks",
                "$$delta"
              ]
            }
          }
        }
      ],
      "whenNotMatched": "insert"
    }
  }
])

Mongo Playground