Max Date Agg on The same Document for Multiple Fields

Elastic Stack Elasticsearch
1

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?

2

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;
"""
}
}
}
}