How to divide two fields and get an average

Question

I am using Elastic 6.5

I need to divide two fields together and then get an average value.

Example mysql that I am trying to recreate:

SELECT avg(field1/field2)
FROM table
WHERE field1 > 0 && field2 > 0

Answer 1

You can achieve what you are looking for using Script Fields.

I've created sample mapping, documents, the query and the response for this use case which are mentioned below:

Mapping

PUT myindex
{  
   "mappings":{  
      "mydocs":{  
         "properties":{  
            "field1":{  
               "type":"long"
            },
            "field2":{  
               "type":"long"
            }
         }
      }
   }
}

Sample Documents:

POST myindex/mydocs/1
{
  "field1": 0,
  "field2": 0
}
POST myindex/mydocs/2
{
  "field1": 20,
  "field2": 20
}
POST myindex/mydocs/3
{
  "field1": 40,
  "field2": 20
}
POST myindex/mydocs/4
{
  "field1": 40,
  "field2": 0
}

Query Request

POST <your_index_name>/_search
{  
   "_source":"*",
   "query":{  
      "match_all":{  

      }
   },
   "script_fields":{  
      "average":{  
         "script":"long a1 = 0l;long a2 = 0l;if(params._source.field1==null){a1 = 01;} else {a1 = params._source.field1}if(params._source.field2==null || params._source.field2<0){ a2=0l} else {a2 = params._source.field2}return a2 > 0? (a1/a2) : 0;"
      }
   }
}

Note that you can modify the above logic accordingly to fit your use case of averaging only if fields > 0

Response

{
  "took": 12,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "hits": {
    "total": 4,
    "max_score": 1,
    "hits": [
      {
        "_index": "myindex",
        "_type": "mydocs",
        "_id": "2",
        "_score": 1,
        "_source": {
          "field1": 20,
          "field2": 20
        },
        "fields": {
          "average": [
            1
          ]
        }
      },
      {
        "_index": "myindex",
        "_type": "mydocs",
        "_id": "4",
        "_score": 1,
        "_source": {
          "field1": 40,
          "field2": 0
        },
        "fields": {
          "average": [
            0
          ]
        }
      },
      {
        "_index": "myindex",
        "_type": "mydocs",
        "_id": "1",
        "_score": 1,
        "_source": {
          "field1": 0,
          "field2": 0
        },
        "fields": {
          "average": [
            0
          ]
        }
      },
      {
        "_index": "myindex",
        "_type": "mydocs",
        "_id": "3",
        "_score": 1,
        "_source": {
          "field1": 40,
          "field2": 20
        },
        "fields": {
          "average": [
            2
          ]
        }
      }
    ]
  }
}

What you are looking for in the above response is the average field.

Hope it helps!

Answer 2

I have solved my task with the query below:

{
  "size": 0,
  "query": { 
    "match_all":{}
  },
  "aggs": {
    "average": {
      "avg": {
        "script": {
          "source": "(doc['field1'].empty || doc['field2'].empty || doc['field2'].value < 1  ? null : doc['field1'].value / doc['field2'].value)"
        }
      }
    }
  }
}