Filtering using Elasticsearch Query DSL
editFiltering using Elasticsearch Query DSL
editOne can filter the results that SQL will run on using a standard Elasticsearch Query DSL by specifying the query in the filter parameter.
resp = client.sql.query( format="txt", query="SELECT * FROM library ORDER BY page_count DESC", filter={ "range": { "page_count": { "gte": 100, "lte": 200 } } }, fetch_size=5, ) print(resp)
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
const response = await client.sql.query({ format: "txt", query: "SELECT * FROM library ORDER BY page_count DESC", filter: { range: { page_count: { gte: 100, lte: 200, }, }, }, fetch_size: 5, }); console.log(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:
resp = client.sql.query( format="txt", query="SELECT * FROM library", filter={ "terms": { "_routing": [ "abc" ] } }, ) print(resp)
response = client.sql.query( format: 'txt', body: { query: 'SELECT * FROM library', filter: { terms: { _routing: [ 'abc' ] } } } ) puts response
const response = await client.sql.query({ format: "txt", query: "SELECT * FROM library", filter: { terms: { _routing: ["abc"], }, }, }); console.log(response);
POST /_sql?format=txt { "query": "SELECT * FROM library", "filter": { "terms": { "_routing": ["abc"] } } }