Aggregate by concatenate in lens

[Jonas_S]

Hello, i have Kibana 8.7 and am looking for a way to aggregate multiple documents in a table lens by concatenating a string field. Is this possible? I can only find this functionality in TSVB.

[stephenb (Stephen Brown)]

Hi @Jonas_S

Perhaps try creating a runtime field that concatenates the fields then use lens to aggregate that should work.

[Jonas_S]

Hello @stephenb, can you explain or give an example on how to do this with a runtime field? I thought a runtimefield works on the basis of a single document, so i know how i could concatenate different fields within the same document, but not how i should concatenate the same field over multiple documents. Lens will aggregate on a different field a in the document and the string field s of all documents with the same value in a should be concatenated. It also has to correctly update when using control elements on a filter field f.
Example:

{id: 1, a: 100, s: "a", f: "f1"}, 
{id: 2, a: 100, s: "b", f: "f2"}, 
{id: 3, a: 200, s: "c", f: "f2"}

should result in

  a | s
---------
100 | ab
200 | c

and with the control element set to f:f2 the table should only look like this

  a | s
---------
100 | b
200 | c

Can a runtime field achive this?

[stephenb (Stephen Brown)]

@Jonas_S

but not how i should concatenate the same field over multiple documents.

Thanks for clarifying, yes runtime fields work only on a single field so that would not work.

Apologies, I do not see a straightforward way to do this in a lens table.

[Marco_Liberati]

I think the only way to do that is thru a transform which will aggregate the s field periodically into an array of values.

[Jonas_S]

Ok, thanks for the reply @Marco_Liberati. Can you elaborate a bit? Where do i define a transform?

[Marco_Liberati]

A transform is a process to aggregate the given index into a new index with aggregated data: Transform overview | Elasticsearch Guide [8.9] | Elastic

[Jonas_S]

Ok, but how would that solve the problem? As i wrote it needs to correctly update when using a filter so doing the aggregation beforehand into an index doesn't work, right?
If lens is not capable of doing a concatenation aggregation like TSVB can it is impossible, or what am i missing?

But i feel like this is a pretty basic feature that lens should have. Given that TSVB already has it..

[Jonas_S]

Feature request: [Dashboard] [Lens] Concatenation Aggregation on .keyword fields in Lens · Issue #165358 · elastic/kibana · GitHub

[Stratoula_Kalafateli]

@Jonas_S can you give an example of how you can accomplish the above example in TSVB? If I am not mistaken concatenate is only possible with date fields

[Jonas_S]

Hello @Stratoula_Kalafateli, no Concatenate is availabe for .keyword fields:

I removed the names, but here you can see, that the 145 lines for the group get concatenated into a big list:

[Stratoula_Kalafateli]

You are right, thanx for the feedback

[Jonas_S]

@Stratoula_Kalafateli It would be really great if the concatenate function comes to lens. I would use TSVB instead, but i don't see a way of resizing columns, there is no pagination and the sorting on a sum column ignores the filter field. So it sorts like there is no filter set in the control which results in a wrong order.

BTW, aggregation based data table also has this feature:

But i have a field to sum by which needs to be filtered. In lens i can just use Filter by:

But i don't see a way to do this filter on the sum in the aggreagtion based table.
There is this advanced JSON input and maybe it can be used to filter, but i don't know the syntax or where to find information on it.

So i hope lens can get the concatenation aggregation as well.