# Aggregate by concatenate in lens **URL:** \ **Category:** Kibana\ **Tags:** lens\ **Created:** [August 28, 2023, 4:12pm UTC](https://discuss.elastic.co/t/aggregate-by-concatenate-in-lens/341820 "2023-08-28T16:12:17Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![Jonas\_S](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jonas_s/32/97631_2.png) [@Jonas\_S](https://discuss.elastic.co/u/Jonas_S)\ **Post date:** [August 28, 2023, 4:12pm UTC](https://discuss.elastic.co/t/aggregate-by-concatenate-in-lens/341820/1 "2023-08-28T16:12:17Z")
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. ---
**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\ **Post date:** [August 28, 2023, 4:25pm UTC](https://discuss.elastic.co/t/aggregate-by-concatenate-in-lens/341820/2 "2023-08-28T16:25:16Z")
Hi @Jonas_S Perhaps try creating a runtime field that concatenates the fields then use lens to aggregate that should work. ---
**Author:** ![Jonas\_S](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jonas_s/32/97631_2.png) [@Jonas\_S](https://discuss.elastic.co/u/Jonas_S)\ **Post date:** [August 29, 2023, 9:04am UTC](https://discuss.elastic.co/t/aggregate-by-concatenate-in-lens/341820/3 "2023-08-29T09:04:24Z")
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: ```auto {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 ```auto a | s --------- 100 | ab 200 | c ``` and with the control element set to `f:f2` the table should only look like this ```auto a | s --------- 100 | b 200 | c ``` Can a runtime field achive this? ---
**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\ **Post date:** [August 29, 2023, 3:10pm UTC](https://discuss.elastic.co/t/aggregate-by-concatenate-in-lens/341820/4 "2023-08-29T15:10:27Z")
> [@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. ---
**Author:** ![Marco\_Liberati](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marco_liberati/32/82953_2.png) [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)\ **Post date:** [August 29, 2023, 3:28pm UTC](https://discuss.elastic.co/t/aggregate-by-concatenate-in-lens/341820/5 "2023-08-29T15:28:43Z")
I think the only way to do that is thru a `transform` which will aggregate the `s` field periodically into an array of values. ---
**Author:** ![Jonas\_S](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jonas_s/32/97631_2.png) [@Jonas\_S](https://discuss.elastic.co/u/Jonas_S)\ **Post date:** [August 29, 2023, 3:37pm UTC](https://discuss.elastic.co/t/aggregate-by-concatenate-in-lens/341820/6 "2023-08-29T15:37:12Z")
Ok, thanks for the reply @Marco_Liberati. Can you elaborate a bit? Where do i define a `transform`? ---
**Author:** ![Marco\_Liberati](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marco_liberati/32/82953_2.png) [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)\ **Post date:** [August 30, 2023, 7:21am UTC](https://discuss.elastic.co/t/aggregate-by-concatenate-in-lens/341820/7 "2023-08-30T07:21:19Z")
A `transform` is a process to aggregate the given index into a new index with aggregated data: [Transform overview | Elasticsearch Guide [8.9] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/transform-overview.html) ---
**Author:** ![Jonas\_S](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jonas_s/32/97631_2.png) [@Jonas\_S](https://discuss.elastic.co/u/Jonas_S)\ **Post date:** [August 30, 2023, 1:10pm UTC](https://discuss.elastic.co/t/aggregate-by-concatenate-in-lens/341820/8 "2023-08-30T13:10:20Z")
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.. ---
**Author:** ![Jonas\_S](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jonas_s/32/97631_2.png) [@Jonas\_S](https://discuss.elastic.co/u/Jonas_S)\ **Post date:** [August 31, 2023, 3:45pm UTC](https://discuss.elastic.co/t/aggregate-by-concatenate-in-lens/341820/9 "2023-08-31T15:45:21Z")
Feature request: [[Dashboard] [Lens] Concatenation Aggregation on .keyword fields in Lens · Issue #165358 · elastic/kibana · GitHub](https://github.com/elastic/kibana/issues/165358) ---
**Author:** ![Stratoula\_Kalafateli](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stratoula_kalafateli/32/70923_2.png) [@Stratoula\_Kalafateli](https://discuss.elastic.co/u/Stratoula_Kalafateli)\ **Post date:** [September 1, 2023, 12:43pm UTC](https://discuss.elastic.co/t/aggregate-by-concatenate-in-lens/341820/10 "2023-09-01T12:43:23Z")
@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 ![image](https://us1.discourse-cdn.com/elastic/original/3X/4/3/43db8405e853d7ea9b6aefb716ea944102780cb8.png) ---
**Author:** ![Jonas\_S](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jonas_s/32/97631_2.png) [@Jonas\_S](https://discuss.elastic.co/u/Jonas_S)\ **Post date:** [September 4, 2023, 8:34am UTC](https://discuss.elastic.co/t/aggregate-by-concatenate-in-lens/341820/11 "2023-09-04T08:34:43Z")
Hello @Stratoula_Kalafateli, no Concatenate is availabe for .keyword fields: ![image](https://us1.discourse-cdn.com/elastic/original/3X/f/a/fa122d349a45bdd576ee14131cbb08bd44e37569.png) I removed the names, but here you can see, that the 145 lines for the group get concatenated into a big list: ![image](https://us1.discourse-cdn.com/elastic/original/3X/1/e/1ef18f5b23d726df8d2fdfd62d951f325722b8f5.png) ---
**Author:** ![Stratoula\_Kalafateli](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stratoula_kalafateli/32/70923_2.png) [@Stratoula\_Kalafateli](https://discuss.elastic.co/u/Stratoula_Kalafateli)\ **Post date:** [September 4, 2023, 8:48am UTC](https://discuss.elastic.co/t/aggregate-by-concatenate-in-lens/341820/12 "2023-09-04T08:48:48Z")
You are right, thanx for the feedback 🙂 ---
**Author:** ![Jonas\_S](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jonas_s/32/97631_2.png) [@Jonas\_S](https://discuss.elastic.co/u/Jonas_S)\ **Post date:** [September 4, 2023, 8:58am UTC](https://discuss.elastic.co/t/aggregate-by-concatenate-in-lens/341820/13 "2023-09-04T08:58:40Z")
@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: ![image](https://us1.discourse-cdn.com/elastic/original/3X/8/e/8e9ab51da30d967d71a50e5e108881d86563c4c6.png) But i have a field to sum by which needs to be filtered. In lens i can just use Filter by: ![image](https://us1.discourse-cdn.com/elastic/original/3X/3/e/3e6a8b33a356b80223acc8a109acdb71cb68affd.png) 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. ![image](https://us1.discourse-cdn.com/elastic/original/3X/2/9/294d3a81c279bccd134685f1ac7db03262de7813.png) So i hope lens can get the concatenation aggregation as well. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [October 2, 2023, 8:59am UTC](https://discuss.elastic.co/t/aggregate-by-concatenate-in-lens/341820/14 "2023-10-02T08:59:35Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.