Elasticsearch Esql

{
  "name": "elasticsearch-esql",
  "version": "1.1.0",
  "type": "module",
  "description": "Execute ES|QL queries against Elasticsearch",
  "author": "elastic",
  "license": "Elastic-2.0",
  "dependencies": {
    "@elastic/elasticsearch": "^9.2.1"
  }
}


# Query DSL to ES|QL Migration Guide

This guide helps you migrate from Elasticsearch Query DSL (JSON-based queries) to ES|QL (piped query language).

## Table of Contents

- [Overview: Key Differences](#overview-key-differences)
- [Basic Query Structure](#basic-query-structure)
- [Match All Query](#match-all-query)
- [Term Query (Exact Match)](#term-query-exact-match)
- [Match Query (Full-Text Search)](#match-query-full-text-search)
- [Match Phrase Query](#match-phrase-query)
- [Multi-Match Query](#multi-match-query)
- [Query String Query](#query-string-query)
- [Range Query](#range-query)
- [Bool Query](#bool-query)
- [Exists Query](#exists-query)
- [Wildcard / Prefix Query](#wildcard--prefix-query)
- [Regexp Query](#regexp-query)
- [Aggregations](#aggregations)
- [Sorting](#sorting)
- [Field Selection (\_source)](#field-selection-_source)
- [Pagination](#pagination)
- [Script Fields](#script-fields)
- [LOOKUP JOIN (Replaces Enrichment Patterns)](#lookup-join-replaces-enrichment-patterns)
- [Filters Aggregation (Per-Aggregation WHERE)](#filters-aggregation-per-aggregation-where)
- [Pipeline Aggregations (Chained STATS)](#pipeline-aggregations-chained-stats)
- [Highlighting](#highlighting)
- [ES|QL Limitations (vs Query DSL)](#esql-limitations-vs-query-dsl)
- [Migration Checklist](#migration-checklist)
- [Performance Considerations](#performance-considerations)
- [Quick Reference Table](#quick-reference-table)

## Overview: Key Differences

| Aspect           | Query DSL               | ES\|QL                          |
| ---------------- | ----------------------- | ------------------------------- |
| Format           | JSON                    | Piped text                      |
| Execution        | Translated to Lucene    | Native execution engine         |
| Default results  | 10                      | 1,000                           |
| Max results      | 10,000 (configurable)   | 10,000 (configurable)           |
| Aggregations     | Nested JSON structure   | `STATS ... BY` command          |
| Full-text search | `match`, `query_string` | `MATCH()`, `QSTR()`, `KQL()`    |
| Scoring          | Automatic with queries  | Explicit with `METADATA _score` |

### When to Use ES|QL vs Query DSL

**Use ES|QL for:**

- Log exploration and ad-hoc analysis
- Time-series data analysis
- Simple to moderate aggregations
- Data transformation pipelines
- Interactive troubleshooting

**Use Query DSL for:**

- Complex nested aggregations
- Advanced scoring and boosting
- Nested/parent-child document queries
- Features not yet in ES|QL (see Limitations)

---

## Basic Query Structure

### Query DSL

```json
POST /my-index/_search
{
  "query": { ... },
  "aggs": { ... },
  "sort": [ ... ],
  "size": 100,
  "_source": ["field1", "field2"]
}
```

### ES|QL

```esql
FROM my-index
| WHERE <conditions>
| STATS <aggregations> BY <groupings>
| SORT <field> DESC
| KEEP field1, field2
| LIMIT 100
```

---

## Match All Query

### Query DSL

```json
{
  "query": {
    "match_all": {}
  },
  "size": 100
}
```

### ES|QL

```esql
FROM my-index
| LIMIT 100
```

---

## Term Query (Exact Match)

### Query DSL

```json
{
  "query": {
    "term": {
      "status": "published"
    }
  }
}
```

### ES|QL

```esql
FROM my-index
| WHERE status == "published"
```

### Multiple Terms (terms query)

#### Query DSL

```json
{
  "query": {
    "terms": {
      "status": ["published", "draft"]
    }
  }
}
```

#### ES|QL

```esql
FROM my-index
| WHERE status IN ("published", "draft")
```

---

## Match Query (Full-Text Search)

### Query DSL

```json
{
  "query": {
    "match": {
      "title": "elasticsearch guide"
    }
  }
}
```

### ES|QL (8.17+)

```esql
FROM my-index
| WHERE MATCH(title, "elasticsearch guide