# Aggregation on last element

**URL:** <https://discuss.elastic.co/t/aggregation-on-last-element/20432>\
**Category:** Elasticsearch\
**Created:** [October 25, 2014, 7:34pm UTC](https://discuss.elastic.co/t/aggregation-on-last-element/20432 "2014-10-25T19:34:18Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Michael\_Gallego\_2](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/michael_gallego_2/32/1160_2.png) [@Michael\_Gallego\_2](https://discuss.elastic.co/u/Michael_Gallego_2)\
**Post date:** [October 25, 2014, 7:34pm UTC](https://discuss.elastic.co/t/aggregation-on-last-element/20432/1 "2014-10-25T19:34:18Z")

</div>

Hi,

I have a type whose data looks like this:

{  
"date": "2014-01-01"  
"element": "abc",  
"type": "A"  
},  
{  
"date": "2014-01-02"  
"element": "abc",  
"type": "B"  
},  
{  
"date": "2014-01-03"  
"element": "def",  
"type": "A"  
}

I'd like to be able to group the data by element, and count the documents  
where the LAST document by date have a type of A. In this case, I want the  
result to be "1" (because the second document, that has the same element as  
the first document, has a date that is after the first document, but as its  
type is not B, I don't want it to be counted ; for the last document, it is  
the only one with element "def" and the type is A).

I'm not sure this is even possible. Please note that the cardinality of  
"element" can be quite high (up to 20 000 different values).

Thank you in advance!

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/46509869-4afa-4062-8c34-ad828dcf680c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/46509869-4afa-4062-8c34-ad828dcf680c%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![vineeth\_mohan\_2](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vineeth_mohan_2/32/747_2.png) [@vineeth\_mohan\_2](https://discuss.elastic.co/u/vineeth_mohan_2)\
**Post date:** [October 26, 2014, 1:52pm UTC](https://discuss.elastic.co/t/aggregation-on-last-element/20432/2 "2014-10-26T13:52:48Z")

</div>

Hello Michaël ,

I cant think of a way to do this in a single call.  
May be you should try the following

(Terms aggregation on element) -\> (Top N hits aggregation , sort by date by  
asc and size = 1 ) -\> (Filter aggregation by type A)  
With this you will get the elements that you are looking for. Now do a  
filter on those elements and a terms aggregation query on element filed to  
get the results.

Thanks  
Vineeth

On Sun, Oct 26, 2014 at 1:04 AM, Michaël Gallego [michael@maestrooo.com](mailto:michael@maestrooo.com)  
wrote:

> Hi,
> 
> I have a type whose data looks like this:
> 
> {  
> "date": "2014-01-01"  
> "element": "abc",  
> "type": "A"  
> },  
> {  
> "date": "2014-01-02"  
> "element": "abc",  
> "type": "B"  
> },  
> {  
> "date": "2014-01-03"  
> "element": "def",  
> "type": "A"  
> }
> 
> I'd like to be able to group the data by element, and count the documents  
> where the LAST document by date have a type of A. In this case, I want the  
> result to be "1" (because the second document, that has the same element as  
> the first document, has a date that is after the first document, but as its  
> type is not B, I don't want it to be counted ; for the last document, it is  
> the only one with element "def" and the type is A).
> 
> I'm not sure this is even possible. Please note that the cardinality of  
> "element" can be quite high (up to 20 000 different values).
> 
> Thank you in advance!
> 
> --  
> You received this message because you are subscribed to the Google Groups  
> "elasticsearch" group.  
> To unsubscribe from this group and stop receiving emails from it, send an  
> email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
> To view this discussion on the web visit  
> [https://groups.google.com/d/msgid/elasticsearch/46509869-4afa-4062-8c34-ad828dcf680c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/46509869-4afa-4062-8c34-ad828dcf680c%40googlegroups.com)  
> [https://groups.google.com/d/msgid/elasticsearch/46509869-4afa-4062-8c34-ad828dcf680c%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/46509869-4afa-4062-8c34-ad828dcf680c%40googlegroups.com?utm_medium=email&utm_source=footer)  
> .  
> For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAGdPd5mGGdrY-K3maf4H0QeGuDjS-GUTCbV3MSxdE62wdMYpyA%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAGdPd5mGGdrY-K3maf4H0QeGuDjS-GUTCbV3MSxdE62wdMYpyA%40mail.gmail.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![Michael\_Gallego\_2](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/michael_gallego_2/32/1160_2.png) [@Michael\_Gallego\_2](https://discuss.elastic.co/u/Michael_Gallego_2)\
**Post date:** [October 26, 2014, 3:10pm UTC](https://discuss.elastic.co/t/aggregation-on-last-element/20432/3 "2014-10-26T15:10:15Z")

</div>

Hi Vineeth,

I'm afraid that this won't work, because as I said "element" can have high  
cardinality (while it's not bounded in theory, in practice it will range  
from 500 to 40000). Therefore if I do a "terms" on element, then a top hit,  
it will require to generate maybe 40000 sub-buckets. I think this will kill  
performance.

For now, I've rethought my format so it now looks like this:

{  
"element": "abc",  
"history": [  
{"type": "A", "date": "2014-01-01"},  
{"type": "B", "date": "2014-01-02"}  
]  
}

Where history is mapped as nested. Now, I can do that:

{  
"aggs": {  
"history": {  
"nested": {  
"path": "history"  
},  
"aggs": {  
"latest-history": {  
"filter": {  
"limit": {  
"value": 1  
}  
},

```
      "aggs": {
        "by-type": {
          "terms": {
            "field": "history.type",
            "size": 0
          }
        }
      }
    }
  }
}

```

}  
}

This will get the nested history, limit by 1, then group by type, so I can  
get the count of the ones I'm interested (A type or B type). The only  
drawback is that inside the history nested, I need to sort the history by  
date in my application (I have not found any way to sort the nested by date  
before doing the limit filter...), and that while history is typically  
quite low (around 10-200 elements), it is not bounded, and updating is  
harder to do...

If anyone has any other idea, don't hesitate to share!

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/3948211d-2029-42f4-a07a-3ff0ba1834c7%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/3948211d-2029-42f4-a07a-3ff0ba1834c7%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![Michael\_Gallego\_2](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/michael_gallego_2/32/1160_2.png) [@Michael\_Gallego\_2](https://discuss.elastic.co/u/Michael_Gallego_2)\
**Post date:** [October 26, 2014, 7:22pm UTC](https://discuss.elastic.co/t/aggregation-on-last-element/20432/4 "2014-10-26T19:22:20Z")

</div>

After some testing, it appears that my solution does not work, but I'm not  
sure to understand why. The filter returns less result that what is  
expected.

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/933d228e-82f1-47c4-9fc3-909de234b93b%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/933d228e-82f1-47c4-9fc3-909de234b93b%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<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:** [July 6, 2017, 12:53am UTC](https://discuss.elastic.co/t/aggregation-on-last-element/20432/5 "2017-07-06T00:53:49Z")

</div>