Elasticsearch: How to aggregate and sum the data grouped by some fields having array

Question

Based on below 2 messages, I need to aggregate the data based on:

1. granularity (MyHeader.granularity)
2. myType (MyHeader.myType)

3. Currency.

   {
           "_source": {
               "MyData": {
                   "MyHeader": {
                       "granularity": "Level1",
                       "myType": "YTD",
                       "businessDate": "2018-12-27"
                   },
                   "MyBreakDown": [
                       {
                           "category": null,
                           "Currency": "eur",
                           "MyDetails": [
                               {
                                   "Id": 123,
                                   "myLocalAmount": 100
                               }
                           ]
                       },
                       {
                           "category": null,
                           "Currency": "clf",
                           "MyDetails": [
                               {
                                   "Id": 234,
                                   "myLocalAmount": 130
                               }
                           ]
                       },
                       {
                           "category": null,
                           "Currency": "usd",
                           "MyDetails": [
                               {
                                   "Id": 120,
                                   "myLocalAmount": 250
                               }
                           ]
                       }
                   ]
               }
           }
       },          {
   
           "_source": {
               "MyData": {
                   "MyHeader": {
                       "granularity": "Level1",
                       "myType": "MTD",
                       "businessDate": "2018-12-27"
                   },
                   "MyBreakDown": [
                       {
                           "category": null,
                           "Currency": "eur",
                           "MyDetails": [
                               {
                                   "Id": 123,
                                   "myLocalAmount": 110
                               }
                           ]
                       },
                       {
                           "category": null,
                           "Currency": "clf",
                           "MyDetails": [
                               {
                                   "Id": 234,
                                   "myLocalAmount": 120
                               }
                           ]
                       },
                       {
                           "category": null,
                           "Currency": "usd",
                           "MyDetails": [
                               {
                                   "Id": 120,
                                   "myLocalAmount": 253
                               }
                           ]
                       }
                   ]
               }
           }
       }
   

Here I can do basic aggregation:

"aggs":{
   "by_granularity":{
      "terms":{
         "field":"MyHeader.granularity"
      },
      "aggs":{
         "by_myType ":{
            "terms":{
               "field":"MyHeader.myType"
            }
         }
      }
   }
},
"size":0

However, Currency is under the array field and unable to use it.

I need help how to also use currency in aggregations, so it will give me correct data in buckets.

Data is expected something like:

Level1->YTD->eur = 100
Level1->YTD->clf= 130
Level1->YTD->usd = 250

Level1->MTD->eur = 110
Level1->MTD->clf = 120
Level1->MTD->usd = 253

P.S. The index is not managed by us, so I cannot change anything in config. So looking forward to see if it is possible to get such data in ES query?

Answer 1

Try this query, and arrange your data by the output.

{
    "from": 0,
    "size": 0,
    "_source": {
        "includes": [
            "MyData.MyBreakDown.MyDetails.myLocalAmount"
        ],
        "excludes": []
    },
    "stored_fields": "MyData.MyBreakDown.MyDetails.myLocalAmount",
    "aggregations": {
        "MyData.MyHeader.granularity": {
            "terms": {
                "field": "MyData.MyHeader.granularity",
                "size": 200,
                "min_doc_count": 1,
                "shard_min_doc_count": 0,
                "show_term_doc_count_error": false,
                "order": [
                    {
                        "_count": "desc"
                    },
                    {
                        "_key": "asc"
                    }
                ]
            },
            "aggregations": {
                "MyData.MyHeader.myType": {
                    "terms": {
                        "field": "MyData.MyHeader.myType",
                        "size": 10,
                        "min_doc_count": 1,
                        "shard_min_doc_count": 0,
                        "show_term_doc_count_error": false,
                        "order": [
                            {
                                "_count": "desc"
                            },
                            {
                                "_key": "asc"
                            }
                        ]
                    },
                    "aggregations": {
                        "MyData.MyBreakDown.Currency": {
                            "terms": {
                                "field": "MyData.MyBreakDown.Currency",
                                "size": 10,
                                "min_doc_count": 1,
                                "shard_min_doc_count": 0,
                                "show_term_doc_count_error": false,
                                "order": [
                                    {
                                        "_count": "desc"
                                    },
                                    {
                                        "_key": "asc"
                                    }
                                ]
                            },
                            "aggregations": {
                                "MyData.MyBreakDown.MyDetails.myLocalAmount": {
                                    "terms": {
                                        "field": "MyData.MyBreakDown.MyDetails.myLocalAmount",
                                        "size": 10,
                                        "min_doc_count": 1,
                                        "shard_min_doc_count": 0,
                                        "show_term_doc_count_error": false,
                                        "order": [
                                            {
                                                "_count": "desc"
                                            },
                                            {
                                                "_key": "asc"
                                            }
                                        ]
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}