Query in elasticsearch with multiple ranges on multiple dates

Question

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?

Answer 1

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"
                        }
                    }
                }
            ]
        }
    }
}
'