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

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?

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:

Any idea about this?

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

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