Filtering using Elasticsearch Query DSL

Filtering using Elasticsearch Query DSL

One can filter the results that SQL will run on using a standard Elasticsearch Query DSL by specifying the query in the filter parameter.

  1. POST /_sql?format=txt
  2. {
  3.   "query": "SELECT * FROM library ORDER BY page_count DESC",
  4.   "filter": {
  5.     "range": {
  6.       "page_count": {
  7.         "gte" : 100,
  8.         "lte" : 200
  9.       }
  10.     }
  11.   },
  12.   "fetch_size": 5
  13. }

Which returns:

  1.     author     |                name                |  page_count   | release_date
  2. ---------------+------------------------------------+---------------+------------------------
  3. Douglas Adams  |The Hitchhiker's Guide to the Galaxy|180            |1979-10-12T00:00:00.000Z

A useful and less obvious usage for standard Query DSL filtering is to search documents by a specific routing key. Because Elasticsearch SQL does not support a routing parameter, one can specify a terms filter for the _routing field instead:

  1. POST /_sql?format=txt
  2. {
  3.   "query": "SELECT * FROM library",
  4.   "filter": {
  5.     "terms": {
  6.       "_routing": ["abc"]
  7.     }
  8.   }
  9. }