如何从Elasticsearch上的同一个表中选择相关事件？

Question

如何在 Elasticsearch 上进行下一个自我查询？

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"}

预期结果应如下所示：

user_id	started_at	end_at
1	2022-02-01T10:15Z	2022-02- 01T10:20Z
2	2022-02-01T10:16Z	2022-02-01T10:21Z
1	2022-02-02T11:01Z	2022-02-02T11:02Z