Nested fieds: applying scripts and average calculation

[Paddy_Mahadeva]

Hi,

I have a mapping similar to below for one of my indexes.

{
  "name" : {
     "type: keyword"
  },
  "age" : {
       "type" : "long"  
   }
   "networth" : {
       "type" : "double" 
    },
    "bank_accounts : {
           type: "nested"
           "properties" : {
                "account_no" : {
                     "type" : long
                }
                "monthly_balanace" : {
                      "type" : "nested"
                      "properties" : {
                                "first" : {
                                       type : "double"
                                 },"fifteenth" : {
                                       type : "double"
                                 }, "thirty" : {
                                       type : "double"
                                 }   
                       } 
                 }
           }
      }

with sample data

{
          "name" : "test user"
          "networth" : 100020101,
          "age"  :50 
           bank_Accounts  : [
                     {
                          account_no: 1,
                          monthly_balance : [{
                                 "month: 1
                                 "first" : 100000,
                                 "fifteenth": 2000000,
                                 "thirtieth"  3000000
                            },
                            {
                                 "month: 2
                                 "first" : 100000,
                                 "fifteenth": 2000000,
                                 "thirtieth"  3000000
                            }
                          ] 
                      },
                     {
                          account_no : 2,
                          monthly_balance : [
                                 [{
                                 "month: 1
                                 "first" : 100000,
                                 "fifteenth": 2000000,
                                 "thirtieth"  3000000
                            },
                            {
                                 "month: 2
                                 "first" : 100000,
                                 "fifteenth": 2000000,
                                 "thirtieth"  3000000
                            }
                          ]
                      }   
           ] 
}

Now I need to calculate the monthy average balance for each of the account that the user holds and the average balance of each month for all the bank accounts put together.

Can I do this with elasticsearch? I was trying to achieve the same using scripted fields, but not able to add values of nested objects

In this example I would need my query to return a document that has the value

month1_avg_bal for each of the account, for each of the month.

  "script_fields":{
        "average":{
            "script":{
                "inline":"(doc['bank_accounts .monthly_balance.first'].value+doc['bank_accounts .monthly_balance.fifteenth'].value + doc['bank_accounts .monthly_balance.thirtieth'].value)/3",
                "lang":"painless"
            }
        }    
    }

and

"aggs" : {
       "balance" : {
           "nested " : {
               "path" : "banking_details.bank_account_monthly_details"
           },
           "avg" : { "script" : "doc['bank_accounts .monthly_balance.first'].value+doc['bank_accounts .monthly_balance.fifteenth'].value + doc['bank_accounts .monthly_balance.thirtieth'].value)/3" } }
   }

But nothing seems to be working! Can I achieve this with ES?