How to perform a date range elasticsearch query given multiple dates per document?

Question

I'm using ElasticSearch to index forum threads and reply posts. Each post has a date field associated with it. I'd like to perform a query that includes a date range which will return threads that contain posts matching a date range. I've looked at using a nested mapping but the docs say the feature is experimental and may lead to inaccurate results.

What's the best way to accomplish this? I'm using the Java API.

DrTech · Accepted Answer · 2011-11-12 12:10:10Z

12

You haven't said much about your data structure, but I'm inferring from your question that you have post objects which contain a date field, and presumably a thread_id field, ie some way of identifying which thread a post belongs to?

Do you also have a thread object, or is your thread_id sufficient?

Either way, your stated goal is to return a list of threads which have posts in a particular date range. This means that you need to group your threads (rather than returning the same thread_id multiple times for each post in the date range).

This grouping can be done by using facets.

So the query in JSON would look like this:

curl -XGET 'http://127.0.0.1:9200/posts/post/_search?pretty=1&search_type=count'  -d '
{
   "facets" : {
      "thread_id" : {
         "terms" : {
            "size" : 20,
            "field" : "thread_id"
         }
      }
   },
   "query" : {
      "filtered" : {
         "query" : {
            "text" : {
               "content" : "any keywords to match"
            }
         },
         "filter" : {
            "numeric_range" : {
               "date" : {
                  "lt" : "2011-02-01",
                  "gte" : "2011-01-01"
               }
            }
         }
      }
   }
}
'

Note:

I'm using search_type=count because I don't actually want the posts returned, just the thread_ids
I've specified that I want the 20 most frequently encountered thread_ids (size: 20). The default would be 10
I'm using a numeric_range for the date field because dates typically have many distinct values, and the numeric_range filter uses a different approach to the range filter, making it perform better in this situation
If your thread_ids look like how-to-perform-a-date-range-elasticsearch-query then you can use these values directly. But if you have a separate thread object, then you can use the multi-get API to retrieve these
your thread_id field should be mapped as { "index": "not_analyzed" } so that the whole value is treated as a single term, rather than being analyzed into separate terms

answered Nov 12, 2011 at 12:10

DrTech

17.4k5 gold badges56 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

digitalsanctum Over a year ago

First, thanks for the lengthy and informative answer. My final solution was a little different. I basically created an array of "postDates" of type Date for each thread document. From there, I do a range query. I may look into changing to a numeric_range filter for the performance as you suggested. Thanks again!

Thomas Decaux Over a year ago

You shoud use histogram facet if you want data versus time

DrTech Over a year ago

@ThomasDecaux I think you should reread the question. And no, my date format is not wrong: joda-time.sourceforge.net/api-release/org/joda/time/format/…

John Carter Over a year ago

Just a note, I think this answer is out of date now - "Facets are deprecated and will be removed in a future release. You are encouraged to migrate to aggregations instead."

Collectives™ on Stack Overflow

How to perform a date range elasticsearch query given multiple dates per document?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related