# Add a column based on a Bucket Script Aggregation or other columns into a Data Table visualization **URL:** \ **Category:** Kibana\ **Created:** [April 22, 2020, 11:56am UTC](https://discuss.elastic.co/t/add-a-column-based-on-a-bucket-script-aggregation-or-other-columns-into-a-data-table-visualization/229250 "2020-04-22T11:56:29Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![mhd.mousa.hamad](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mhd.mousa.hamad/32/66857_2.png) [@mhd.mousa.hamad](https://discuss.elastic.co/u/mhd.mousa.hamad)\ **Post date:** [April 22, 2020, 11:56am UTC](https://discuss.elastic.co/t/add-a-column-based-on-a-bucket-script-aggregation-or-other-columns-into-a-data-table-visualization/229250/1 "2020-04-22T11:56:30Z")
I am building a Data Table in which I could get two required columns but I need to add one additional column which is the difference between these two. Is that somehow possible? I could get the desired results using a _Bucket Script Aggregation_, but how can I use this in the Data Table? Please consider the following mock-up case for a better explanation. The case is for performance metrics recorded for different machine learning models per customer. We need to show a summary table of the average performance of each model on each measured performance metric on a customer basis. - The index mapping template: ```auto PUT _template/metrics-template { "index_patterns": [ "metrics-*" ], "settings": { "analysis": { "analyzer": { "path-analyzer": { "tokenizer": "path_hierarchy" } } } }, "mappings": { "dynamic": false, "properties": { "path": { "type": "text", "analyzer": "path-analyzer", "search_analyzer": "keyword", "fields": { "raw": { "type": "keyword" } } }, "time": { "type": "date" }, "labels": { "type": "object", "dynamic": true }, "value": { "type": "double" } }, "dynamic_templates": [ { "labels_as_keywords": { "path_match": "labels.*", "mapping": { "type": "keyword" } } } ] } } ``` - The data: ```auto POST _bulk {"index": {"_index": "metrics-01"}} {"path": "models/performance/mse", "time": "2020-04-20T08:00:00.00000Z", "value": 1, "labels": {"customer_id": "1", "model_name": "rf"}} {"index": {"_index": "metrics-01"}} {"path": "models/performance/r2", "time": "2020-04-20T08:00:00.00000Z", "value": 0.5, "labels": {"customer_id": "1", "model_name": "rf"}} {"index": {"_index": "metrics-01"}} {"path": "models/performance/mse", "time": "2020-04-20T08:00:00.00000Z", "value": 2, "labels": {"customer_id": "1", "model_name": "nn"}} {"index": {"_index": "metrics-01"}} {"path": "models/performance/r2", "time": "2020-04-20T08:00:00.00000Z", "value": 0.2, "labels": {"customer_id": "1", "model_name": "nn"}} {"index": {"_index": "metrics-01"}} {"path": "models/performance/mse", "time": "2020-04-21T08:00:00.00000Z", "value": 1.2, "labels": {"customer_id": "1", "model_name": "rf"}} {"index": {"_index": "metrics-01"}} {"path": "models/performance/r2", "time": "2020-04-21T08:00:00.00000Z", "value": 0.5, "labels": {"customer_id": "1", "model_name": "rf"}} {"index": {"_index": "metrics-01"}} {"path": "models/performance/mse", "time": "2020-04-21T08:00:00.00000Z", "value": 2.4, "labels": {"customer_id": "1", "model_name": "nn"}} {"index": {"_index": "metrics-01"}} {"path": "models/performance/r2", "time": "2020-04-21T08:00:00.00000Z", "value": 0.2, "labels": {"customer_id": "1", "model_name": "nn"}} {"index": {"_index": "metrics-01"}} {"path": "models/performance/mse", "time": "2020-04-20T08:00:00.00000Z", "value": 10, "labels": {"customer_id": "2", "model_name": "rf"}} {"index": {"_index": "metrics-01"}} {"path": "models/performance/r2", "time": "2020-04-20T08:00:00.00000Z", "value": 0.8, "labels": {"customer_id": "2", "model_name": "rf"}} {"index": {"_index": "metrics-01"}} {"path": "models/performance/mse", "time": "2020-04-20T08:00:00.00000Z", "value": 20, "labels": {"customer_id": "2", "model_name": "nn"}} {"index": {"_index": "metrics-01"}} {"path": "models/performance/r2", "time": "2020-04-20T08:00:00.00000Z", "value": -1, "labels": {"customer_id": "2", "model_name": "nn"}} {"index": {"_index": "metrics-01"}} {"path": "models/performance/mse", "time": "2020-04-21T08:00:00.00000Z", "value": 12, "labels": {"customer_id": "2", "model_name": "rf"}} {"index": {"_index": "metrics-01"}} {"path": "models/performance/r2", "time": "2020-04-21T08:00:00.00000Z", "value": 0.3, "labels": {"customer_id": "2", "model_name": "rf"}} {"index": {"_index": "metrics-01"}} {"path": "models/performance/mse", "time": "2020-04-21T08:00:00.00000Z", "value": 24, "labels": {"customer_id": "2", "model_name": "nn"}} {"index": {"_index": "metrics-01"}} {"path": "models/performance/r2", "time": "2020-04-21T08:00:00.00000Z", "value": -1, "labels": {"customer_id": "2", "model_name": "nn"}} ``` - The Data Table (what I could achieve) ![Screenshot 2020-04-22 at 13.36.59](https://us1.discourse-cdn.com/elastic/original/3X/2/8/282e8ff25ab853488f88c75371eb802d60d411e9.png) - **What is missing is an additional column showing the difference (division or subtraction) between the _Avg MSE (Yesterday)_ column the _Avg MSE_ column.** - I could get this information using a _Bucket Script Aggregation_ ran using the _Dev Tools_, is there anyway to get these results (run a similar query) into the Data Table? ```auto GET metrics-01/_search { "query": { "match_all": {} }, "size": 0, "aggs": { "customers": { "terms": { "field": "labels.customer_id", "order": { "_key": "asc" }, "size": 500 }, "aggs": { "models": { "terms": { "field": "labels.model_name", "order": { "_key": "asc" }, "size": 10 }, "aggs": { "avg_mse_all": { "filter": { "query_string": { "analyze_wildcard": true, "query": """ path.raw: "models/performance/mse" """ } }, "aggs": { "avg_mse": { "avg": { "field": "value" } } } }, "avg_mse_yesterday": { "filter": { "query_string": { "analyze_wildcard": true, "query": """ path.raw: "models/performance/mse" AND time: [now-1d/d TO now/d] """ } }, "aggs": { "avg_mse": { "avg": { "field": "value" } } } }, "avg_mse_difference": { "bucket_script": { "buckets_path": { "avg_mse_all_value": "avg_mse_all>avg_mse", "avg_mse_yesterday_value": "avg_mse_yesterday>avg_mse" }, "script": "params.avg_mse_yesterday_value - params.avg_mse_all_value" } } } } } } } } ``` I know that some plugins might provide a solution, but I never used any and I am not sure which one is the best option and what are the disadvantages of using kibana plugins. Any help or direction is appreciated. Thank you! ---
**Author:** ![wylie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wylie/32/81794_2.png) [@wylie](https://discuss.elastic.co/u/wylie)\ **Post date:** [April 22, 2020, 6:55pm UTC](https://discuss.elastic.co/t/add-a-column-based-on-a-bucket-script-aggregation-or-other-columns-into-a-data-table-visualization/229250/2 "2020-04-22T18:55:23Z")
Bucket script is not supported for the table, although this is a frequent request. [https://github.com/elastic/kibana/issues/4707](https://github.com/elastic/kibana/issues/4707) ---
**Author:** ![mhd.mousa.hamad](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mhd.mousa.hamad/32/66857_2.png) [@mhd.mousa.hamad](https://discuss.elastic.co/u/mhd.mousa.hamad)\ **Post date:** [April 23, 2020, 6:35am UTC](https://discuss.elastic.co/t/add-a-column-based-on-a-bucket-script-aggregation-or-other-columns-into-a-data-table-visualization/229250/3 "2020-04-23T06:35:57Z")
Thank you @wylie for your reply. I am looking forward to having that feature released. I found [this plugin](https://github.com/fbaligand/kibana-enhanced-table) offering the concept of a _ **Computed Column** _ which is quite handy in such cases of course combined with the feature of hiding columns (which might be the source of the computation). It would be nice if you offer such solutions directly integrated in kibana visualizations. ---
**Author:** ![richcollier](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/richcollier/32/115035_2.png) [@richcollier](https://discuss.elastic.co/u/richcollier)\ **Post date:** [April 23, 2020, 1:44pm UTC](https://discuss.elastic.co/t/add-a-column-based-on-a-bucket-script-aggregation-or-other-columns-into-a-data-table-visualization/229250/4 "2020-04-23T13:44:56Z")
Another possibility might be to use [Transforms](https://www.elastic.co/guide/en/elasticsearch/reference/current/transform-api-quickref.html). Transforms can leverage arbitrary aggregations, including bucket\_script aggregations and it writes the results to a new index. You would then use this new index for your data table. I noticed however, that you use `filter` aggregations. This is only supported in [Transforms in v7.7+](https://www.elastic.co/guide/en/elasticsearch/reference/7.7/put-transform.html) ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [May 21, 2020, 1:46pm UTC](https://discuss.elastic.co/t/add-a-column-based-on-a-bucket-script-aggregation-or-other-columns-into-a-data-table-visualization/229250/5 "2020-05-21T13:46:07Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.