Index patterns like logs-* as SQL tables

Anything you can put after /…/_search works in FROM. A pattern such as "logs-*" is one table; the cluster expands it and the engine treats the result as a single row source.

The pattern is the table#

SELECT service, level, COUNT(*) AS events
FROM "logs-*"
WHERE "@timestamp" >= '2026-11-01' AND region = 'eu-west-1'
GROUP BY service, level
ORDER BY events DESC;

FlatQL sends POST /logs-*/_search with the range and term as filters and the grouping as a composite aggregation. The cluster expands the index pattern across every matching index; nothing in FlatQL enumerates them first. Daily or monthly indices, data streams and rollover sets all behave the same way.

Quoting names#

Double-quote any table name that is not a plain identifier: dashes, dots, the * wildcard and names starting with a digit.

SELECT COUNT(*) FROM "logs-2026.11.03";
SELECT COUNT(*) FROM "logs-2026.11.*";
SELECT COUNT(*) FROM orders;             -- no quotes needed

Two conveniences from the Snowflake dialect:

• IDENTIFIER('logs-2026.11.03') is accepted and rewritten to the quoted name.
• A dotted schema.index such as prod.events is read as the literal index name prod.events. There are no schemas in Elasticsearch, so the dot is part of the name.

Field names with special characters are quoted the same way: "@timestamp", "user.agent". Column names are case-insensitive in SQL but must exist in the mapping; DESCRIBE "logs-*" lists them.

Several indices without a wildcard#

A comma-separated list inside one quoted name goes to the cluster as a multi-index search:

SELECT level, COUNT(*) AS n
FROM "logs-2026.11.02,logs-2026.11.03"
GROUP BY level;

For anything beyond concatenation, UNION ALL between two explicit tables also works, at the cost of two fetches.

SQL aliases and self-joins#

A table alias behaves as in any database and is required when the same index appears twice:

SELECT a.service, COUNT(*) AS retried
FROM "logs-*" a
JOIN "logs-*" b
  ON a.service = b.service AND a.status_code = 503 AND b.status_code = 200
WHERE a."@timestamp" >= '2026-11-03'
  AND b."@timestamp" >= '2026-11-03'
GROUP BY a.service;

Each alias is a separate fetch with its own pushed filters, so qualify every column. An unqualified column in a multi-table statement cannot be attributed to an index and makes FlatQL fetch all fields of every table. See joins for the key pushdown between the two sides.

Mixed mappings across the pattern#

FlatQL reads field types with GET /logs-*/_field_caps?fields=*, cached for five minutes per connection. When the indices behind a pattern disagree about a field, the cluster lists more than one type and FlatQL takes the first concrete one for pushdown decisions. Rows are then loaded by value: a column that holds numbers in some documents and strings in others is read as text in the engine, with no error.

Three practical effects:

The engine's column names are case-insensitive while Elasticsearch field names are not, so FlatQL merges spellings that never share a document and suffixes the ones that do (_ID next to _id becomes _ID_2). When a pattern mixes mappings heavily, DESCRIBE one concrete index to see which type wins, and consult the type mapping page for what is filterable.

Access control and patterns#

Team roles grant read or write per index pattern, and FlatQL checks the pattern in FROM against those grants on every request. A wildcard in FROM is allowed only when every grant covers it: a member allowed logs-eu-* may query logs-eu-2026.* but not logs-*. Patterns are therefore safe to hand out; see index-level access for how the grants are set up and audited.

The cap and why filters matter#

A pattern is one table reference, and the engine pulls at most 50,000 rows for it per statement. With thirty daily indices behind logs-*, an unfiltered SELECT * takes the first 50,000 documents the cluster hands back and flags the result as truncated. Add a time range (pushed as a range filter), a LIMIT, or a GROUP BY the cluster can aggregate, and the pattern size stops mattering. The limits page lists the caps; the Show as DSL tab shows whether the request was a bounded search, an aggregation or a scroll.