Query in elasticsearch with multiple ranges on multiple dates

elasticsearch

Solution

Under your `bool` queries' `must` clause there's no need to wrap it in an `and`. There is no `and` query, maybe you were thinking of the and filter?

Example runnable play as curl commands for convenience:

#!/bin/bash

export ELASTICSEARCH_ENDPOINT="http://localhost:9200"

# Create indexes

curl -XPUT "$ELASTICSEARCH_ENDPOINT/play" -d '{
    "settings": {},
    "mappings": {
        "type": {
            "properties": {
                "created_at": {
                    "type": "date",
                    "format": "dateOptionalTime"
                },
                "name": {
                    "type": "string"
                },
                "happens_on": {
                    "type": "date",
                    "format": "dateOptionalTime"
                }
            }
        }
    }
}'


# Index documents
curl -XPOST "$ELASTICSEARCH_ENDPOINT/_bulk?refresh=true" -d '
{"index":{"_index":"play","_type":"type"}}
{"name":"foo","created_at":"2013-12-09T00:00:00.000Z","happens_on":"2013-12-16T00:00:00.000Z"}
{"index":{"_index":"play","_type":"type"}}
{"name":"bar","created_at":"2013-12-08T00:00:00.000Z","happens_on":"2013-12-16T00:00:00.000Z"}
{"index":{"_index":"play","_type":"type"}}
{"name":"bar","created_at":"2013-12-09T00:00:00.000Z","happens_on":"2013-12-17T00:00:00.000Z"}
'

# Do searches

curl -XPOST "$ELASTICSEARCH_ENDPOINT/_search?pretty" -d '
{
    "query": {
        "bool": {
            "must": [
                {
                    "range": {
                        "created_at": {
                            "gte": "2013-12-09T00:00:00.000Z"
                        }
                    }
                },
                {
                    "range": {
                        "happens_on": {
                            "lte": "2013-12-16T00:00:00.000Z"
                        }
                    }
                }
            ]
        }
    }
}
'

Problem

The following range_query returns a result as expected: ``` {"query": { "bool": { "must": [ { "range": { "created_at": { "gte": "2013-12-09" } } } ] } } } ``` But and-ing together several range queries, returns nothing: ``` {"query": { "bool":{ "must": [ { "and": [ { "range": { "created_at": { "gte": "2013-12-09" } } }, { "range": { "happens_on": { "lte": "2013-12-16" } } }, { "range": { "created_at": { "lte": "2013-12-14" } } } ] } ] } } } ``` What is the correct way to use multiple range_queries on multiple fields? EDIT: Ah, ok, so this is where I use a range_filter as opposed to range_query? This sounded promising, so I re-wrote my query using only a single range filter. Posting all of it here, in case I'm messing up the query someplace else. I'm performing a GET, and everything inside the source key is actually JSON, but I removed the escaped the hyphens for readability: ``` { "source": { "filtered": { "filter": { "and": [ { "term": { "restricted": false } }, { "not": { "term": { "deleted": true } } }, { "range": { "happens_on": { "lte": "2013-12-16" } } } ] }, "query": { "bool": { "must": [ ] } } }, "from": 0, "size": 10 } } ``` Regrettably, my problem is still the same: I'm not getting any hits. EDIT2: So, going down the alley of ranges inside a must clause like Njal suggested. This gives me a multi-range query like this: ``` { "source": { "filter": { "and": [ { "term": { "restricted": false } }, { "not": { "term": { "deleted": true } } } ] }, "from": 0, "query": { "bool": { "must": [ { "range": { "happens_on": { "gte": "2013-12-06" } } }, { "range": { "created_at": { "gte": "2013-12-15" } } }, { "range": { "happens_on": { "lte": "2013-12-17" } } }, { "range": { "created_at": { "lte": "2013-12-17" } } } ] } }, "size": 10 } } ``` Still no results returned. Am I doing any obvious mistakes here?

Original source