Add Query Watches API to support Kibana Watcher UI

[ElenaStoeva]

Related to elastic/kibana#152142

We are requesting enhancements to the Query Watches API to better support fetching a large number of watches for the table in Kibana's Watcher UI. Currently, the table supports sorting by all columns (id, name, state, condition last met, last checked) and a search bar functionality that allows searching by id and name. Ideally, we would like to preserve these functionalities when switching to the Query Watches API.

We’ve explored several approaches using the current API, but each has limitations that block a performant and user-friendly experience:

Approach 1: Fetch a single page watches using from, size, sort, and query parameters

• What we tried: Fetching only the current page of watches by sending from, size, sort, and query parameters to the API. This would allow Elasticsearch to pre-filter and pre-sort the results before returning them to the UI.
• Issues:
  • sort only supports fields under metadata.*, so we can only sort by metadata.name. Note: The docs mention that we can also sort by id, but this doesn't work unless the user explicitly enables a dynamic setting.
  • If none of the watches has a metadata.name field and we try to sort by metadata.name, the API returns an error.
  • query can search metadata.* and _id, but wildcard search on _id is not supported, preventing users from performing partial ID searches in the UI.
  • from + size cannot exceed 10,000, which prevents fetching results beyond the 10,000th watch.
• Pros: Most performant for large watch counts, as it avoids fetching all watches at once.

Approach 2: Fetch all watches using from + size paging and filter client-side

• What we tried: Paginating through all watches using from and size, then filtering and sorting on the client.
• Issues:
  • Limited to the first 10,000 watches due to from + size constraint.
  • Inefficient for large watch counts - slow and heavy on both client and server.
  • Possibility of duplicate or inconsistent data if new watches are created while fetching (this is rare and somewhat mitigated by polling in the UI).

Approach 3: Use search_after for deep pagination and then filter client-side

• Approach (not tried in code): Use search_after to fetch all watches, overcoming the 10,000 item limit.
• Issues:
  • search_after relies on sorting, but sorting by _id is not currently supported by the API, so this method cannot be used in practice.

Request
We request that the Query Watches API be enhanced to provide enough support to make at least one of the described approaches above viable without introducing limitations.

[elasticsearchmachine]

Pinging @elastic/es-data-management (Team:Data Management)