'How to select related events from the same table on Elasticsearch?

How do I make the next self query on 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" 

Over the next event data table:

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

The expected result should look like:

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


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source