How to get max value with document data


(gpong) #1

Hi Guys,

I have three document
{
"plant": "F130",
"partno": "aa.123.abcds",
"date": "20150903",
"curr": "NTD",
"cost": "1000",
}

{
"plant": "F130",
"partno": "aa.123.abcds",
"date": "20151203",
"curr": "NTD",
"cost": "1200",
}

{
"plant": "F130",
"partno": "aa.123.abcds",
"date": "20151215",
"curr": "NTD",
"cost": "1500",
}

and now I need to get document with max date value. so I use this query to get result

{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "plant": "f130"
          }
        },
        {
          "term": {
            "partno": "aa.123.abcds"
          }
        },
        {
          "term": {
            "curr": "ntd"
          }
        }
      ]
    }
  },
  "aggregations": {
    "by_date": {
      "max": {
        "field": "date"
      }
    }
  }
}

But aggregation the result is

"aggregations": {
"by_date": {
"value": 1450137600000,
"value_as_string": "20151215"
}

what I expected is this document

{
"plant": "F130",
"partno": "aa.123.abcds",
"date": "20151215",
"curr": "NTD",
"cost": "1500",
}

Can anyone help me how to do it?

Thanks


(David Pilato) #2

It looks good but may be your mapping is incorrect?


(Ivan Brusic) #3

I see no need to use aggregations. Simply sort the results on the date
value (descending) and retrieve only one result by setting the size to 1.

{
"query": {
"bool": {
"must": [
{
"term": {
"plant": "f130"
}
},
{
"term": {
"partno": "aa.123.abcds"
}
},
{
"term": {
"curr": "ntd"
}
}
]
}
},
"sort": [
{
"date": {
"order": "desc"
}
}
],
"size": 1
}


(gpong) #4

this works perfectly
Thanks Ivan


(system) #5