Which table to use/how to filter columns in aggreagtion based data table

[Jonas_S]

Hello,

i'm using Kibana 8.7.0 and i have data in this form:
{id: 1, valueToFilterBy: 0, valueToSum: 10, stringValue: "someString1"},
{id: 1, valueToFilterBy: 0, valueToSum: 20, stringValue: "someString2"},
{id: 1, valueToFilterBy: 1, valueToSum: 15, stringValue: "someString3"},
{id: 1, valueToFilterBy: 1, valueToSum: 35, stringValue: "someString4"},
{id: 2, valueToFilterBy: 0, valueToSum: 5, stringValue: "someString5"},
...
I want to create a table with one row per id and columns:
a column with the id,
a column with the sum of all valueToSum with valueToFilterBy = 0 with the same id,
a column with the sum of all valueToSum with valueToFilterBy = 1 with the same id,
a column with a concatenation of all stringValues of this id with valueToFilterBy = 0 and
a column with a concatenation of all stringValues of this id with valueToFilterBy = 1.

So the result with the example should be:
ID | Sum0 | String0 | Sum1 | String1
1 | 30 | someString1, someString2 | 50 | someString3, someString4
2 | 5 | someString5 | - | -

So my first approach was using lens but lens has no concatenation option at all. You can only use last value, but i need to see all stringValues, not just the last, to see which values went into the sum. So i think lens is no option at all.

Next i tried using TSVB Table, which has the Top Hit Aggregation with Aggregate with Concatenate, which does exactly what i'm missing in lens. If this functionality could be integrated into lens it would be great btw. But there are several other problems with TSVB:

1. Compared to Lens it is really ugly
2. No way of resizing columns
3. No pagination
4. The biggest Problem: The sorting on the sum column ignores the filter valueToFilterBy: 0/1, which results in this:
   
   Which would be the correct order without the filter

So the last thing i tried now is to use the aggregation based data table, which looks as nice as lens, with resizeable columns, with pagination and also with the Top Hit Aggregation with Concatenate for my Strings.
But here i'm failing with the very basic thing: The filter.

I add a new Metric, i chose Sum Aggregation and how can i tell this sum now that is should only sum over valueToSum with valueToFilterBy = 0/1? There are no options to set filters. All thats there is this Advanced section:

I tried something like this

from the "help page" link there Sum aggregation | Elasticsearch Guide [8.7] | Elastic
But i don't understand how this documentation compares to the input you have to give. Are there any better resources available on how to use this advanced JSON Input? Is it even possible to filter the sum column with this?

I also saw that you can split the table under buckets and add the filter there, which works as expected, but this also duplicates the id column. And that's not what i want.

Best regards
Jonas

[Jonas_S]

Bump