Filtering using Elasticsearch Query DSL

edit

Filtering using Elasticsearch Query DSL

edit

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

response = client.sql.query(
  format: 'txt',
  body: {
    query: 'SELECT * FROM library ORDER BY page_count DESC',
    filter: {
      range: {
        page_count: {
          gte: 100,
          lte: 200
        }
      }
    },
    fetch_size: 5
  }
)
puts response
POST /_sql?format=txt
{
  "query": "SELECT * FROM library ORDER BY page_count DESC",
  "filter": {
    "range": {
      "page_count": {
        "gte" : 100,
        "lte" : 200
      }
    }
  },
  "fetch_size": 5
}

Which returns:

    author     |                name                |  page_count   | release_date
---------------+------------------------------------+---------------+------------------------
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:

response = client.sql.query(
  format: 'txt',
  body: {
    query: 'SELECT * FROM library',
    filter: {
      terms: {
        _routing: [
          'abc'
        ]
      }
    }
  }
)
puts response
POST /_sql?format=txt
{
  "query": "SELECT * FROM library",
  "filter": {
    "terms": {
      "_routing": ["abc"]
    }
  }
}