Loading

ES|QL query builder

Warning: This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.

The ES|QL query builder allows you to construct ES|QL queries using JavaScript/TypeScript syntax. Consider the following example:

import { ESQL, E } from '@elastic/elasticsearch-esql-dsl'

const query = ESQL.from('employees')
  .sort(E('emp_no'))
  .keep('first_name', 'last_name', 'height')
  .eval({
    height_feet: E('height').mul(3.281),
    height_cm: E('height').mul(100),
  })
  .limit(3)

You can then see the assembled ES|QL query by printing the resulting query object:

console.log(query.render())
// FROM employees
// | SORT emp_no
// | KEEP first_name, last_name, height
// | EVAL height_feet = height * 3.281, height_cm = height * 100
// | LIMIT 3

To execute this query, pass it to the client.esql.query() endpoint:

import { Client } from '@elastic/elasticsearch'

const client = new Client({ node: process.env.ELASTICSEARCH_URL })
const response = await client.esql.query({ query: query.render() })

The response body contains a columns attribute with the list of columns included in the results, and a values attribute with the list of results for the query, each given as an array of column values. Here is a possible response body returned by the example query given above:

{
  "columns": [
    { "name": "first_name", "type": "text" },
    { "name": "last_name", "type": "text" },
    { "name": "height", "type": "double" },
    { "name": "height_feet", "type": "double" },
    { "name": "height_cm", "type": "double" }
  ],
  "is_partial": false,
  "took": 11,
  "values": [
    ["Adrian", "Wells", 2.424, 7.953144, 242.4],
    ["Aaron", "Gonzalez", 1.584, 5.1971, 158.4],
    ["Miranda", "Kramer", 1.55, 5.08555, 155]
  ]
}

You can also use the built-in helper for typed records:

const { records } = await client.helpers
  .esql({ query: query.render() })
  .toRecords<{ first_name: string; last_name: string; height: number }>()

To construct an ES|QL query you start from one of the ES|QL source commands:

The FROM command selects the indices, data streams, or aliases to be queried.

Examples:

import { ESQL } from '@elastic/elasticsearch-esql-dsl'

// FROM employees
const q1 = ESQL.from('employees')

// FROM employees-00001, other-employees-*
const q2 = ESQL.from('employees-00001', 'other-employees-*')

// FROM cluster_one:employees-00001, cluster_two:other-employees-*
const q3 = ESQL.from('cluster_one:employees-00001', 'cluster_two:other-employees-*')

// FROM employees METADATA _id
const q4 = ESQL.from('employees').metadata('_id')

// FROM employees METADATA _id, _score
const q5 = ESQL.from('employees').metadata('_id', '_score')

Note how in the last examples the optional METADATA clause of the FROM command is added as a chained method.

The ROW command produces a row with one or more columns, with the values that you specify.

Examples:

import { ESQL, f } from '@elastic/elasticsearch-esql-dsl'

// ROW a = 1, b = "two", c = null
const q1 = ESQL.row({ a: 1, b: 'two', c: null })

// ROW a = [1, 2]
const q2 = ESQL.row({ a: [1, 2] })

// ROW a = ROUND(1.23, 0)
const q3 = ESQL.row({ a: f.round(1.23, 0) })

The SHOW command returns information about the deployment and its capabilities.

Example:

import { ESQL } from '@elastic/elasticsearch-esql-dsl'

// SHOW INFO
const q = ESQL.show('INFO')

The TS command is the source command for time-series indices.

Example:

import { ESQL } from '@elastic/elasticsearch-esql-dsl'

// TS metrics METADATA _tsid
const q = ESQL.ts('metrics').metadata('_tsid')

Once you have a query object, you can add one or more processing commands to it. The following example shows how to create a query that uses the WHERE and LIMIT commands to filter the results:

import { ESQL, E } from '@elastic/elasticsearch-esql-dsl'

// FROM employees
// | WHERE still_hired == true
// | LIMIT 10
const query = ESQL.from('employees')
  .where(E('still_hired').eq(true))
  .limit(10)

All queries are immutable — each method call returns a new query object. This means you can safely branch from a common base:

