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.
Hi @Jonas_S
Perhaps try creating a runtime field that concatenates the fields then use lens to aggregate that should work.
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?
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.
1 Like
I think the only way to do that is thru a transform which will aggregate the s field periodically into an array of values.
1 Like
Ok, thanks for the reply @Marco_Liberati. Can you elaborate a bit? Where do i define a transform?
A transform is a process to aggregate the given index into a new index with aggregated data: Transform overview | Elasticsearch Guide [8.9] | Elastic
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..
Feature request: [Dashboard] [Lens] Concatenation Aggregation on .keyword fields in Lens · Issue #165358 · elastic/kibana · GitHub
@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
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:
You are right, thanx for the feedback 
@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.
1 Like
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.