Как написать такой запрос в elastic?

Question

[Антон Рейтаровский]

Есть такой набор данных:

{
    "@timestamp": "2023-05-28T12:12:09.592Z",
    "userId": "Pir6UkHdRXMgZvFHMRttHAT3nXyxzxWw+hsf1iXyMiw=",
    "userSession": "a2942642-9569-452b-a642-cc2f9a80b88c",
    "state": "active"
},
{
    "@timestamp": "2023-05-28T12:15:09.592Z",
    "userId": "Pir6UkHdRXMgZvFHMRttHAT3nXyxzxWw+hsf1iXyMiw=",
    "userSession": "a2942642-9569-452b-a642-cc2f9a80b88c",
    "state": "inactive"
},

как можно агрегировать данные по userId и userSession и получить разницу двох timestamp, тоесть разницу между inactive и active состоянием?

Какой-то такой результат должен быть:

{
    "start":"2023-05-28T12:12:09.592Z",
    "end": "2023-05-28T12:15:09.592Z",
    "userId": "Pir6UkHdRXMgZvFHMRttHAT3nXyxzxWw+hsf1iXyMiw=",
    "userSession": "a2942642-9569-452b-a642-cc2f9a80b88c",
    "dateDiff": "3 minute (или кол.во секунд)"
},

Answer 1

[Mouvdy]

{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "terms": {
            "state": ["active", "inactive"]
          }
        }
      ]
    }
  },
  "aggs": {
    "users": {
      "terms": {
        "field": "userId",
        "size": 10
      },
      "aggs": {
        "sessions": {
          "terms": {
            "field": "userSession",
            "size": 10
          },
          "aggs": {
            "states": {
              "terms": {
                "field": "state",
                "size": 2
              },
              "aggs": {
                "timestamps": {
                  "min": {
                    "field": "@timestamp"
                  }
                }
              }
            },
            "duration": {
              "scripted_metric": {
                "init_script": "state.timestamps = []",
                "map_script": "if (doc['state.keyword'].value == 'active') { state.timestamps.add(doc['@timestamp'].value.toInstant().toEpochMilli()) } else if (doc['state.keyword'].value == 'inactive') { state.timestamps.add(doc['@timestamp'].value.toInstant().toEpochMilli()) }",
                "combine_script": "return state.timestamps",
                "reduce_script": "long duration = 0; if (states.size() == 2) { duration = states[1] - states[0]; } return duration;"
              }
            }
          }
        }
      }
    }
  }
}