const base = ESQL.from('employees')
  .where(E('still_hired').eq(true))

const byName = base.sort(E('last_name').asc()).limit(10)
const topEarners = base.sort(E('salary').desc()).limit(5)

// `base`, `byName`, and `topEarners` are three independent queries
Method ES|QL Command
.where(expr) WHERE
.eval(assignments) EVAL
.stats(aggs).by(fields) STATS ... BY
.sort(fields) SORT
.limit(n) LIMIT
.keep(fields) KEEP
.drop(fields) DROP
.rename(mapping) RENAME
.mvExpand(field) MV_EXPAND
.enrich(policy).on(field).with(fields) ENRICH ... ON ... WITH
.dissect(field, pattern) DISSECT
.grok(field, pattern) GROK
.lookupJoin(index).on(field) LOOKUP JOIN ... ON
.inlineStats(aggs).by(fields) INLINESTATS ... BY
.changePoint(field).on(key).as_(t, p) CHANGE_POINT ... ON ... AS
.sample(ratio) SAMPLE
.fork(branches) FORK
.fuse(method) FUSE
.completion(prompt).with(opts) COMPLETION ... WITH
.rerank(query).on(fields).with(opts) RERANK ... ON ... WITH

The ES|QL query builder for JavaScript/TypeScript provides multiple ways to create expressions and conditions in ES|QL queries.

The simplest option is to provide all ES|QL expressions and conditionals as strings. The following example uses this approach to add two calculated columns to the results using the EVAL command:

import { ESQL } from '@elastic/elasticsearch-esql-dsl'

// FROM employees
// | SORT emp_no
// | KEEP first_name, last_name, height
// | EVAL height_feet = height * 3.281, height_cm = height * 100
const query = ESQL.from('employees')
  .sort('emp_no')
  .keep('first_name', 'last_name', 'height')
  .eval('height_feet = height * 3.281', 'height_cm = height * 100')

A more advanced alternative is to replace the strings with the E() helper function, which creates InstrumentedExpression objects that support method chaining for comparisons, arithmetic, and more. The following example is functionally equivalent to the one above:

import { ESQL, E } from '@elastic/elasticsearch-esql-dsl'

// FROM employees
// | SORT emp_no
// | KEEP first_name, last_name, height
// | EVAL height_feet = height * 3.281, height_cm = height * 100
const query = ESQL.from('employees')
  .sort(E('emp_no'))
  .keep('first_name', 'last_name', 'height')
  .eval({
    height_feet: E('height').mul(3.281),
    height_cm: E('height').mul(100),
  })

Here the E() helper function is used as a wrapper around column names that initiates an ES|QL expression. The resulting object can be chained with comparison methods (.eq(), .gt(), .lt(), etc.), arithmetic methods (.add(), .mul(), etc.), and sort modifiers (.asc(), .desc(), .nullsFirst(), .nullsLast()).

Here is a second example, which uses a conditional expression in the WHERE command:

import { ESQL } from '@elastic/elasticsearch-esql-dsl'

// FROM employees
// | KEEP first_name, last_name, height
// | WHERE first_name == "Larry"
const query = ESQL.from('employees')
  .keep('first_name', 'last_name', 'height')
  .where('first_name == "Larry"')

Using the E() expression builder:

import { ESQL, E } from '@elastic/elasticsearch-esql-dsl'

// FROM employees
// | KEEP first_name, last_name, height
// | WHERE first_name == "Larry"
const query = ESQL.from('employees')
  .keep('first_name', 'last_name', 'height')
  .where(E('first_name').eq('Larry'))

For mixing literal ES|QL with dynamic values, the esql template tag provides safe interpolation:

import { esql, E } from '@elastic/elasticsearch-esql-dsl'

const minSalary = 50000
const dept = 'Engineering'

// salary > 50000 AND department == "Engineering"
const condition = esql`salary > ${minSalary} AND department == ${dept}`

Values are automatically escaped. InstrumentedExpression objects pass through without escaping, so you can freely mix them:

const expr = E('salary').gt(50000)
const condition = esql`${expr} AND department == ${dept}`

