Get latest records from index

[Avinash1 (Avinash)]

Hi ,

In mysql i had query like

select * from (select idx,machine,customer,FROM_UNIXTIME(servertime) from Events where customer='MSP_NH_TEST' and machine in ('GERMAN','HFN-ENGINE','HFND100035','HFND100036') order by idx desc) as t group by machine;

Which gives me data of all latest records of user.

I tried in EL

{
"size": 0,
"query": {
"match" : {
"customer" : "BMW__201700051",
}
},
"aggs": {
"machines": {
"terms": {
"field": "machine.keyword",
"order": {
"idx": "desc"
}
},

  }

}
}

But am not getting expected result please someone help me.

[fdartayre (Fred)]

Hi Avinash,

The simplest way I am thinking of is using Field Collapsing:

{
  "query": {
    "match": {
      "customer.keyword": "MSP_NH_TEST"
    }
  },
  "collapse": {
    "field": "machine.keyword"
  },
  "sort": [
    {
      "idx": {
        "order": "desc"
      }
    }
  ]
}

Or using nested aggregations this should work as well:

{
  "size": 0,
  "query": {
    "match": {
      "customer.keyword": "BMW__201700051"
    }
  },
  "aggs": {
    "machines": {
      "terms": {
        "field": "machine.keyword"
      },
      "aggs": {
        "results": { 
          "top_hits": {
            "sort": [
              {
                "idx": {
                  "order": "desc"
                }
              }
            ],
            "size": 1
          }
        }
      }
    }
  }
}

Frédéric