Elastic search get one record per hour


(Jothi Kannan) #1

I want to get only one record per hours in a date range. For Example, If I want to get the data's between the days (28-08-2018 - 15-09-2018) there are almost 10000 records, but I want to filter the result to show only one record per hour, So I am using aggregation with date_histogram, and I can see only one record per hour

{ 
    "size" : 0,
      "query": {
        "bool": {
          "must": [
                    {
              "range": {
                "createdtime": {
                  "gte": "1535201500000",
                  "lte": "1536756706000",
                  "boost": 2.0
                }
              }
            },

            {
              "match": {
                "gen": 1
              }
            },
            {
              "match": {
                "Mid": 350404
              }
            }
          ]
        }
      },

    "aggregations" : {
        "runtime" : {
            "date_histogram" : {
                "field" : "createdtime",
                "interval" : "1H",
                "min_doc_count": 1
            },"aggs": {
                "tops": {
                  "top_hits": {
                    "size": 1
                  }
                }
            }
        }
    }      
}

Here the problem is I want to use pagination to show these results as I know there is no way to use size and from on aggregation query as of now, I want to know is there any other way to get only one records in an hour


(James Addison) #2

I think you could do this using field collapsing?

You would probably need to index a date_hour field of keyword type. Format it like yearMONTHdayHOUR (ie. 2018091209).

With this approach (if it works at all!), you wouldn't need aggregations at all. I'd love to know if this works, by the way. I haven't had a chance to play with collapsing yet.


(Jothi Kannan) #3

@jaddison, thanks for the kind reply.

But I think Field collapsing won't work in my case as I am getting the records by hours, in my example just I have been using 1H , but it may vary to 1 to 24H.

Please advice me on any other technic


(system) #4

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