# Custom elastic query with Enhanced Table

**URL:** <https://discuss.elastic.co/t/custom-elastic-query-with-enhanced-table/305384>\
**Category:** Kibana\
**Tags:** vega\
**Created:** [May 23, 2022, 9:41am UTC](https://discuss.elastic.co/t/custom-elastic-query-with-enhanced-table/305384 "2022-05-23T09:41:27Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![nomi-gee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nomi-gee/32/106029_2.png) [@nomi-gee](https://discuss.elastic.co/u/nomi-gee)\
**Post date:** [May 23, 2022, 9:41am UTC](https://discuss.elastic.co/t/custom-elastic-query-with-enhanced-table/305384/1 "2022-05-23T09:41:28Z")

</div>

Continuing the discussion from [Enhanced Table](https://discuss.elastic.co/t/enhanced-table/240817/7):

Kibana Datatable has some limitations. I am using aggregations with a custom elastic query for generating results and sorting on the basis of buckets can Enhanced table give freedom to use custom query like we have in Vega

---

<div class="post-metadata">

**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:** [May 23, 2022, 9:51am UTC](https://discuss.elastic.co/t/custom-elastic-query-with-enhanced-table/305384/2 "2022-05-23T09:51:29Z")

</div>

I am not very familar with the enhanced table plugin.Maybe @fbaligand can help here but can you give us more information on what exactly do you want to do? You don't want to use an index pattern but an ES search query instead?

---

<div class="post-metadata">

**Author:** ![fbaligand](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fbaligand/32/5657_2.png) [@fbaligand](https://discuss.elastic.co/u/fbaligand)\
**Post date:** [May 23, 2022, 10:37am UTC](https://discuss.elastic.co/t/custom-elastic-query-with-enhanced-table/305384/3 "2022-05-23T10:37:02Z")

</div>

I’m sorry but Enhanced Table does not manage full ES queries.  
That said, in filter bar, you can edit filter and enter your custom query using full ES DSL possibilities.

I already used that to make parent/child queries.

---

<div class="post-metadata">

**Author:** ![nomi-gee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nomi-gee/32/106029_2.png) [@nomi-gee](https://discuss.elastic.co/u/nomi-gee)\
**Post date:** [May 23, 2022, 11:01am UTC](https://discuss.elastic.co/t/custom-elastic-query-with-enhanced-table/305384/4 "2022-05-23T11:01:43Z")

</div>

```auto
{
  "aggs": {
    "time_buckets": {
      "terms": {
        "field": "vehicle"
      },
      "aggs": {
        "max_ctd": {
          "filter": {
            "range": {
              "CTD": {
                "gt": 0
              }
            }
          },
          "aggs": {
            "max_ctd_agg": {
              "max": {
                "field": "CTD"
              }
            }
          }
        },
        "min_ctd": {
          "filter": {
            "range": {
              "CTD": {
                "gt": 0
              }
            }
          },
          "aggs": {
            "min_ctd_agg": {
              "min": {
                "field": "CTD"
              }
            }
          }
        },
        "CTD": {
          "bucket_script": {
            "buckets_path": {
              "max_ctd": "max_ctd>max_ctd_agg",
              "min_ctd": "min_ctd>min_ctd_agg"
            },
            "script": "params.max_ctd - params.min_ctd"
          }
        },
        "max_cteu": {
          "filter": {
            "range": {
              "CTEU": {
                "gt": 0
              }
            }
          },
          "aggs": {
            "max_cteu_agg": {
              "max": {
                "field": "CTEU"
              }
            }
          }
        },
        "min_cteu": {
          "filter": {
            "range": {
              "CTEU": {
                "gt": 0
              }
            }
          },
          "aggs": {
            "min_cteu_agg": {
              "min": {
                "field": "CTEU"
              }
            }
          }
        },
        "CTEU": {
          "bucket_script": {
            "buckets_path": {
              "max_cteu": "max_cteu>max_cteu_agg",
              "min_cteu": "min_cteu>min_cteu_agg"
            },
            "script": "params.max_cteu - params.min_cteu"
          }
        },
        "max_cter": {
          "filter": {
            "range": {
              "CTER": {
                "gt": 0
              }
            }
          },
          "aggs": {
            "max_cter_agg": {
              "max": {
                "field": "CTER"
              }
            }
          }
        },
        "min_cter": {
          "filter": {
            "range": {
              "CTER": {
                "gt": 0
              }
            }
          },
          "aggs": {
            "min_cter_agg": {
              "min": {
                "field": "CTER"
              }
            }
          }
        },
        "CTER": {
          "bucket_script": {
            "buckets_path": {
              "max_cter": "max_cter>max_cter_agg",
              "min_cter": "min_cter>min_cter_agg"
            },
            "script": "params.max_cter - params.min_cter"
          }
        },
        "Energy Usage Per Km": {
          "bucket_script": {
            "buckets_path": {
              "ctd": "CTD",
              "cteu": "CTEU"
            },
            "script": "params.cteu/params.ctd"
          }
        },
        "Regenration %": {
          "bucket_script": {
            "buckets_path": {
              "cteu": "CTEU",
              "cter": "CTER"
            },
            "script": "(params.cter/params.cteu)*100"
          }
        }
      }
    }
  },
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "datetime": {
              "gte": "2022-05-20T08:00:00.000Z",
              "lte": "2022-05-21T08:30:13.184Z",
              "format": "strict_date_optional_time"
            }
          }
        }
      ],
      "filter": [],
      "should": [],
      "must_not": []
    }
  },
  "runtime_mappings": {}
}

```

This is my elastic query it includes bucket script bucket sort and aggregation filters i want to show it as a datatable vega-lite working fine for me but it doesn't have a table view

---

<div class="post-metadata">

**Author:** ![nomi-gee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nomi-gee/32/106029_2.png) [@nomi-gee](https://discuss.elastic.co/u/nomi-gee)\
**Post date:** [May 23, 2022, 11:03am UTC](https://discuss.elastic.co/t/custom-elastic-query-with-enhanced-table/305384/5 "2022-05-23T11:03:13Z")

</div>

does this really work for aggregations as well ??

---

<div class="post-metadata">

**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:** [May 23, 2022, 11:09am UTC](https://discuss.elastic.co/t/custom-elastic-query-with-enhanced-table/305384/6 "2022-05-23T11:09:06Z")

</div>

In Lens data table you can give a different filter per metric as you can see in the screenshot below so I guess you could use it for your use case.

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/3/2/326d28525f813363415caa507457dd48acd3e383.png)

---

<div class="post-metadata">

**Author:** ![nomi-gee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nomi-gee/32/106029_2.png) [@nomi-gee](https://discuss.elastic.co/u/nomi-gee)\
**Post date:** [May 23, 2022, 11:22am UTC](https://discuss.elastic.co/t/custom-elastic-query-with-enhanced-table/305384/7 "2022-05-23T11:22:36Z")

</div>

Thank you so much for your suggestion. 1 more question can i use bucket script as well in lens data table ??

---

<div class="post-metadata">

**Author:** ![nomi-gee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nomi-gee/32/106029_2.png) [@nomi-gee](https://discuss.elastic.co/u/nomi-gee)\
**Post date:** [May 23, 2022, 11:26am UTC](https://discuss.elastic.co/t/custom-elastic-query-with-enhanced-table/305384/8 "2022-05-23T11:26:37Z")

</div>

![Screenshot from 2022-05-23 16-25-57](https://us1.discourse-cdn.com/elastic/original/3X/b/d/bd1764adaeb319a71588db44536288abaa5d659d.png)  
Like i want to subtract these 2 metrics

---

<div class="post-metadata">

**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:** [May 23, 2022, 11:39am UTC](https://discuss.elastic.co/t/custom-elastic-query-with-enhanced-table/305384/9 "2022-05-23T11:39:04Z")

</div>

No unfortunately we don't support bucket script. Can't you use formula like that?

```auto
max(cto) - min(cto)

```

---

<div class="post-metadata">

**Author:** ![nomi-gee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nomi-gee/32/106029_2.png) [@nomi-gee](https://discuss.elastic.co/u/nomi-gee)\
**Post date:** [May 23, 2022, 11:46am UTC](https://discuss.elastic.co/t/custom-elastic-query-with-enhanced-table/305384/10 "2022-05-23T11:46:32Z")

</div>

☹ Different Metrics have a different set of filters. Do we have any third party table plugin to achieve this ??

---

<div class="post-metadata">

**Author:** ![ghudgins](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ghudgins/32/138532_2.png) [@ghudgins](https://discuss.elastic.co/u/ghudgins)\
**Post date:** [May 23, 2022, 12:08pm UTC](https://discuss.elastic.co/t/custom-elastic-query-with-enhanced-table/305384/11 "2022-05-23T12:08:29Z")

</div>

you can write the filter in each formula perhaps?

`max(cto, kql='myMetric: "filter"') - min(cto, kql='myMetric: "filter"')`

---

<div class="post-metadata">

**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:** [May 23, 2022, 12:10pm UTC](https://discuss.elastic.co/t/custom-elastic-query-with-enhanced-table/305384/12 "2022-05-23T12:10:23Z")

</div>

Exactly for the formula metric you can do what Graham proposes. For the other two metrics as I told you above, you can select Advanced options and then Filter by to add your filter.

---

<div class="post-metadata">

**Author:** ![nomi-gee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nomi-gee/32/106029_2.png) [@nomi-gee](https://discuss.elastic.co/u/nomi-gee)\
**Post date:** [May 23, 2022, 12:19pm UTC](https://discuss.elastic.co/t/custom-elastic-query-with-enhanced-table/305384/13 "2022-05-23T12:19:46Z")

</div>

Let me try these and will get back to you

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [June 20, 2022, 12:20pm UTC](https://discuss.elastic.co/t/custom-elastic-query-with-enhanced-table/305384/14 "2022-06-20T12:20:14Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.