For complex conditions, you can pass a plain object using Op symbols:

import { ESQL, Op } from '@elastic/elasticsearch-esql-dsl'

const query = ESQL.from('employees')
  .where({
    department: 'Engineering',
    salary: { [Op.gt]: 50000 },
    [Op.or]: {
      level: { [Op.in]: ['senior', 'staff'] },
    },
  })

Available operators: Op.eq, Op.ne, Op.gt, Op.gte, Op.lt, Op.lte, Op.in, Op.like, Op.rlike, Op.and, Op.or, Op.not.

Combine expressions with standalone functions:

import { and_, or_, not_, E } from '@elastic/elasticsearch-esql-dsl'

const condition = and_(
  E('salary').gt(50000),
  or_(E('dept').eq('Engineering'), E('dept').eq('Sales')),
  not_(E('status').eq('inactive'))
)

const query = ESQL.from('employees').where(condition)

ES|QL, like most query languages, is vulnerable to code injection attacks if untrusted data provided by users is added to a query. To eliminate this risk, ES|QL allows untrusted data to be given separately from the query as parameters.

Continuing with the example above, let's assume that the application needs a findEmployeeByName() function that searches for the name given as an argument. If this argument is received by the application from users, then it is considered untrusted and should not be added to the query directly. Here is how to code the function in a secure manner:

function findEmployeeByName(name: string) {
  const query = ESQL.from('employees')
    .keep('first_name', 'last_name', 'height')
    .where(E('first_name').eq(E('?')))

  return client.esql.query({
    query: query.render(),
    params: [name],
  })
}

Here the part of the query in which the untrusted data needs to be inserted is replaced with a parameter, which in ES|QL is defined by the question mark. When using the expression builder, the parameter must be given as E('?') so that it is treated as an expression and not as a literal string.

The list of values given in the params argument to the query endpoint are assigned in order to the parameters defined in the query.

The ES|QL language includes a rich set of functions that can be used in expressions and conditionals. These can be included in expressions given as strings, as shown in the example below:

import { ESQL } from '@elastic/elasticsearch-esql-dsl'

// FROM employees
// | KEEP first_name, last_name, height
// | WHERE LENGTH(first_name) < 4
const query = ESQL.from('employees')
  .keep('first_name', 'last_name', 'height')
  .where('LENGTH(first_name) < 4')

All available ES|QL functions have JavaScript/TypeScript wrappers in the f namespace, which can be used when building expressions. Below is the example above coded using the function wrappers:

import { ESQL, f } from '@elastic/elasticsearch-esql-dsl'

// FROM employees
// | KEEP first_name, last_name, height
// | WHERE LENGTH(first_name) < 4
const query = ESQL.from('employees')
  .keep('first_name', 'last_name', 'height')
  .where(f.length('first_name').lt(4))

Function argument handling varies by function. Functions like length(), abs(), and avg() treat string arguments as field references (identifiers). Functions like concat(), startsWith(), and replace() treat string arguments as literal values. When you need to explicitly pass a field reference to a literal-mode function, wrap it with E():

// field reference via E(): STARTS_WITH(name, "A")
f.startsWith(E('name'), 'A')

// without E(), both are literals: STARTS_WITH("name", "A")
f.startsWith('name', 'A')

The f namespace includes 150+ function wrappers organized across the following categories:

f.avg('salary')
f.count()
f.countDistinct('dept')
f.max('height')
f.min('height')
f.sum('hours')
f.median('salary')
f.medianAbsoluteDeviation('salary')
f.percentile('latency', 99)
f.top('salary', 5, 'DESC')
f.values('tags')
f.stdDev('salary')
f.variance('salary')
f.weightedAvg('val', 'wt')
f.first('ts')
f.last('ts')
  1. AVG(salary)
  2. COUNT(*)
  3. COUNT_DISTINCT(dept)
  4. MAX(height)
  5. MIN(height)
  6. SUM(hours)
  7. MEDIAN(salary)
  8. MEDIAN_ABSOLUTE_DEVIATION(salary)
  9. PERCENTILE(latency, 99)
  10. TOP(salary, 5, DESC)
  11. VALUES(tags)
  12. STD_DEV(salary)
  13. VARIANCE(salary)
  14. WEIGHTED_AVG(val, wt)
  15. FIRST(ts)
  16. LAST(ts)

