Issue on writting elasticsearch queries for nested field

Elastic Stack Elasticsearch
1

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.

2

Can anybody help me ? Or just give me some tricks that could help me for the double aggregation. Thank you very much