mongodb - 如何从同一个表中选择相关事件？

Question

如何在 MongoDB 上进行下一次自查询？

SELECT e.user_id AS user_id,
       e.datetime AS started_at, 
       (SELECT MIN(datetime)  ## taking the closest "end" event datetime of that userId ##
        FROM events 
        WHERE type = "end" AND 
              user_id = e.user_id AND 
              datetime > e.datetime) AS end_at,
       
FROM events AS e
WHERE e.type = "start" 

下一个事件数据表:

{"_id" : "1", "type": "start", "datetime": "2022-02-01T10:15Z", "userId": "1"},
{"_id" : "2", "type": "end", "datetime": "2022-02-01T10:20Z", "userId": "1"},
{"_id" : "3", "type": "start", "datetime": "2022-02-01T10:16Z", "userId": "2"},
{"_id" : "4", "type": "end", "datetime": "2022-02-01T10:21Z", "userId": "2"},
{"_id" : "5", "type": "start", "datetime": "2022-02-02T11:01Z", "userId": "1"},
{"_id" : "6", "type": "end", "datetime": "2022-02-02T11:02Z", "userId": "1"}

预期的结果应该是这样的:

<表类="s-表"><头>user_id开始时间end_at<正文>12022-02-01T10:15Z2022-02-01T10:20Z22022-02-01T10:16Z2022-02-01T10:21Z12022-02-02T11:01Z2022-02-02T11:02Z

Answer 1

也许是这样的:

db.collection.aggregate([
{
  $sort: {
    "datetime": 1
 }
},
{
  $project: {
     "d": {
           k: "$type",
           v: "$datetime"
        },
          userId: 1
       }
 },
 {
   $group: {
     _id: "$userId",
     e: {
        $push: "$d"
     }
 }
 },
 {
   $addFields: {
     e: {
       $map: {
         input: {
           $range: [
            0,
            {
              $size: "$e"
            },
             2
          ]
         },
         as: "index",
         in: {
           $slice: [
            "$e",
            "$$index",
            2
            ]
          }
        }
      }
    }
  },
 {
   $unwind: "$e"
 },
 {
   $project: {
     events: {
       "$arrayToObject": "$e"
     }
   }
 },
  {
   $project: {
     userId: "$_id",
     start_at: "$events.start",
     end_at: "$events.end",
     _id: 0
   }
  }
 ])

解释:(该解决方案仅在用户事件按顺序开始/结束时才有效)

1. 按日期时间对文档进行排序。
2. 将字段类型和日期时间重命名为 k,v(适用于 $arrayToObject )
3. 按用户 ID 对文档进行分组(请注意，此解决方案的限制是每个用户 ID 的事件总数不得超过 16MB)
4. 按日期/时间对拆分事件(开始+结束，考虑到如果前一个事件尚未完成，用户将无法开始新事件)
5. $展开事件数组
6. 将开始/结束数组转换为对象。
7. 根据预期输出投影字段。

playground

不确定确切的用例是什么，但如果您为每个事件文档添加 sessionId，通常看起来会更实用，这样如果用户可以启动并行 session ，则开始/结束事件可能更容易基于 sessionId 关联。