Issue on writting elasticsearch queries for nested field

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.

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

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