Aggregation functions return AggregationExpression, which extends InstrumentedExpression and adds a .where() method for conditional aggregations:

const query = ESQL.from('employees')
  .stats({
    eng_avg: f.avg('salary').where(E('dept').eq('Engineering')),
    total: f.count(),
  })
		
f.concat(E('first_name'), ' ', E('last_name'))  // CONCAT(first_name, " ", last_name)
f.length('name')
f.toUpper('name')
f.toLower('name')
f.substring('msg', 0, 100)
f.trim('name')
f.left('name', 5)
f.right('name', 3)
f.replace(E('msg'), 'old', 'new')          // REPLACE(msg, "old", "new")
f.repeat('star', 5)
f.startsWith(E('name'), 'prefix')          // STARTS_WITH(name, "prefix")
f.endsWith(E('name'), 'suffix')            // ENDS_WITH(name, "suffix")
f.contains('message', 'error')             // CONTAINS(message, "error")
f.split(E('tags'), ',')                     // SPLIT(tags, ",")
f.reverse('name')
f.locate(E('msg'), 'error')                // LOCATE(msg, "error")
f.space(10)
f.byteLength('data')
f.bitLength('data')
f.lpad(E('val'), 10, '0')                  // LPAD(val, 10, "0")
f.rpad(E('val'), 10, ' ')                  // RPAD(val, 10, " ")
  1. LENGTH(name)
  2. TO_UPPER(name)
  3. TO_LOWER(name)
  4. SUBSTRING(msg, 0, 100)
  5. TRIM(name)
  6. LEFT(name, 5)
  7. RIGHT(name, 3)
  8. REPEAT(star, 5)
  9. REVERSE(name)
  10. SPACE(10)
  11. BYTE_LENGTH(data)
  12. BIT_LENGTH(data) 
f.now()
f.dateDiff('day', 'hire_date', f.now())    // DATE_DIFF("day", hire_date, NOW())
f.dateTrunc('date', '1 month')            // DATE_TRUNC(date, "1 month")
f.dateExtract('year', 'hire_date')         // DATE_EXTRACT("year", hire_date)
f.dateParse('date_str', 'yyyy-MM-dd')     // DATE_PARSE("date_str", "yyyy-MM-dd")
f.dateFormat('ts', 'yyyy-MM-dd')          // DATE_FORMAT(ts, "yyyy-MM-dd")
  1. NOW() 
f.abs('change')
f.round('salary', -3)
f.ceil('price')
f.floor('price')
f.sqrt('area')
f.pow('base', 2)
f.log('value')
f.log10('value')
f.pi()
f.e()
f.tau()
f.clamp('val', 0, 100)
f.greatest('a', 'b')
f.least('a', 'b')
f.signum('val')
f.cbrt('val')
f.hypot('a', 'b')
  1. ABS(change)
  2. ROUND(salary, -3)
  3. CEIL(price)
  4. FLOOR(price)
  5. SQRT(area)
  6. POW(base, 2)
  7. LOG(value)
  8. LOG10(value)
  9. PI()
  10. E()
  11. TAU()
  12. CLAMP(val, 0, 100)
  13. GREATEST(a, b)
  14. LEAST(a, b)
  15. SIGNUM(val)
  16. CBRT(val)
  17. HYPOT(a, b) 
f.coalesce('nickname', 'first_name')

// CASE WHEN age < 18 THEN "minor" WHEN age < 65 THEN "adult" ELSE "senior" END
f.case_()
  .when(E('age').lt(18), 'minor')
  .when(E('age').lt(65), 'adult')
  .else_('senior')

f.greatest('a', 'b')
f.least('a', 'b')
  1. COALESCE(nickname, first_name)
  2. GREATEST(a, b)
  3. LEAST(a, b)

Search and full-text functions

