CODEWITHSUNDEEP
javaelasticsearchkibanadsl
Harmeet Kaur
Harmeet Kaur

Reputation: 164

Elastic search query for avg on basis of condition

I have written an elastic search query the payload looks like

"userData": [ { "sub":1234, "value":678,"condition" :"A" },{ "sub":1234, "value":678,"condition" :"B" }]

Query :

{ 
  "aggs": { 
    "student_data": { 
      "date_histogram": { 
        "field":"@timestamp",
        "calendar_interval":"minute"
      },
      "aggs": { 
        "user_avg": { 
          "avg": { 
            "field":"value"
          }
        }
      }
    }
  }
}

The result gives the avg for both the values i.e. for condition A and for condition B also but what I actually want is the avg for just condition B on the basis of the time period for min interval.

Upvotes: 2

Views: 859

Answers (1)

Assael Azran
Assael Azran

Reputation: 2993

Base on your payload I assume userData is a nested type.

This is a working example of how to average every single condition (nested inner):

Mapping

PUT my_index
{
  "mappings": {
    "properties": {
      "@timestamp": {
        "type": "date"
      },
      "userData": {
        "type": "nested",
        "properties": {
          "condition": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "sub": {
            "type": "long"
          },
          "value": {
            "type": "long"
          }
        }
      }
    }
  }
}

Index few documents

PUT my_index/_doc/1
{
  "@timestamp": "2019-11-08T11:15:24.188Z",
  "userData": [
    {
      "sub": 1234,
      "value": 678,
      "condition": "A"
    },
    {
      "sub": 1234,
      "value": 620,
      "condition": "B"
    }
  ]
}

PUT my_index/_doc/2
{
  "@timestamp": "2019-11-08T11:15:44.188Z",
  "userData": [
    {
      "sub": 1234,
      "value": 680,
      "condition": "A"
    },
    {
      "sub": 1234,
      "value": 640,
      "condition": "B"
    }
  ]
}

Search query

GET my_index/_search
{
  "size": 0,
  "aggs": {
    "student_data": {
      "date_histogram": {
        "field": "@timestamp",
        "calendar_interval": "minute"
      },
      "aggs": {
        "data": {
          "nested": {
            "path": "userData"
          },
          "aggs": {
            "condition": {
              "terms": {
                "field": "userData.condition.keyword"
              }, 
              "aggs": {
                "user_avg": {
                  "avg": {
                    "field": "userData.value"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

Results

"student_data" : {
  "buckets" : [
    {
      "key_as_string" : "2019-11-08T11:15:00.000Z",
      "key" : 1573211700000,
      "doc_count" : 2,
      "data" : {
        "doc_count" : 4,
        "condition" : {
          "doc_count_error_upper_bound" : 0,
          "sum_other_doc_count" : 0,
          "buckets" : [
            {
              "key" : "A",
              "doc_count" : 2,
              "user_avg" : {
                "value" : 679.0
              }
            },
            {
              "key" : "B",
              "doc_count" : 2,
              "user_avg" : {
                "value" : 630.0
              }
            }
          ]
        }
      }
    }
  ]
}

Hope this helps

Upvotes: 2

Related Questions