# Max Date Agg on The same Document for Multiple Fields

**URL:** <https://discuss.elastic.co/t/max-date-agg-on-the-same-document-for-multiple-fields/197737>\
**Category:** Elasticsearch\
**Created:** [September 2, 2019, 8:30pm UTC](https://discuss.elastic.co/t/max-date-agg-on-the-same-document-for-multiple-fields/197737 "2019-09-02T20:30:34Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Lucas\_Rezende](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lucas_rezende/32/43971_2.png) [@Lucas\_Rezende](https://discuss.elastic.co/u/Lucas_Rezende)\
**Post date:** [September 2, 2019, 8:30pm UTC](https://discuss.elastic.co/t/max-date-agg-on-the-same-document-for-multiple-fields/197737/1 "2019-09-02T20:30:34Z")

</div>

Hi there. I have millions of documents with a block like this one:

```
{
  "useraccountid": 123456,
  "purchases_history" : {
    "last_updated" : "Sat Apr 27 13:41:46 UTC 2019",
    "purchases" : [
      {
        "purchase_id" : 19854284,
        "purchase_date" : "Jan 11, 2017 7:53:35 PM"
      },
      {
        "purchase_id" : 19854285,
        "purchase_date" : "Jan 12, 2017 7:53:35 PM"
      },
      {
        "purchase_id" : 19854286,
        "purchase_date" : "Jan 13, 2017 7:53:35 PM"
      }
    ]
  }
}

```

I am trying to figure out how I can do something like:

`SELECT useraccountid, max(purchases_history.purchases.purchase_date) FROM my_index GROUP BY useraccountid`

I only found the max aggregation but it aggregates over all the documents in the index, but this is not what I need. I need to find the max purchase date for each document. I believe there must be a way to iterate over each path _ **purchases\_history.purchases.purchase\_date** _ of each document to identify which one is the max purchase date, but I really cannot find how to do it (if this is really the best way of course).

Any suggestion?

---

<div class="post-metadata">

**Author:** ![jmorph99](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jmorph99/32/53466_2.png) [@jmorph99](https://discuss.elastic.co/u/jmorph99)\
**Post date:** [September 2, 2019, 11:20pm UTC](https://discuss.elastic.co/t/max-date-agg-on-the-same-document-for-multiple-fields/197737/2 "2019-09-02T23:20:31Z")

</div>

You cannot use aggregates. Use painless scripting. Example on Elastic version 6.8

PUT ex1/  
{  
"mappings": {  
"\_doc": {  
"properties": {  
"purchases\_history": {  
"properties": {  
"last\_updated": {  
"type": "date",  
"format":"EEE MMM dd HH:mm:ss zzz YYYY"  
},  
"purchases": {  
"properties": {  
"purchase\_date": {  
"type": "date",  
"format":"MMM dd, YYYY hh:mm:ss aa"  
},  
"purchase\_id": {  
"type": "long"  
}  
}  
}  
}  
},  
"useraccountid": {  
"type": "long"  
}  
}  
}  
}  
}

PUT ex1/\_doc/1  
{  
"useraccountid": 123456,  
"purchases\_history": {  
"last\_updated": "Sat Apr 27 13:41:46 UTC 2019",  
"purchases": [  
{  
"purchase\_id": 19854284,  
"purchase\_date": "Jan 11, 2017 7:53:35 PM"  
},  
{  
"purchase\_id": 19854285,  
"purchase\_date": "Jan 12, 2017 7:53:35 PM"  
},  
{  
"purchase\_id": 19854286,  
"purchase\_date": "Jan 13, 2017 7:53:35 PM"  
}  
]  
}  
}

GET ex1/\_search  
{  
"docvalue\_fields": ["purchases\_history.purchases.purchase\_date"],  
"script\_fields": {  
"maxdate": {  
"script": {  
"lang": "painless",  
"source": """  
def f = doc['purchases\_history.purchases.purchase\_date'];  
def p = f[0];  
for(int i = 1; i \< f.length; i++ ){  
if (f[i].getMillis() \> p.getMillis() ){  
p = f[i];  
}  
}  
return p;  
"""  
}  
}  
}  
}

---

<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:** [September 30, 2019, 11:20pm UTC](https://discuss.elastic.co/t/max-date-agg-on-the-same-document-for-multiple-fields/197737/3 "2019-09-30T23:20:39Z")

</div>

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