How to aggregate on filtered nested documents

Elastic Stack Elasticsearch
1

Hi,

I am working aggregations. I am so happy that I am able prepare different kinds of analytical reports using aggregations.

But I am stuck while aggregating the filtered nested documents.

I have indexed customer data along with orders. Here "orders" are stored as nested type.

Ex:-
example document:
{"company":"ABC","orders":[{"order_no":"OL1", "prod_type" : "OLP",
"price":20}, {"order_no":"OL2", "prod_type" : "OLP", "price":50},
{"order_no":"OL3", "prod_type" : "GLP", "price":100},{"order_no":"OL3", "prod_type" : "OLP", "price":200} ]}

Here I want to get the total price where orders.prod_type='OLP'
expected output total_price = 270

Can anyone please help me?

Thanks & Regards,
Samanth

2

Try the following example (it uses your example document):

POST test
{
  "settings": {
    "number_of_shards": 1,
    "number_of_replicas": 0
  },
  "mappings": {
    "doc": {
      "properties": {
        "orders": {
          "type": "nested"
        }
      }
    }
  }
}

GET test

POST test/doc/1
{
  "company": "ABC",
  "orders": [
    {
      "order_no": "OL1",
      "prod_type": "OLP",
      "price": 20
    },
    {
      "order_no": "OL2",
      "prod_type": "OLP",
      "price": 50
    },
    {
      "order_no": "OL3",
      "prod_type": "GLP",
      "price": 100
    },
    {
      "order_no": "OL3",
      "prod_type": "OLP",
      "price": 200
    }
  ]
}

GET test/_search
{
  "query": {
    "nested": {
      "path": "orders",
      "score_mode": "avg",
      "query": {
        "bool": {
          "must": [
            {
              "match": {
                "orders.prod_type": "OLP"
              }
            }
          ]
        }
      }
    }
  },
  "aggs": {
    "orders": {
      "nested": {
        "path": "orders"
      },
      "aggs": {
        "OLP_orders": {
          "filter": {
            "match": {
              "orders.prod_type": "OLP"
            }
          },
          "aggs": {
            "total_price": {
              "sum": {
                "field": "orders.price"
              }
            }
          }
        }
      }
    }
  }
}

In the search request it uses the query to filter documents that have at least one order with the prod_type "OLP" and then in the aggregations uses the nested aggregation to get all of the order nested documents (the orders), uses the filter aggregation to filter those orders by those with a prod_type of "OLP" and then uses the sum aggregation to get the total price of all the "OLP" orders

Hope that helps

3

Wow! Thank You so much!... It is working!!!

I am so happy now.

Thanks again!

Regards,
Samanth