Sum aggregation on nested query

Elastic Stack Elasticsearch
1

Hello,

In my database I have products, each products have differents offers and offers have sales.
A sale is defined by a sale datetime and a nb (number of sales for de date)

I have to get produts and offers who have sales on a period, for each offer get the sum of sale for the period and car sort on this sales sum.

so I've defined in mapping offers.sales as nested object and for the moment i've have this query :

{
  "query": {
    "bool": {
      "must": {
        "nested": {
          "path": "offers.sales",
          "query": {
            "range": {
              "offers.sales.saleDate": {
                "from": "2018-08-01"
              }
            }
          },
          "inner_hits": {}
        }
      }
    }
  },
  "sort": [
    {
      "offers.sales.nb": {
        "order": "desc",
        "mode": "sum",
        "nested_filter": {
          "range": {
            "offers.sales.saleDate": {
              "gte": "2018-08-01"
            }
          }
        }
      }
    }
  ]
}

int inner_hits I have filtered sales, but I don't know how to sum them because aggs are not able in nested query...

someone could give me a way to get my result please ?

2

I've found the solution, i give it if someone need it :

POST /ogo-test/item/_search
{
  "query": {
    "bool": {
      "must": {
        "nested": {
          "path": "offers.sales",
          "query": {
            "range": {
              "offers.sales.saleDate": {
                "gte": 1533081600
              }
            }
          }
        }
      }
    }
  },
  "aggs": {
    "offers_sales": {
      "nested": {
        "path": "offers.sales"
      },
      "aggs": {
        "offers": {
          "filter": {
            "range": {
              "offers.sales.saleDate": {
                "gte": 1533081600
              }
            }
          },
          "aggs": {
            "offers": {
              "terms": {
                "field": "offers.sales.offerId"
              },
              "aggs": {
                "period_sale_number": {
                  "sum": {
                    "field": "offers.sales.nb"
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  "sort": {
    "offers.sales.nb": {
      "mode": "sum",
      "order": "asc",
      "nested": {
        "path": "offers.sales",
        "filter": {
          "range": {
            "offers.sales.saleDate": {
              "gte": 1533081600
            }
          }
        }
      }
    }
  }
}
3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.