Exact count and sum of fields

Elastic Stack Elasticsearch
1

Hi,

I want to query elasticsearch on data we have that is broken down into four distinct fields in a query.

There is a time dimension a customer, a status and a currency and an amount.

effectively what I want to do is the same as the SQL (simplified to illustrate the point)

SELECT day, customer, status, currency, count(id), sum(amount)
FROM   customers
WHERE  day >= :start AND day <:end 
GROUP  BY day, customer, status, currency

The details however are for billing purposes and therefore need to be exact.

The query that kibana generates (when creating a chart) looks like the following:

{
  "aggs": {
    "2": {
      "date_histogram": {
        "field": "day",
        "interval": "1d",
        "time_zone": "UTC",
        "min_doc_count": 1
      },
      "aggs": {
        "3": {
          "terms": {
            "field": "customer.keyword"
          },
          "aggs": {
            "4": {
              "terms": {
                "field": "status.keyword"
              },
              "aggs": {
                "5": {
                  "terms": {
                    "field": "currency.keyword"
                  },
                  "aggs": {
                    "6": {
                      "sum": {
                        "field": "amount"
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  "size": 0,
  "_source": {
    "excludes": []
  },
  "stored_fields": [
    "*"
  ],
  "script_fields": {}
  ],
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "date_created": {
              "format": "strict_date_optional_time",
              "gte": "2019-07-31T23:00:00.000Z",
              "lte": "2019-08-31T22:59:59.999Z"
            }
          }
        }
      ],
      "filter": [
        {
          "match_all": {}
        },
        {
          "match_all": {}
        }
      ],
      "should": [],
      "must_not": []
    }
  }
}

but that seems very verbose I believe its also approximating the count. Is there a way to get the exact value? What would the correct way of approaching this sort of query be?

There are multiple other fields for the record, however they are not needed for this query.

2

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