CODEWITHSUNDEEP
elasticsearch
Jothi Kannan
Jothi Kannan

Reputation: 3358

Elastic search find difference in a field using range query

I have to find out how many KWH has been run between two given time. For now, I am having 2 queries to find out last and the first record between the time using asc and desc sorting and doing subtraction to get the KWH value between the time is there any other way to get the KWH without 2 queries

Range query:

"query": {
    "bool": {
      "must": [
        {
          "range": {
            "createdtime": {
              "gte": "1566757800000",
              "lte": "1566844199000",
              "boost": 2.0
            }
          }
        },
        {
          "match": {
            "meter_id": 101
          }
        }
      ]
    }
  },
  "size" : 1,
  "from": 0,
  "sort": { "createdtime" : {"order" : "desc"} }
}

another query is almost same except the order is asc

So both the 2 queries will return the record, and I am doing the subtractions in the result set to find out the differences.

Upvotes: 0

Views: 90

Answers (1)

Andrei Stefan
Andrei Stefan

Reputation: 52368

You could run one query only and use top_hits aggregation to extract the "first" and "last" value, but it won't calculate the difference. You'd still have to do it outside Elasticsearch.

{
    "size": 0,
    "query": {
        "bool": {
          "must": [
            {
              "range": {
                "createdtime": {
                  "gte": "1566757800000",
                  "lte": "1566844199000",
                  "boost": 2.0
                }
              }
            },
            {
              "match": {
                "meter_id": 101
              }
            }
          ]
        }
    },
    "aggs": {
        "range": {
            "filter": {
                "range": {
                    "createddate": {
                        "gte": "2016-08-19T10:00:00",
                        "lte": "2016-08-23T10:00:00"
                    }
                }
            },
            "aggs": {
                "min": {
                    "top_hits": {
                        "sort": [{"createddate": {"order": "asc"}}],
                        "_source": {"includes": [ "kwh_value" ]},
                        "size" : 1
                    }
                },
                "max": {
                    "top_hits": {
                        "sort": [{"createddate": {"order": "desc"}}],
                        "_source": {"includes": [ "kwh_value" ]},
                        "size" : 1
                    }
                }
            }
        }
    }
}

Upvotes: 1

Related Questions