> For the complete documentation index, see [llms.txt](https://docs.appstrategy.com/apprules-r-documentation/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.appstrategy.com/apprules-r-documentation/platform/platform-features/system-settings/data-sources/connection-settings/search/elasticsearch.md). # ElasticSearch Connects to Elasticsearch clusters (self-hosted, Elastic Cloud and Elastic Cloud Serverless) through the REST API. Each index is exposed as a table and each document as a row. SQL statements are translated to Elasticsearch searches and document operations. #### Connection Properties **Connection** | Property | Default | Notes | | -------- | ------------------------ | --------------------------------------------------------------------- | | Server | `https://localhost:9200` | Server URL including scheme and port. Required unless Cloud ID is set | | Cloud ID | — | Elastic Cloud deployment ID. When set, it is used instead of Server | | Timeout | `60` | Request timeout, seconds | **Authentication** **Auth Scheme** selects the mechanism: `ApiKey` (default), `Basic` or `Bearer`. | Property | Used by | Notes | | ------------ | ------- | -------------------------------------------------------------------------------------------------------- | | API Key | ApiKey | The encoded API key (base64 of `id:api_key`), as shown by Elasticsearch / Kibana when the key is created | | User | Basic | User name | | Password | Basic | Password | | Bearer Token | Bearer | Access token | **Network** Proxy: **Proxy Auto Detect** (default `true`), **Proxy Server**, **Proxy Port** (default `80`), **Proxy User**, **Proxy Password**, **Proxy Exceptions** (semicolon-separated bypass list), **Proxy Auth Scheme** (`BASIC` default, `DIGEST`, `NEGOTIATE`, `NTLM`). Firewall: **Firewall Type** (`NONE` default, `TUNNEL`, `SOCKS4`, `SOCKS5`, `HTTP`), **Firewall Server**, **Firewall Port**, **Firewall User**, **Firewall Password**. **SSL Server Cert** — `*` to accept any certificate (for example a self-signed certificate on a development cluster), or a specific thumbprint, PEM content or file path. **Logging** | Property | Default | Notes | | --------- | ---------------------------------------------------------------------------------- | ----------------------------- | | Log Level | `0` | `0` disabled, `1` log to file | | Log File | `%APPDATA%\appStrategy\PortalData\Default\Logs\Connectors\Elasticsearch_Debug.log` | | #### Supported SQL **Tables and columns** * Every index is a table. System indices (names starting with `.`) are hidden. * Columns come from the index mapping. Fields of nested objects are exposed with a dotted name, e.g. `address.city`. * Every table has an `_id` column holding the document ID. It is the primary key. * Elasticsearch has no foreign keys and no transactions. **SELECT** ```sql SELECT * FROM orders SELECT order_id, customer, amount FROM orders WHERE amount > 1000 ORDER BY amount DESC SELECT TOP 50 * FROM orders WHERE country IN ('FR', 'IE') AND (status = 'open' OR shipped_date IS NULL) SELECT COUNT(*) FROM orders WHERE status = 'open' ``` | Clause | Supported | | ----------- | ----------------------------------------------------------------------------------- | | Columns | `*` or a list of field names, with optional aliases (`amount AS total`) | | Aggregates | `COUNT`, `SUM`, `AVG`, `MIN`, `MAX` over the whole result (no `GROUP BY`) | | WHERE | See operators below | | ORDER BY | One or more fields, `ASC` / `DESC` | | TOP / LIMIT | Maximum number of rows: `SELECT TOP 10 …` or `… LIMIT 10` | | OFFSET | Skip rows: `… LIMIT 10 OFFSET 20`. Limited to the first 10,000 results of the index | Without `TOP` or `LIMIT`, all matching documents are returned, including beyond Elasticsearch's 10,000-result window. **WHERE operators** | Operator | Example | Notes | | ------------------------------- | -------------------------------- | --------------------------------------------------------- | | `=`, `<>`, `!=` | `status = 'open'` | Exact match | | `>`, `>=`, `<`, `<=` | `amount >= 100` | Numbers and dates | | `LIKE`, `NOT LIKE` | `customer LIKE 'A%'` | `%` any characters, `_` one character; not case-sensitive | | `IN`, `NOT IN` | `country IN ('FR', 'IE')` | | | `IS NULL`, `IS NOT NULL` | `shipped_date IS NULL` | Field missing / present in the document | | `AND`, `OR`, `NOT`, parentheses | `a = 1 AND (b = 2 OR NOT c = 3)` | | Exact-match and `LIKE` comparisons work on `keyword` fields. For a `text` field (full-text analysed), use its keyword sub-field when the mapping has one: `customer.keyword = 'Contoso Ltd'`. Dates can be written in ISO format (`'2026-02-13'`, `'2026-02-13T10:30:00'`) or in the date format of the appRules environment. **INSERT, UPDATE, DELETE** ```sql INSERT INTO orders (_id, customer, amount) VALUES ('1001', 'Contoso', 250.00) INSERT INTO orders (customer, amount) VALUES ('Contoso', 250.00) UPDATE orders SET status = 'closed' WHERE _id = '1001' UPDATE orders SET status = 'archived' WHERE order_date < '2020-01-01' DELETE FROM orders WHERE _id = '1001' DELETE FROM orders WHERE status = 'cancelled' ``` * INSERT creates one document. When `_id` is omitted, Elasticsearch generates it. Inserting with an existing `_id` replaces that document. * UPDATE changes only the listed fields, in every document matching the WHERE clause. `_id` cannot be changed. * UPDATE and DELETE without a WHERE clause affect every document of the index. **Stored procedures** The connector provides built-in procedures for operations that have no SQL equivalent. They are listed with their parameters under the data source's stored procedures, and can be called with `EXEC`: ```sql EXEC ProcedureName @Param1 = 'value', @Param2 = 10 ``` **Parameters** Named parameters are written `@name` (the `:name` form is also accepted). ```sql SELECT * FROM orders WHERE customer = @customer AND amount > @minAmount ``` **Not supported** * `JOIN`, sub-queries and `UNION` * `GROUP BY` and `HAVING` * `BETWEEN`, functions and calculated expressions * `CREATE` / `ALTER` / `DROP` * Transactions #### Data Types | Elasticsearch type | Read as | | ----------------------------------------------- | -------------- | | `keyword`, `text` and other types | Text | | `integer`, `short`, `byte` | Integer | | `long` | 64-bit integer | | `float`, `half_float`, `scaled_float`, `double` | Floating point | | `boolean` | Boolean | | `date` | Date/time | | `binary` | Binary | --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation by asking a question. Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters: ``` GET https://docs.appstrategy.com/apprules-r-documentation/platform/platform-features/system-settings/data-sources/connection-settings/search/elasticsearch.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.