› › ›

Фильтрация с помощью Elasticsearch Query DSL

Результаты запроса SQL можно фильтровать, используя стандартный Elasticsearch Query DSL, указав запрос в параметре filter.

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
}

Что возвращает:

    author     |                name                |  page_count   | release_date
---------------+------------------------------------+---------------+------------------------
Douglas Adams  |The Hitchhiker's Guide to the Galaxy|180            |1979-10-12T00:00:00.000Z

Полезное, но менее очевидное применение стандартной фильтрации Query DSL — поиск документов по конкретному ключу маршрутизации. Поскольку Elasticsearch SQL не поддерживает параметр routing, можно указать фильтр terms для поля _routing:

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

© 2023-2025 Elasticsearch
As of September 2024, Elasticsearch is available under a choice of three licenses: the Server Side Public License (SSPL), the Elastic License, or the AGPLv3 (OSI approved).
Elasticsearch and the Elasticsearch logo are trademarks of Elasticsearch B.V., registered in the U.S. and in other countries.
https://www.elastic.co/guide/en/elasticsearch/reference/8.17/sql-rest-filtering.html