How to get the last nested object of all documents then perform sub-aggregations

Elastic Stack Elasticsearch
1

I have an index called socialmedia and trying to create queries with this field called eng (omitted some unnecessary fields)

"id" : "1",
"eng": 
[
{
  "soc_mm_score" : "3",
  "date_updated" : "1520969306",
},
{
  "soc_mm_score" : "1",
  "date_updated" : "1520972191",
},
{
  "soc_mm_score" : "4",
  "date_updated" : "1520937222",
}
]

I have a lot of documents from this index that contains eng nested field that also contains a lot of "sub-objects"

Now, my main goal is, what Elasticsearch query should I formulate to filter out these nested objects

STEP 1
Get the nested object with the highest date_updated value

STEP 2
After getting those nested objects, perform a sum aggregation so I could add all the values of the soc_mm_score field for the corresponding "latest nested object"

I have tried this query but seems to fail

ATTEMPT # 1 (I'm using elasticsearch-php API so please trust my query that it's working with this format)

'aggs' => [
    'ENG' => [
        'nested' => [
            'path' => 'eng'
        ],
        'aggs' => [
            'FILTER' => [
                'filter' => [
                    'bool' => [
                        'must' => [
                            [
                                // I'm thinking of using max aggregation here
                            ]
                        ]
                    ]
                ]
            ]
            'LATEST' => [
                'top_hits' => [
                    'size' => 1,
                    'sort' => [
                        'eng.date_updated' => [
                            'order' => 'desc'
                        ]
                    ]
                ]
            ]
        ]
    ]
]

PRO/S: it is returning the correct nested object
CON/S: I cannot perform further aggregations

Sample Output
Output 1

Then I tried adding sub-aggregation
Output 2

Then this is the output Output 3

Is there any other ways that I can perform the desired query?

2

Someone answered to my inquiry in StackOverflow:

'aggs' => [
    'LATEST' => [
        'scripted_metric' => [
            "init_script" => 
                "params._agg.map = new HashMap(); 
                params._agg.results = new HashMap(); 
                params._agg.size = 1; 
                params._agg.date_arr = null",
            "map_script" => 
                "params._agg.map[doc['eng.date_updated.keyword'].value] = doc['eng.soc_like_count'].value;
                params._agg.date_arr = new ArrayList(params._agg.map.keySet());
                Collections.sort(params._agg.date_arr, Collections.reverseOrder())",
            "combine_script" => 
                "params._agg.size = params._agg.size > params._agg.date_arr.length - 1 ?  params._agg.date_arr.length : params._agg.size; 
                double soc= 0; 
                for (t in params._agg.date_arr.subList(0,params._agg.size)) { 
                    params._agg.results[t] = params._agg.map[t];
                    soc += params._agg.map[t]} params._agg.results.total = soc;
                }
                return params._agg.results"
        ]
    ]
]

But the problem is, it's asking for the reduce_script:

image
image.png1363×603 23.4 KB

Any idea about this?

3

Update, made a progress with this answer from StackOverflow, however, I failed to produce my desired output, and it's displaying an error coz my field is of nested nature:

Sir @val may you kindly help me out with this? you were the one who answered to my provided link's question