# Filter latest record for each user and aggregate on different field in elasticsearch

**URL:** <https://discuss.elastic.co/t/filter-latest-record-for-each-user-and-aggregate-on-different-field-in-elasticsearch/250435>\
**Category:** Elasticsearch\
**Created:** [September 30, 2020, 1:56am UTC](https://discuss.elastic.co/t/filter-latest-record-for-each-user-and-aggregate-on-different-field-in-elasticsearch/250435 "2020-09-30T01:56:17Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![matrix1](https://avatars.discourse-cdn.com/v4/letter/m/2acd7d/32.png) [@matrix1](https://discuss.elastic.co/u/matrix1)\
**Post date:** [September 30, 2020, 1:56am UTC](https://discuss.elastic.co/t/filter-latest-record-for-each-user-and-aggregate-on-different-field-in-elasticsearch/250435/1 "2020-09-30T01:56:17Z")

</div>

Need to filter the latest record for each user and aggregate the count on each vote type. Still cannot able to figure out. any help pls?

{user:"sam", vote: 20, "createdDate": 1601285178238}

{user:"sam", vote: 21, "createdDate": 1601285178239}

{user:"harry", vote: 30, "createdDate": 1601285178238}

Any help

---

<div class="post-metadata">

**Author:** ![shrikantgulia](https://avatars.discourse-cdn.com/v4/letter/s/c68b51/32.png) [@shrikantgulia](https://discuss.elastic.co/u/shrikantgulia)\
**Post date:** [September 30, 2020, 8:27am UTC](https://discuss.elastic.co/t/filter-latest-record-for-each-user-and-aggregate-on-different-field-in-elasticsearch/250435/2 "2020-09-30T08:27:32Z")

</div>

Hello @matrix1,

first change the createdDate in the human readable format from epoch and then apply the aggregation.

Hope it works for you

---

<div class="post-metadata">

**Author:** ![matrix1](https://avatars.discourse-cdn.com/v4/letter/m/2acd7d/32.png) [@matrix1](https://discuss.elastic.co/u/matrix1)\
**Post date:** [September 30, 2020, 9:27am UTC](https://discuss.elastic.co/t/filter-latest-record-for-each-user-and-aggregate-on-different-field-in-elasticsearch/250435/3 "2020-09-30T09:27:45Z")

</div>

> [@matrix1](#):
>

@shrikantgulia pls find my index details after the changes. i need to count only the votes of the latest user response

POST vote\_result/\_doc  
{  
"user": "sam",  
"vote": "20",  
"createdDate": "2020-09-30T09:05:30.113Z"  
}

POST vote\_result/\_doc  
{  
"user": "sam",  
"vote": "21",  
"createdDate": "2020-09-30T09:05:32.113Z"  
}

POST vote\_result/\_doc  
{  
"user": "harry",  
"vote": "30",  
"createdDate": "2020-09-30T09:05:30.113Z"  
}

GET vote\_result/\_search  
{  
"aggs":{  
"by\_user":{  
"terms": {  
"field": "user"  
},  
"aggs": {  
"tops": {  
"top\_hits": {  
"size": 1  
}  
}  
}  
}  
}  
}

Response:

"aggregations" : {  
"by\_user" : {  
"doc\_count\_error\_upper\_bound" : 0,  
"sum\_other\_doc\_count" : 0,  
"buckets" : [  
{  
"key" : "sam",  
"doc\_count" : 2,  
"tops" : {  
"hits" : {  
"total" : {  
"value" : 2,  
"relation" : "eq"  
},  
"max\_score" : 1.0,  
"hits" : [  
{  
"\_index" : "vote\_result",  
"\_type" : "\_doc",  
"\_id" : "UdFS3nQBmPKI5bzQIM1V",  
"\_score" : 1.0,  
"\_source" : {  
"user" : "sam",  
"vote" : "21",  
"createdDate" : "2020-09-30T09:05:32.113Z"  
}  
}  
]  
}  
}  
},  
{  
"key" : "harry",  
"doc\_count" : 1,  
"tops" : {  
"hits" : {  
"total" : {  
"value" : 1,  
"relation" : "eq"  
},  
"max\_score" : 1.0,  
"hits" : [  
{  
"\_index" : "vote\_result",  
"\_type" : "\_doc",  
"\_id" : "UtFS3nQBmPKI5bzQKM0B",  
"\_score" : 1.0,  
"\_source" : {  
"user" : "harry",  
"vote" : "30",  
"createdDate" : "2020-09-30T09:05:30.113Z"  
}  
}  
]  
}  
}  
}  
]  
}  
}  
}

After this i need to aggregate the count of each unique vote in this case i need

{  
key : 21  
count : 1  
} ,  
{  
key : 30  
count: 1  
}

how to achieve this result

---

<div class="post-metadata">

**Author:** ![shrikantgulia](https://avatars.discourse-cdn.com/v4/letter/s/c68b51/32.png) [@shrikantgulia](https://discuss.elastic.co/u/shrikantgulia)\
**Post date:** [September 30, 2020, 10:01am UTC](https://discuss.elastic.co/t/filter-latest-record-for-each-user-and-aggregate-on-different-field-in-elasticsearch/250435/4 "2020-09-30T10:01:50Z")

</div>

Hello,

to get the first result put the size parameter.  
size =1;

1. You marked the votes as string Please change them to number

POST test\_1/\_doc  
{  
"user": "sam",  
"vote": 21,  
"createdDate": "2020-09-30T09:05:32.113Z"  
}

POST test\_1/\_doc  
{  
"user": "harry",  
"vote": 30,  
"createdDate": "2020-09-30T09:05:30.113Z"  
}

POST test\_1/\_search  
{  
"size": 0,  
"aggs": {  
"max\_votes": {  
"max": {  
"field": "vote"  
}  
}  
}  
}

---

<div class="post-metadata">

**Author:** ![matrix1](https://avatars.discourse-cdn.com/v4/letter/m/2acd7d/32.png) [@matrix1](https://discuss.elastic.co/u/matrix1)\
**Post date:** [September 30, 2020, 10:18am UTC](https://discuss.elastic.co/t/filter-latest-record-for-each-user-and-aggregate-on-different-field-in-elasticsearch/250435/5 "2020-09-30T10:18:54Z")

</div>

@shrikantgulia

i can get the max vote from size 1. the problem is need to filter only the latest record for each user then aggregate on count to get each vote type

eg:

i have 3 document . just a sample pls ignore the timestamp format

{user:"sam", vote: 20, "createdDate": 1601285178238}

{user:"sam", vote: 21, "createdDate": 1601285178239}

{user:"harry", vote: 30, "createdDate": 1601285178238}

filter only the latest of document for each user which will give me

{user:"sam", vote: 21, "createdDate": 1601285178239}

{user:"harry", vote: 30, "createdDate": 1601285178238}

And aggregate the field vote to get count of each vote type which will provide me

{  
key : 21  
count : 1  
} ,  
{  
key : 30  
count: 1  
}

how to achieve this result?

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [September 30, 2020, 11:05am UTC](https://discuss.elastic.co/t/filter-latest-record-for-each-user-and-aggregate-on-different-field-in-elasticsearch/250435/6 "2020-09-30T11:05:06Z")

</div>

Any reasoning based on last-known state of entities is tricky in a distributed system where related records are scattered across nodes. For this sort of analysis you typically need to bring related data physically closer together in a special index.  
This can be done in your client app by updating a dedicated entity-state document keyed on the entity ID or using the transform API to periodically fuse multiple related records into a single entity-centric document. This fusion process is simple for some operations (e.g. counting the number of urls a user has accessed using a cardinality agg) but requires more complex scripting for some things like [recording data only from the last known state.](https://www.elastic.co/guide/en/elasticsearch/reference/current/transform-painless-examples.html#painless-top-hits)

---

<div class="post-metadata">

**Author:** ![matrix1](https://avatars.discourse-cdn.com/v4/letter/m/2acd7d/32.png) [@matrix1](https://discuss.elastic.co/u/matrix1)\
**Post date:** [September 30, 2020, 1:20pm UTC](https://discuss.elastic.co/t/filter-latest-record-for-each-user-and-aggregate-on-different-field-in-elasticsearch/250435/7 "2020-09-30T13:20:13Z")

</div>

@Mark_Harwood  
GET opinion\_test/\_search  
{  
"aggs": {  
"instances": {  
"terms": {  
"field": "user",  
"size": 1000,  
"order": {  
"\_key": "desc"  
}  
},  
"aggs": {  
"latest\_mem": {  
"top\_hits": {  
"sort": [  
{  
"createdDate": {  
"order": "desc"  
}  
}  
]  
}  
}  
}  
},  
"latest\_doc": {  
"scripted\_metric": {  
"init\_script": "state.timestamp\_latest = 0L; state.last\_doc = ''",  
"map\_script": "def current\_date = doc['@timestamp'].getValue().toInstant().toEpochMilli(); if (current\_date \> state.timestamp\_latest) {state.timestamp\_latest = current\_date;state.last\_doc = new HashMap(params['\_source']);}",  
"combine\_script": "return state",  
"reduce\_script": "def last\_doc = '';def timestamp\_latest = 0L; for (s in states) {if (s.timestamp\_latest \> (timestamp\_latest)) {timestamp\_latest = s.timestamp\_latest; last\_doc = s.last\_doc;}} return last\_doc"  
}  
}  
}  
}

tried this getting some error .can u help me with a es query for this scenerio

---

<div class="post-metadata">

**Author:** ![matrix1](https://avatars.discourse-cdn.com/v4/letter/m/2acd7d/32.png) [@matrix1](https://discuss.elastic.co/u/matrix1)\
**Post date:** [September 30, 2020, 1:24pm UTC](https://discuss.elastic.co/t/filter-latest-record-for-each-user-and-aggregate-on-different-field-in-elasticsearch/250435/8 "2020-09-30T13:24:58Z")

</div>

@Mark_Harwood

fixed the query issue but is not returning each user latest values it is giving only one user value

GET test/\_search  
{  
"aggs": {  
"instances": {  
"terms": {  
"field": "user",  
"size": 1000,  
"order": {  
"\_key": "desc"  
}  
},  
"aggs": {  
"latest\_mem": {  
"top\_hits": {  
"sort": [  
{  
"createdDate": {  
"order": "desc"  
}  
}  
]  
}  
}  
}  
},  
"latest\_doc": {  
"scripted\_metric": {  
"init\_script": "state.timestamp\_latest = 0L; state.last\_doc = ''",  
"map\_script": "def current\_date = doc['createdDate'].getValue().toInstant().toEpochMilli(); if (current\_date \> state.timestamp\_latest) {state.timestamp\_latest = current\_date;state.last\_doc = new HashMap(params['\_source']);}",  
"combine\_script": "return state",  
"reduce\_script": "def last\_doc = '';def timestamp\_latest = 0L; for (s in states) {if (s.timestamp\_latest \> (timestamp\_latest)) {timestamp\_latest = s.timestamp\_latest; last\_doc = s.last\_doc;}} return last\_doc"  
}  
}  
}  
}

Please let me know if any issues in query

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [September 30, 2020, 1:27pm UTC](https://discuss.elastic.co/t/filter-latest-record-for-each-user-and-aggregate-on-different-field-in-elasticsearch/250435/9 "2020-09-30T13:27:32Z")

</div>

The code I linked to is used to fuse data in a _transform job_ not a query.  
You should read up on the [transform API](https://www.elastic.co/guide/en/elasticsearch/reference/current/transform-overview.html).

Fundamentally you need to fuse data into a new index which only holds the latest information for each user. You can then query that index to determine the breakdown of vote types.

---

<div class="post-metadata">

**Author:** ![matrix1](https://avatars.discourse-cdn.com/v4/letter/m/2acd7d/32.png) [@matrix1](https://discuss.elastic.co/u/matrix1)\
**Post date:** [October 1, 2020, 1:38pm UTC](https://discuss.elastic.co/t/filter-latest-record-for-each-user-and-aggregate-on-different-field-in-elasticsearch/250435/10 "2020-10-01T13:38:06Z")

</div>

@Mark_Harwood thank you understood. is it any way possible to do in a query without Transform API ?

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [October 1, 2020, 2:57pm UTC](https://discuss.elastic.co/t/filter-latest-record-for-each-user-and-aggregate-on-different-field-in-elasticsearch/250435/11 "2020-10-01T14:57:59Z")

</div>

How many unique user IDs do you have?

---

<div class="post-metadata">

**Author:** ![matrix1](https://avatars.discourse-cdn.com/v4/letter/m/2acd7d/32.png) [@matrix1](https://discuss.elastic.co/u/matrix1)\
**Post date:** [October 1, 2020, 5:07pm UTC](https://discuss.elastic.co/t/filter-latest-record-for-each-user-and-aggregate-on-different-field-in-elasticsearch/250435/12 "2020-10-01T17:07:21Z")

</div>

@Mark_Harwood this for user poll aggregation so we have more user so no fixed count

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [October 1, 2020, 5:09pm UTC](https://discuss.elastic.co/t/filter-latest-record-for-each-user-and-aggregate-on-different-field-in-elasticsearch/250435/13 "2020-10-01T17:09:04Z")

</div>

Rough guess on largest numbers?

---

<div class="post-metadata">

**Author:** ![matrix1](https://avatars.discourse-cdn.com/v4/letter/m/2acd7d/32.png) [@matrix1](https://discuss.elastic.co/u/matrix1)\
**Post date:** [October 1, 2020, 5:21pm UTC](https://discuss.elastic.co/t/filter-latest-record-for-each-user-and-aggregate-on-different-field-in-elasticsearch/250435/14 "2020-10-01T17:21:02Z")

</div>

@Mark_Harwood maybe 10000 - 12000

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [October 2, 2020, 8:41am UTC](https://discuss.elastic.co/t/filter-latest-record-for-each-user-and-aggregate-on-different-field-in-elasticsearch/250435/15 "2020-10-02T08:41:11Z")

</div>

The good news is that's sufficiently small that you may be able to get away with doing it in one search request without needing the transform API. When numbers are in the millions it requires too much memory.  
The bad news is it's essentially a programming activity on your part. The `scripted_metric` gives you the framework to have custom scripts collect search result data from data nodes and fuse these at the coordinating node for a final result. It will involve the use of temporary maps etc. to organise results into buckets.

---

<div class="post-metadata">

**Author:** ![matrix1](https://avatars.discourse-cdn.com/v4/letter/m/2acd7d/32.png) [@matrix1](https://discuss.elastic.co/u/matrix1)\
**Post date:** [October 2, 2020, 10:05am UTC](https://discuss.elastic.co/t/filter-latest-record-for-each-user-and-aggregate-on-different-field-in-elasticsearch/250435/16 "2020-10-02T10:05:18Z")

</div>

@Mark_Harwood thank you for the solution. I am new to scripted metrics and checking on the documentation now. Will be helpful if u can provide a sample script for this user based lastest aggregation using scripted metrics to get each vote count

---

<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:** [October 30, 2020, 10:05am UTC](https://discuss.elastic.co/t/filter-latest-record-for-each-user-and-aggregate-on-different-field-in-elasticsearch/250435/17 "2020-10-30T10:05:23Z")

</div>

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