ElasticSearch - Nested Filtered Aggregations

Question

I'm looking for a way to get the min and max value from a 2 level nested object which have been filtered. So below im trying to get the min and max price which has a currencyCode GBP. Each product can have multiple skus and each sku can have multiple prices (though only 1 will be GBP):

"hits": [
      {
        "_index": "product",
        "_type": "main",
        "_id": "1",
        "_score": 1,
        "_source": {         
          "skus": [
            {
              "prices": [
                {
                  "currencyCode": "GBP",
                  "price": 15
                }
              ]
            }
          ]
        }
      },{
        "_index": "product",
        "_type": "main",
        "_id": "2",
        "_score": 1,
        "_source": {         
          "skus": [
            {
              "prices": [
                {
                  "currencyCode": "GBP",
                  "price": 20
                }
              ]
            }
          ]
        }
      },
    {
        "_index": "product",
        "_type": "main",
        "_id": "3",
        "_score": 1,
        "_source": {         
          "skus": [
            {
              "prices": [
                {
                  "currencyCode": "GBP",
                  "price": 25
                }
              ]
            }
          ]
        }
      }]
  }

So I want min 15, max 25. I've looked into Filter Aggregation and Nested Aggregation but can not come up with the answer.

I'm using ElasticSearch version 5.5.

I'm trying to get the query working correctly first before converting to Nest .net.

Any help would be much appreciated.

Answer 1

You can nest "nested" and "filter" aggregations, like this:

{
  "size": 0,
  "aggs": {
    "skus": {
      "nested": {
        "path": "skus"
      },
      "aggs": {
        "prices": {
          "nested": {
            "path": "skus.prices"
          },
          "aggs": {
            "gbp_filter": {
              "filter": {
                "term": {
                  "skus.prices.currencyCode": "GBP"
                }
              },
              "aggs": {
                "min_price": {
                  "min": {
                    "field": "skus.prices.price"
                  }
                },
                "max_price": {
                  "max": {
                    "field": "skus.prices.price"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

However, since you say only one price can be GBP, is it also true that for every SKU, there can only be one price per currency? If that's the case, I would suggest not to use nested data type for prices here. Instead you can use a mapping something like this:

{
  "product": {
    "properties": {
      "skus": {
        "type": "nested",
        "properties": {
          "prices": {
            "properties": {
              "GBP": {"properties": {"price": {"type": "integer"}}},
              "USD": {"properties": {"price": {"type": "integer"}}},
              ...remaining currencies...
            }
          }
        }
      }
    }
  }
}

The mapping is not very concise, but it will be more efficient to query, and the queries are nicer. (Almost) anytime you can denormalize your data to get rid of nesting, even if you have to duplicate information (in order to meet the needs of different types of queries), it's a good idea.