Filtering using Elasticsearch Query DSL
You can filter the results that SQL will run on using a standard Elasticsearch Query DSL by specifying the query in the filter parameter.
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.000ZTip
A useful and less obvious usage for standard Query DSL filtering is to search documents by a specificrouting key. Because Elasticsearch SQL does not support arouting parameter, one can specify aterms filter for the_routing field instead:
POST /_sql?format=txt{ "query": "SELECT * FROM library", "filter": { "terms": { "_routing": ["abc"] } }}