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

**URL:** <https://discuss.elastic.co/t/how-to-get-the-last-nested-object-of-all-documents-then-perform-sub-aggregations/207306>\
**Category:** Elasticsearch\
**Created:** [November 11, 2019, 8:46am UTC](https://discuss.elastic.co/t/how-to-get-the-last-nested-object-of-all-documents-then-perform-sub-aggregations/207306 "2019-11-11T08:46:26Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Noctis17](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/noctis17/32/41656_2.png) [@Noctis17](https://discuss.elastic.co/u/Noctis17)\
**Post date:** [November 11, 2019, 8:46am UTC](https://discuss.elastic.co/t/how-to-get-the-last-nested-object-of-all-documents-then-perform-sub-aggregations/207306/1 "2019-11-11T08:46:26Z")

</div>

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

```auto
"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)

```auto
'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](https://us1.discourse-cdn.com/elastic/original/3X/8/2/824b6f3960a9110571efb3c6feaa91301eeccc8a.png)](https://i.stack.imgur.com/3t3M1.png)

Then I tried adding sub-aggregation  
[![Output 2](https://us1.discourse-cdn.com/elastic/original/3X/1/6/1606f366ff2dac71c60a17471ebef9f1d8dba141.png)](https://i.stack.imgur.com/7tduU.png)

Then this is the output [![Output 3](https://us1.discourse-cdn.com/elastic/original/3X/9/4/94e3b46cbf7414c222b29e120ab23753e4de12c2.png)](https://i.stack.imgur.com/uzP35.png)

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

---

<div class="post-metadata">

**Author:** ![Noctis17](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/noctis17/32/41656_2.png) [@Noctis17](https://discuss.elastic.co/u/Noctis17)\
**Post date:** [November 12, 2019, 5:25am UTC](https://discuss.elastic.co/t/how-to-get-the-last-nested-object-of-all-documents-then-perform-sub-aggregations/207306/2 "2019-11-12T05:25:55Z")

</div>

Someone answered to my inquiry in StackOverflow:

> <https://stackoverflow.com/questions/58797633/elastcisearch-how-to-get-the-last-nested-object-of-all-documents-then-perform>

```
'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](https://us1.discourse-cdn.com/elastic/original/3X/6/c/6cf0b9d77a5ac15371a3252baca7b03fa4ad7354.png)

Any idea about this?

---

<div class="post-metadata">

**Author:** ![Noctis17](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/noctis17/32/41656_2.png) [@Noctis17](https://discuss.elastic.co/u/Noctis17)\
**Post date:** [November 13, 2019, 9:32am UTC](https://discuss.elastic.co/t/how-to-get-the-last-nested-object-of-all-documents-then-perform-sub-aggregations/207306/3 "2019-11-13T09:32:04Z")

</div>

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:

> <https://stackoverflow.com/questions/51709347/sum-over-top-hits-aggregation>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [December 11, 2019, 9:32am UTC](https://discuss.elastic.co/t/how-to-get-the-last-nested-object-of-all-documents-then-perform-sub-aggregations/207306/4 "2019-12-11T09:32:14Z")

</div>

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