Max Date Agg on The same Document for Multiple Fields

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?

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

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