f.match('title', 'search')           // MATCH(title, "search")
f.matchPhrase('title', 'exact phrase')  // MATCH_PHRASE(title, "exact phrase")
f.multiMatch('query', 'f1', 'f2')   // MULTI_MATCH("query", f1, f2)
f.term('status', 'active')          // TERM(status, "active")
f.kql('status: active')             // KQL("status: active")
f.qstr('title:search')              // QSTR("title:search")
f.knn('embedding', 10)
f.score()
  1. KNN(embedding, 10)
  2. SCORE() 
f.mvAvg('values')
f.mvCount('values')
f.mvMin('values')
f.mvMax('values')
f.mvSum('values')
f.mvMedian('values')
f.mvFirst('values')
f.mvLast('values')
f.mvConcat(E('tags'), ', ')  // MV_CONCAT(tags, ", ")
f.mvSort('values')
f.mvDedupe('values')
f.mvSlice('values', 0, 5)
f.mvZip('keys', 'values')
f.mvContains('tags', 'x')  // MV_CONTAINS(tags, "x")
f.mvAppend('a', 'b')
  1. MV_AVG(values)
  2. MV_COUNT(values)
  3. MV_MIN(values)
  4. MV_MAX(values)
  5. MV_SUM(values)
  6. MV_MEDIAN(values)
  7. MV_FIRST(values)
  8. MV_LAST(values)
  9. MV_SORT(values)
  10. MV_DEDUPE(values)
  11. MV_SLICE(values, 0, 5)
  12. MV_ZIP(keys, values)
  13. MV_APPEND(a, b) 
f.stX('point')
f.stY('point')
f.stDistance('a', 'b')
f.stIntersects('geo', 'shape')
f.stContains('geo', 'point')
f.stDisjoint('geo', 'shape')
f.stWithin('geo', 'boundary')
f.stCentroidAgg('location')
f.stExtentAgg('location')
f.stEnvelope('geo')
  1. ST_X(point)
  2. ST_Y(point)
  3. ST_DISTANCE(a, b)
  4. ST_INTERSECTS(geo, shape)
  5. ST_CONTAINS(geo, point)
  6. ST_DISJOINT(geo, shape)
  7. ST_WITHIN(geo, boundary)
  8. ST_CENTROID_AGG(location)
  9. ST_EXTENT_AGG(location)
  10. ST_ENVELOPE(geo) 
f.toBoolean('val')
f.toInteger('val')
f.toLong('val')
f.toDouble('val')
f.toString_('val')
f.toDatetime('val')
f.toIp('val')
f.toVersion('val')
f.toGeoPoint('val')
f.toGeoShape('val')
f.toCartesianPoint('val')
f.toCartesianShape('val')
f.toUnsignedLong('val')
  1. TO_BOOLEAN(val)
  2. TO_INTEGER(val)
  3. TO_LONG(val)
  4. TO_DOUBLE(val)
  5. TO_STRING(val)
  6. TO_DATETIME(val)
  7. TO_IP(val)
  8. TO_VERSION(val)
  9. TO_GEOPOINT(val)
  10. TO_GEOSHAPE(val)
  11. TO_CARTESIANPOINT(val)
  12. TO_CARTESIANSHAPE(val)
  13. TO_UNSIGNED_LONG(val) 
f.cidrMatch('ip', '10.0.0.0/8', '172.16.0.0/12')  // CIDR_MATCH(ip, "10.0.0.0/8", "172.16.0.0/12")
f.ipPrefix('ip', 24, 0)
f.networkDirection('src', 'dst')
  1. IP_PREFIX(ip, 24, 0)
  2. NETWORK_DIRECTION(src, dst) 
f.bucket('salary', 10000)
f.categorize('message')
  1. BUCKET(salary, 10000)
  2. CATEGORIZE(message) 
