Trouble to write a query making two counts on a relative period of time and dividing the counts

Elastic Stack Elasticsearch
1

I have been trying to write a query for a few days now, and I began to worry it won't be possible.

My goal is two make two counts (full-text queries) over a relative period of time (ie the last 24 hours), and my final result must be the division of the first and the second count.

My use case is similar to the example in this page of the doc : https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-bucket-script-aggregation.html.

I have two sets of keywords: set_a and set_b.
I need to perform a first query with set_a U set_b, and then on the results a second query with set_b. Finally, I need to divide the two resulting counts.

Here is the query I have right now:

GET _search
{
  "aggs": {
    "risk": {
      "filters": {
        "filters": {
          "company_and_risk": {
            "match": {
              "text": {
                "query": "apple AND pollution"
              }
            }
          },
          "risk": {
           "match": {
            "text": {
              "query": "pollution"
              }
            }
          }
        }
      },
      "aggs": {
        "risk-percentage": {
          "bucket_script": {
            "buckets_path": {
              "companyAndRisk": "company_and_risk",
              "risk": "risk"
            },
            "script": "params.companyAndRisk / params.risk * 100"
          }
        }
      }
    }
  }
}

I assume I need some intermediary count with value_count for each bucket, but I don't know how to do this with a filters aggregation. Also my buckets_path are wrong, and the filter to get only the last 24 hours data points is missing.

I absolutely need to do all of this at once, I cannot perform multiple queries.

Could anyone help finish this query please ? (or tell me if this is impossible)

2

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