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.

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"]
    }
  }
}