I have an index that includes a field called session and a field called timeOnSite. The data can include several docs for each session. The highest value of timeOnSite for a given session is the session duration. I have a need to chart the overall average session duration over time (monthly interval). So, in this scenario, I'm only interested in 1 doc per session (the one with the highest timeOnSite value). I've been able to use collapse within a query in Dev Tools to return only the docs I want, but I can't seem to use collapse within a filter in Discover or a Visualization (I get a malformed query error message every time). If I try to use the UI for building a TSVB Time Series graph, anytime I try to group by the session, I end up with a separate series for each session which is not what I want. I really want to filter by the top value of timeOnSite for each session and use that result as the basis for my visualization.
I'd like to 1) confirm that collapse isn't allowed in Kibana filters and 2) determine if there is another way to do what I want to do. I think this may be the same use case described in this topic, the conclusion of which was that it wasn't possible with the index as is, but I wasn't totally sure it was the same.
I am using Kibana v7.17.3
Sample data with rows highlighted that have the data I'm interested in just to illustrate what I'm describing:
|@timestamp|session|timeOnSite|
|Aug 24, 2022 @ 08:29:25.648|c0446185b215|42,518|
|Aug 24, 2022 @ 08:29:05.300|c0446185b215|22,300|
|Aug 24, 2022 @ 08:28:45.708|c0446185b215|3,050|
|Jul 15, 2022 @ 16:35:00.112|14a42f3ea282|40,537|
|Jul 15, 2022 @ 16:34:20.212|14a42f3ea282|600|
|Jul 10, 2022 @ 10:29:25.648|d5b6c0356bf8|22,477|
|Jul 10, 2022 @ 10:29:03.147|d5b6c0356bf8|508|
|Jun 30, 2022 @ 15:27:26.441|0d4b751b1c44|41,451|
|Jun 30, 2022 @ 15:27:11.200|0d4b751b1c44|25,601|
|Jun 30, 2022 @ 15:26:45.321|0d4b751b1c44|520|
|Jun 30, 2022 @ 15:26:45.216|0d4b751b1c44|415|