Custom elastic query with Enhanced Table

[nomi-gee]

Continuing the discussion from Enhanced Table:

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

[Stratoula_Kalafateli]

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?

[fbaligand (Fabien Baligand)]

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.

[nomi-gee]

{
  "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

[nomi-gee]

does this really work for aggregations as well ??

[Stratoula_Kalafateli]

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.

[nomi-gee]

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

[nomi-gee]

Like i want to subtract these 2 metrics

[Stratoula_Kalafateli]

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

max(cto) - min(cto)

[nomi-gee]

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

[ghudgins (Graham Hudgins)]

you can write the filter in each formula perhaps?

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

[Stratoula_Kalafateli]

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.

[nomi-gee]

Let me try these and will get back to you