elasticsearch aggregate on list of objects with condition

Question

Say we have following documents in elasticsearch:

[{
  "person": {
    'name': 'asqar'
  },
  "bill": [
    {
      code:2,
      value: 210
    },
    {
      code:3,
      value: 330
    },
    {
      code:8,
      value: 220
    },
  ]
},
{
  "person": {
    'name': 'asqar'
  },
  "bill": [
    {
      code:2,
      value: 340
    },
    {
      code:4,
      value: 340
    },
    {
      code:1,
      value: 200
    },
  ]
},
{
  "person": {
    'name': 'asqar'
  },
  "bill": [
    {
      code:2,
      value: 810
    },
    {
      code:4,
      value: 630
    },
    {
      code:8,
      value: 220
    },
  ]
}]

I want to apply aggregate function on specific object in the bill array iwth some condition, for example I want calculate avg of value which its code is 2.

Possible duplicate of [How to get an Elasticsearch aggregation with multiple fields](https://stackoverflow.com/questions/30728583/how-to-get-an-elasticsearch-aggregation-with-multiple-fields) — ollik1, May 28 '19 at 15:23

score 2 · Answer 1 · answered May 29 '19 at 04:05

You need to first make sure that the bill field is of nested type. Then you can use nested aggregation to deal with nested documents. You can use terms aggregation on bill.code and a child avg aggregation on field bill.value to this terms aggregation. This will give you average value for each code. Now since you want only aggregation against the code 2, you can make use of bucket selector aggregation to filter and get only bucket with code 2.

So the final aggregation query will look as below:

{
  "aggs": {
    "VALUE_NESTED": {
      "nested": {
        "path": "bill"
      },
      "aggs": {
        "VALUE_TERM": {
          "terms": {
            "field": "bill.code"
          },
          "aggs": {
            "VALUE_AVG": {
              "avg": {
                "field": "bill.value"
              }
            },
            "CODE": {
              "max": {
                "field": "bill.code"
              }
            },
            "CODE_FILTER": {
              "bucket_selector": {
                "buckets_path": {
                  "code": "CODE"
                },
                "script": "params.code == 2"
              }
            }
          }
        }
      }
    }
  }
}

Sample o/p for above:

"aggregations": {
  "VALUE_NESTED": {
    "doc_count": 9,
    "VALUE_TERM": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": 2,
          "doc_count": 3,
          "CODE": {
            "value": 2
          },
          "VALUE_AVG": {
            "value": 453.3333333333333
          }
        }
      ]
    }
  }
}

score 2 · Accepted Answer · answered May 29 '19 at 04:06

Field bill needs to be created as nested object to filter on it.

You can then use filter aggregation

Mapping:

PUT testindex/_mapping
{
  "properties": {
    "person": {
      "properties": {
        "name": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        }
      }
    },
    "bill": {
      "type": "nested",
      "properties": {
        "code": {
          "type": "integer"
        },
        "value":{
          "type": "double"
        }
      }
    }
  }
}

Data:

    "hits" : [
      {
        "_index" : "testindex",
        "_type" : "_doc",
        "_id" : "422tAWsBd-1D6Ztt1_Tb",
        "_score" : 1.0,
        "_source" : {
          "person" : {
            "name" : "asqar"
          },
          "bill" : [
            {
              "code" : 2,
              "value" : 210
            },
            {
              "code" : 3,
              "value" : 330
            },
            {
              "code" : 8,
              "value" : 220
            }
          ]
        }
      },
      {
        "_index" : "testindex",
        "_type" : "_doc",
        "_id" : "5G2uAWsBd-1D6ZttpfR9",
        "_score" : 1.0,
        "_source" : {
          "person" : {
            "name" : "asqar"
          },
          "bill" : [
            {
              "code" : 2,
              "value" : 340
            },
            {
              "code" : 4,
              "value" : 340
            },
            {
              "code" : 1,
              "value" : 200
            }
          ]
        }
      },
      {
        "_index" : "testindex",
        "_type" : "_doc",
        "_id" : "5W2vAWsBd-1D6ZttQfQ_",
        "_score" : 1.0,
        "_source" : {
          "person" : {
            "name" : "asqar"
          },
          "bill" : [
            {
              "code" : 2,
              "value" : 810
            },
            {
              "code" : 4,
              "value" : 630
            },
            {
              "code" : 8,
              "value" : 220
            }
          ]
        }
      }
    ]

Query:

GET testindex/_search
{
  "size": 0, 
  "aggs": {
    "terms_agg": {
      "terms": {
        "field": "person.name.keyword"
      },
      "aggs": {
        "bill": {
          "nested": {
            "path": "bill"
          },
          "aggs": {
            "bill_code": {
              "filter": {
                "term": {
                  "bill.code": 2
                }
              },
              "aggs": {
                "average": {
                  "avg": {
                    "field": "bill.value"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

Output:

 "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "terms_agg" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "asqar",
          "doc_count" : 3,
          "bill" : {
            "doc_count" : 9,
            "bill_code" : {
              "doc_count" : 3,
              "average" : {
                "value" : 453.3333333333333
              }
            }
          }
        }
      ]
    }
  }

elasticsearch aggregate on list of objects with condition

2 Answers2