CODEWITHSUNDEEP
elasticsearchlucenekibanaquerydsl
James Es
James Es

Reputation: 1

Elasticsearch query to get distinct result with most recent date form one month

I am very new to ElasticSearch. Could any one please help me in finding query. I have following records in my elasticsearch

Name    Work         Time Stamp
--------------------------------------------
Steve,  eating,     2016-11-12 05:36:40
Steve,  sleeping,   2016-11-12 06:14:50
Steve,  going,      2016-11-12 07:21:22
Steve,  driving,    2016-11-12 08:20:10
Steve,  reading,    2016-11-12 09:24:30
James,  eating,     2016-11-12 11:36:40
James,  sleeping,   2016-11-12 05:14:50
James,  going,      2016-11-12 08:21:22
James,  driving,    2016-11-12 10:20:10
James,  reading,    2016-11-12 09:24:30
Crag,   sleeping,   2016-05-12 09:24:30

I need following data

Name    Work        Time Stamp
-------------------------------------------
Steve,  reading,    2016-11-12 09:24:30
James,  eating,     2016-11-12 11:36:40

Upvotes: 0

Views: 941

Answers (1)

Lax
Lax

Reputation: 1167

It's very important to index the data in the right way:

1) Because you want to sort by date, you have to "say" to elastic that specific field is date field, so you need to map the data:

  PUT stack 
{
  "mappings": {
    "stack": {
      "properties": {
        "time": {
               "type": "date"
            }
      }
    }
  }
}

2) Then insert the data:

    POST /_bulk
{"index":{"_index":"stack","_type":"stack"}}
{"name":"Steve","work":"eating","Time":"2016-11-12"}
{"index":{"_index":"stack","_type":"stack"}}
{"name":"Steve","work":"sleeping","Time":"2016-11-13"}
{"index":{"_index":"stack","_type":"stack"}}
{"name":"James","work":"eating","Time":"2016-11-12"}
{"index":{"_index":"stack","_type":"stack"}}
{"name":"James","work":"sleeping","Time":"2016-05-15"}

3)Then search:

GET stack/_search
{
    "size": 0, 
    "aggs": {
        "top-tags": {
            "terms": {
                "field": "name",
                "size": 3
            },
            "aggs": {
                "top_tag_hits": {
                    "top_hits": {
                        "sort": [
                          { "Time":   { "order": "desc" }}
                        ],
                        "size" : 2
                    }
                }
            }
        }
    }
}

Upvotes: 2

Related Questions