Elasticsearch SQL supports two types of patterns for matching multiple indices or tables:
- Elasticsearch multi-index
The Elasticsearch notation for enumerating, including or excluding multi index syntax is supported as long as it is quoted or escaped as a table identifier.
SHOW TABLES "*,-l*"; name | type ---------------+--------------- emp |BASE TABLE employees |ALIAS
Notice the pattern is surrounded by double quotes
". It enumerated
* meaning all indices however
it excludes (due to
-) all indices that start with
This notation is very convenient and powerful as it allows both inclusion and exclusion, depending on
the target naming convention.
The same kind of patterns can also be used to query multiple indices or tables.
SELECT emp_no FROM "e*p" LIMIT 1; emp_no --------------- 10001
There is the restriction that all resolved concrete tables have the exact same mapping.
LIKE statement (including escaping if needed) to match a wildcard pattern, based on one
SHOW TABLES command again:
SHOW TABLES LIKE 'emp%'; name | type ---------------+--------------- emp |BASE TABLE employees |ALIAS
The pattern matches all tables that start with
This command supports escaping as well, for example:
SHOW TABLES LIKE 'emp!%' ESCAPE '!'; name | type ---------------+---------------
Notice how now
emp% does not match any tables because
%, which means match zero or more characters,
has been escaped by
! and thus becomes an regular char. And since there is no table named
an empty table is returned.
In a nutshell, the differences between the two type of patterns are:
|Feature||Multi index|| SQL |
Type of quoting
One char pattern
Multi char pattern
Which one to use, is up to you however try to stick to the same one across your queries for consistency.
As the query type of quoting between the two patterns is fairly similar (
'), Elasticsearch SQL always
requires the keyword
LIKE for SQL