You are looking at preliminary documentation for a future release.
Not what you want? See the
current release documentation.
Filtering using Elasticsearch Query DSLedit
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"] } } }