Elasticsearch:Query syntax

Key concepts

Map your SQL knowledge to Elasticsearch terminology before writing queries:

The plug-in is built on Apache Calcite and translates SQL statements to Elasticsearch domain-specific language (DSL) queries at runtime.

Basic queries

All basic queries use the POST /_alisql endpoint.

POST /_alisql?pretty
{
  "query": "select * from monitor where host='100.80.xx.xx' limit 5"
}

POST /_alisql?pretty
{
  "query": "select * from monitor",
  "fetch_size": 3
}

POST /_alisql?pretty
{
  "query": "select * from monitor where host= ? ",
  "params": [{"type":"STRING","value":"100.80.xx.xx"}],
  "fetch_size": 1
}

{
  "columns": [
    {"name": "times", "type": "integer"},
    {"name": "value2", "type": "float"},
    {"name": "host", "type": "keyword"},
    {"name": "region", "type": "keyword"},
    {"name": "measurement", "type": "keyword"},
    {"name": "timestamp", "type": "date"}
  ],
  "rows": [
    [572575, 4649800.0, "100.80.xx.xx", "china-dd", "cpu", "2018-08-09T08:18:42.000Z"]
  ],
  "cursor": "u5HzAgJzY0BEWEYxWlhKNVFXNWtS****"
}

Paginated queries

For large result sets, use cursor-based pagination.

Step 1 — Initial request with `fetch_size`:

POST /_alisql?pretty
{
  "query": "select * from monitor",
  "fetch_size": 3
}

The response includes a cursor token when more data is available.

Step 2 — Continue with the cursor:

Pass the cursor value from the previous response to retrieve the next page:

POST /_alisql?pretty
{
  "cursor": "u5HzAgJzY0BEWEYxWlhKNVFXNWtS****"
}

Cursor query responses omit the columns field to reduce network latency. All other fields follow the same structure as the initial response.

Step 3 — End condition:

Keep sending cursor requests until the response contains no cursor field or no rows. At that point, all data has been retrieved.

JSON-formatted queries

Add format=org to return results in the same JSON format as native Elasticsearch DSL queries. JOIN statements are not supported.

POST /_alisql?format=org
{
  "query": "select * from monitor where host= ? ",
  "params": [{"type":"STRING","value":"100.80.xx.xx"}],
  "fetch_size": 1
}

The response uses the standard Elasticsearch hits structure:

{
  "_scroll_id": "DXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAsWYXNEdlVJZzJTSXFfOGluOVB4Q3Z****",
  "took": 18,
  "timed_out": false,
  "_shards": {"total": 1, "successful": 1, "skipped": 0, "failed": 0},
  "hits": {
    "total": 2,
    "max_score": 1.0,
    "hits": [
      {
        "_index": "monitor",
        "_type": "_doc",
        "_id": "2",
        "_score": 1.0,
        "_source": {
          "times": 572575,
          "value2": 4649800,
          "host": "100.80.xx.xx",
          "region": "china-dd",
          "measurement": "cpu",
          "timestamp": "2018-08-09T16:18:42+0800"
        }
      }
    ]
  }
}

Use the _scroll_id field for paged queries when working with this format. All other request parameters are the same as in basic queries.

Translate queries

The translate API converts a SQL statement to the equivalent Elasticsearch DSL query. Use it to debug queries or understand how the plug-in maps SQL conditions to DSL filters. JOIN statements are not supported.

POST _alisql/translate
{
  "query": "select * from monitor where host= '100.80.xx.xx' "
}

The response is the DSL equivalent of the SQL query:

{
  "size": 1000,
  "query": {
    "constant_score": {
      "filter": {
        "term": {
          "host": {
            "value": "100.80.xx.xx",
            "boost": 1.0
          }
        }
      },
      "boost": 1.0
    }
  },
  "_source": {
    "includes": ["times", "value2", "host", "region", "measurement", "timestamp"],
    "excludes": []
  }
}

Special queries

The aliyun-sql plug-in supports querying fields of the nested and text data types, which are not directly queryable in standard SQL databases.

Step 1 — Create an index with nested and text fields:

PUT user_info/
{
  "mappings": {
    "_doc": {
      "properties": {
        "addr": {"type": "text"},
        "age": {"type": "integer"},
        "id": {"type": "integer"},
        "name": {
          "type": "nested",
          "properties": {
            "first_name": {"type": "keyword"},
            "second_name": {"type": "keyword"}
          }
        }
      }
    }
  }
}

Step 2 — Insert documents in bulk:

PUT user_info/_doc/_bulk?refresh
{"index":{"_id":"1"}}
{"addr":"467 Hutchinson Court","age":80,"id":"1","name":[{"first_name":"lesi","second_name":"Adams"},{"first_name":"chaochaosi","second_name":"Aams"}]}
{"index":{"_id":"2"}}
{"addr":"671 Bristol Street","age":21,"id":"2","name":{"first_name":"Hattie","second_name":"Bond"}}
{"index":{"_id":"3"}}
{"addr":"554 Bristol Street","age":23,"id":"3","name":{"first_name":"Hattie","second_name":"Bond"}}

Step 3 — Query a nested field:

Use dot notation to reference nested fields:

POST _alisql
{
  "query": "select * from user_info where name.second_name='Adams'"
}

Response:

{
  "columns": [
    {"name": "id", "type": "integer"},
    {"name": "addr", "type": "text"},
    {"name": "name.first_name", "type": "keyword"},
    {"name": "age", "type": "integer"},
    {"name": "name.second_name", "type": "keyword"}
  ],
  "rows": [
    [1, "467 Hutchinson Court", "lesi", 80, "Adams"]
  ]
}

Step 4 — Query a text field:

Filter on a text type field using a keyword match:

POST _alisql
{
  "query": "select * from user_info where addr='Bristol'"
}

Response:

{
  "columns": [
    {"name": "id", "type": "integer"},
    {"name": "addr", "type": "text"},
    {"name": "name.first_name", "type": "keyword"},
    {"name": "age", "type": "integer"},
    {"name": "name.second_name", "type": "keyword"}
  ],
  "rows": [
    [2, "671 Bristol Street", "Hattie", 21, "Bond"],
    [3, "554 Bristol Street", "Hattie", 23, "Bond"]
  ]
}

Built-in functions and expressions