Examples/help please - how to query arrays of data in TSVB tables?

[Mark_Duncan]

I have a use case, which I need some help with, please.

I'd like to create a metric in TSVB Table, where I can report the sum of a field, across a variable number (not fixed number) of documents.

i.e. if I have docs with 4 fields:

timestamp, qty, price, total spend
(where total spend = qty * price)

I'd like to be able to specify N qty, and get returned total spend values (which I can sum up) from the respective last Y documents (from latest to earliest in order), where their qty's sum up to N.

Where Y could be 1 if N is satisfied by the last/final document, or there could be >1 document returned whose qtys sum up to N.

I hope that is clear enough; I've not had any luck producing in Kibana, yet.

[wylie (Wylie Conlon)]

This type of request is deceptively complicated. Elasticsearch is not meant for this kind of problem in general, except for fetching documents in sorted time order. TSVB is definitely not capable of this, and will never be. Your best and probably only option is to write a Vega visualization. You would need to break this up into stages like this:

1. Collect the most recent 1000 documents (or some other high number) sorted by timestamp
2. Calculate a cumulative sum of the quantity and total spend in sorted order
3. Filter out any documents that have a cumulative sum of quantity > N

If N is intended to be dynamic, and not written into the script, then you also need a special user input section for this. There is only one visualization in Kibana which is capable of doing this, which is Vega.

I put together a sample Vega-Lite visualization which uses kibana sample data to calculate the total price of the first 100ish items sold in the timeframe:

{
  $schema: https://vega.github.io/schema/vega-lite/v4.json
  data: {
    url: {
      %context%: true
      %timefield%: order_date
      index: kibana_sample_data_ecommerce
      body: {
        fields: ["order_date", "total_quantity", "taxful_total_price"],
        _source: false,
        size: 1000
        sort: { "order_date": "asc" }
      }
    }
    format: {property: "hits.hits"}
  }
  transform: [
    {
      "window": [{
        "op": "sum",
        "field": "fields.total_quantity[0]"
        as: "cumulative_quantity"
      }]
      frame: [null, 0]
    }
    {
      "window": [{
        "op": "sum",
        "field": "fields.taxful_total_price[0]"
        as: "cumulative_price"
      }]
      frame: [null, 0]
    }
    {
      "filter": "datum.cumulative_quantity <= 100"
    }
    {
      "aggregate": [{
        "op": "max"
        "field": "cumulative_quantity"
        "as": "max_quantity"
      }, {
        "op": "max"
        "field": "cumulative_price"
        "as": "max_price"
      }]
    }
  ]
  mark: text
  encoding: {
    text: {
      field: "max_price"
      format: "$,.2f"
    }
    size: {
      value: 60
    }
  }
}

[Mark_Duncan]

thanks @wylie! much appreciated.

I've not used the Vega vizs before, and it looks a bit more complex than other Kibana bits.

I'll take a look and see how I get on... Ah, I'm getting this error right now, so need to get Kibana updated:
* The input spec uses vega-lite v4, but current version of vega-lite is 2.6.0.

However, am I right in thinking that Vega produces visualisations and not tables? I'm really looking to do this calculation as one of many metrics in a table. Is there anyway I could produce the metric with Vega and then pull into a table, perhaps?

Out of interest, aside from the Elastic/Kibana docs, are there any other third party Vega resources that will be helpful to look at, too?

Thanks again
Mark

[wylie (Wylie Conlon)]

You're right that it's a lot more complicated than our other visualizations, and that it doesn't produce tables. You could simulate a table layout using Vega.

I wrote some tutorials and reference documentation for Kibana's Vega integration in 7.9: https://www.elastic.co/guide/en/kibana/current/vega-graph.html

I do recommend upgrading to 7.9.1 because Vega-Lite got upgraded from 2 -> 4.