Hi everyone,

I have an issue writing elasticsearch queries with nested field. I think you could help me because I'm the only one using Elastic in my company and I don't know how to solve my problem alone.

Here are how my ES docs look like in this index (very simplified version)

> {

`"_index":"index_test", "_type":"type_test", "_id":"1", "_score":1.0, "_source":{ "category":1, "value_period":[ { "value":5.4, "month":"2019-01" } ], "client_id":"80", } }, { "_index":"index_test", "_type":"type_test", "_id":"2", "_score":1.0, "_source":{ "category":1, "value_period":[ { "value":10.0, "month":"2019-02" } ], "client_id":"80", } }{ "_index":"index_test", "_type":"type_test", "_id":"2", "_score":1.0, "_source":{ "category":2, "value_period":[ { "value":20.0, "month":"2019-02" } ], "client_id":"80", } }`

Here is the question:

I need to write 4 queries for four different use case:

2 simple queries : "sum per month" and "sum per category"

- 1st: date_histogram aggregation (on nested field "value_period.month") and sum aggregation (on value_period.value) in order to get a simple sum by month (1 value per month).
- 2nd: term aggregation (on field "category") and sum aggregation (on value_period.value) in order to get a simple value per category

2 queries a little more complex: "sum per month per category" and "sum per category per month"

- 3rd: date_histogram aggregation (on nested field "value_period.month"), term aggregation (on field "category") and sum aggregation (on value_period.value) in order to get sum per month per category
- 4th: term aggregation (on field "category"), date_histogram aggregation (on nested field "value_period.month") and sum aggregation (on value_period.value) in order to get sum per category per month

For the 1st case (simple value per month) I have already written the query and it works:

`{ "aggs":{ "nested_categories":{ "nested":{ "path":"value_period" }, "aggs":{ "date_agg_interval":{ "date_histogram":{ "field":"value_period.month", "interval":"1M", "format":"yyyy-MM", "min_doc_count":0 }, "aggs":{ "agg_sum":{ "sum":{ "field":"value_period.value" } } } } } } } }`

I really want to thanks in advance for any people that will help me out and consider my request.

PS: excuse my level of english I'm french.