f.rate('bytes')
f.delta('counter')
f.deriv('counter')
f.tbucket('@timestamp', '1h')
f.trange('@timestamp', '24h')
f.avgOverTime('cpu')
f.minOverTime('cpu')
f.maxOverTime('cpu')
f.sumOverTime('bytes')
f.countOverTime('events')
  1. RATE(bytes)
  2. DELTA(counter)
  3. DERIV(counter)
  4. TBUCKET(@timestamp, 1h)
  5. TRANGE(@timestamp, 24h)
  6. AVG_OVER_TIME(cpu)
  7. MIN_OVER_TIME(cpu)
  8. MAX_OVER_TIME(cpu)
  9. SUM_OVER_TIME(bytes)
  10. COUNT_OVER_TIME(events) 
f.hash('algorithm', 'input')  // HASH("algorithm", input)
f.md5('input')
f.sha1('input')
f.sha256('input')
f.urlDecode('url')
f.urlEncode('url')
f.urlEncodeComponent('str')
  1. MD5(input)
  2. SHA1(input)
  3. SHA256(input)
  4. URL_DECODE(url)
  5. URL_ENCODE(url)
  6. URL_ENCODE_COMPONENT(str) 
f.vCosine('a', 'b')
f.vDotProduct('a', 'b')
f.vHamming('a', 'b')
f.vL1Norm('a', 'b')
f.vL2Norm('a', 'b')
f.vMagnitude('vec')
  1. V_COSINE(a, b)
  2. V_DOT_PRODUCT(a, b)
  3. V_HAMMING(a, b)
  4. V_L1_NORM(a, b)
  5. V_L2_NORM(a, b)
  6. V_MAGNITUDE(vec)

The stats() method accepts an object mapping output column names to aggregation expressions. Chain .by() to group the aggregation:

import { ESQL, E, f } from '@elastic/elasticsearch-esql-dsl'

// FROM employees
// | STATS avg_salary = AVG(salary), headcount = COUNT(*) BY department
// | SORT avg_salary DESC
const query = ESQL.from('employees')
  .stats({
    avg_salary: f.avg('salary'),
    headcount: f.count(),
  })
  .by('department')
  .sort(E('avg_salary').desc())

Use fork() to run multiple sub-queries in parallel, then fuse() to merge the results:

import { ESQL, E, f } from '@elastic/elasticsearch-esql-dsl'

const query = ESQL.from('articles')
  .fork(
    ESQL.branch()
      .where(f.match('title', 'elasticsearch'))
      .sort(E('_score').desc())
      .limit(50),
    ESQL.branch()
      .where(f.knn('embedding', 10))
      .sort(E('_score').desc())
      .limit(50),
  )
  .fuse('RRF')
  .limit(10)
		
const query = ESQL.from('logs')
  .enrich('ip_lookup')
  .on('client.ip')
  .with('geo.city', 'geo.country')
  .keep('message', 'geo.city', 'geo.country')
		
// Using DISSECT
const q1 = ESQL.from('logs')
  .dissect('message', '%{timestamp} %{level} %{msg}')
  .keep('timestamp', 'level', 'msg')

// Using GROK
const q2 = ESQL.from('logs')
  .grok('message', '%{TIMESTAMP_ISO8601:timestamp} %{LOGLEVEL:level}')
  .keep('timestamp', 'level')
		
// LLM completion
const q1 = ESQL.from('docs')
  .completion('Summarize this document')
  .with({ inferenceId: 'my-llm' })

// Semantic reranking
const q2 = ESQL.from('docs')
  .rerank('user query')
  .on('title', 'body')
  .with({ inferenceId: 'my-reranker', topN: 10 })
		
const query = ESQL.ts('metrics')
  .stats({
    bytes_rate: f.rate('bytes'),
    avg_cpu: f.avgOverTime('cpu'),
  })
  .by('host')
		
const query = ESQL.from('metrics')
  .changePoint('cpu_usage')
  .on('host')
  .as_('change_type', 'change_pvalue')

Every query object supports render(), toString(), and toJSON():

const query = ESQL.from('employees')
  .where(E('salary').gt(50000))
  .limit(10)

query.render()    // "FROM employees\n| WHERE salary > 50000\n| LIMIT 10"
query.toString()
query.toJSON()    // { query: "FROM employees\n| WHERE salary > 50000\n| LIMIT 10" }
  1. same as render()