The best LLM writes correct Elasticsearch ES|QL 59% of the time. Here's what breaks the other 41%.

Get hands-on with Elasticsearch: Dive into our sample notebooks in the Elasticsearch Labs repo, start a free cloud trial, or try Elastic on your local machine now.

We gave four models 500 natural-language questions from BIRD's Mini-Dev set, asked each for one Elasticsearch Query Language (ES|QL) query, and graded all 6,000 answers on whether the right rows came back. The best model got 59% correct on a single attempt with nothing but the index mappings, and wrote a query that failed to parse only 7 times out of 500. Grammar is no longer the ceiling. A compact ES|QL reference in the prompt is worth around 10 points to a smaller model and takes 10 points off the strongest one. Of the queries that still miss, roughly half throw a precise Elasticsearch error that a single retry can fix. The rest run cleanly and come back empty, because the model guessed a value that isn't in the data.

We started from Text-to-ES Bench (ACL 2025), which measures how well large language models query Elasticsearch using Query DSL. Its models pair a DSL query with Python post-processing, and pandas assembles the multi-index answers. We wanted to see what changes when the model can join inside the query. LOOKUP JOIN joins indices natively, so a multi-index question becomes one statement that Elasticsearch executes end to end.

The dataset: BIRD benchmark, Mini-Dev set

Text-to-ES Bench sourced its questions from BIg Bench for LaRge-scale Database Grounded Text-to-SQL Evaluation (BIRD), a widely used text-to-SQL benchmark. We do the same, using BIRD's Mini-Dev set: 500 natural-language questions over real relational databases. Every task gives you three things:

1. A question in English: The prompt that the model translates.

2. A gold SQL query: The reference query that produces the answer.

3. The returned rows: The answer key that you grade against.

The part we rely on is that the ground truth is the rows, not the SQL. We never compare the generated query text against the gold SQL; a prediction is graded only on the rows that it returns. A query that doesn’t parse returns no rows, and no rows is a zero. "The names of the three drivers with the shortest average pit stop" should produce the same answer, whether you computed it in SQL, ES|QL, or by hand. So we keep BIRD's questions and BIRD's answer key and swap only the language that the model writes in.

Here’s one task, from the student_club database:

Question:  List out the full name and total cost that member id "rec4BLdZHS2Blfp4v" incurred?
Evidence:  full name refers to first_name, last_name

Gold SQL:  SELECT T1.first_name, T1.last_name, SUM(T2.cost)
           FROM member AS T1
           INNER JOIN expense AS T2 ON T1.member_id = T2.link_to_member
           WHERE T1.member_id = 'rec4BLdZHS2Blfp4v'

Gold rows: [["Sacha", "Harrison", 866.25]]

That [["Sacha", "Harrison", 866.25]] is the answer key. We also pass through BIRD's "evidence" hints (the full name refers to... line above) exactly as the dataset provides them; the questions are written assuming that you have them.

Getting BIRD into Elasticsearch

Each table becomes its own index, deliberately not denormalized. Flattening the schema in advance would quietly answer the hard part of the question for the model, and we would end up measuring our data modeling rather than its querying.

Indices on the right side of a LOOKUP JOIN must use lookup index mode:

es.indices.create(index=idx, settings={"index.mode": "lookup"}, mappings=mapping)

And anything you join or group on needs to be a keyword rather than a text field, so text columns become keyword with an ignore_above guard. That keeps them usable in WHERE equality, STATS ... BY, LOOKUP JOIN, and LIKE, while staying under Lucene's term limit:

KEYWORD_IGNORE_ABOVE = 8000  # keeps UTF-8 byte length under Lucene's 32766 term limit

props[field] = {"type": "keyword", "ignore_above": KEYWORD_IGNORE_ABOVE}

All in, that’s roughly 3.9 million documents across 75 indices, running on Elasticsearch 9.5. If you haven’t built a join like this before, this walkthrough of native joins in Elasticsearch covers the index-mode requirements in more depth.

How we prompted the models and scored ES|QL accuracy

The prompt mirrors BIRD's zero-shot protocol: a schema block, the evidence hint, the question, and an instruction to return only the query. The one deviation is that the schema is presented as Elasticsearch index mappings instead of CREATE TABLE DDL, because the target language is ES|QL.

SYSTEM_PROMPT = (
    "You are an expert Elasticsearch ES|QL query writer. You translate a natural-language "
    "question into ONE valid ES|QL query that runs against the provided indices.\n\n"
    "ES|QL is a piped query language: FROM <index> | WHERE ... | STATS ... BY ... | SORT ... | LIMIT ...\n"
    "It is NOT SQL and NOT Elasticsearch Query DSL. To join indices, use LOOKUP JOIN.\n\n"
    "Think step by step, then return ONLY the final ES|QL query."
)

Every query gets one attempt. That’s deliberate, because one call with one prompt isolates what the model knows from what a scaffold could recover.

We ran three prompt variants against four models:

1. base: Schema, evidence, question; whatever ES|QL the model already knows.

2. focused skill: The above, preceded by a compact subset of the ES|QL skill: its SKILL.md overview plus the language reference, the generation tips, and the query patterns. The parts that are irrelevant to relational queries, time series, PromQL, and full-text search are left out.

3. full skill: The same, but with the complete skill attached and every reference file included.

In both skill variants, the files are pasted into the prompt as a static block. A skill normally reaches the model through a trigger, an agent deciding it needs the reference and loading it. We cut that step out so the variable under test is the content of the reference.

The models tested were gpt-5.5, claude-opus-4-8, claude-sonnet-4-6, and gpt-5.4-mini. 

Scoring runs the generated ES|QL through the _query API and compares the result to BIRD's gold rows as a set. Row order, duplicate rows, column order, and extra returned columns are ignored. One standard: the query returns the right data.

We decided to ignore the presentation details because they say nothing about whether the model understood the question. Take the student_club task from above and a model answer that a strict tuple comparison rejects:

gold: [["Sacha", "Harrison", 866.25]]
pred: [["Sacha Harrison", 866.25]]

The model built a full_name where the gold SQL kept first_name and last_name apart. They produced the same data, same rows. Extra columns are the same class of problem and are more common; a model that answers KEEP atom_id, element when the question only asked for the element has still found the element.

How accurate is LLM-written ES|QL?

Execution accuracy, 500 questions per cell:

Two things stand out:

1. The reference is worth about 10 points to every model except the strongest: Opus, Sonnet, and gpt-5.4-mini gain between 9.0 and 10.6 points from the focused skill; gpt-5.5 loses 10.2. It was already at 59.0% with nothing but the schema, and it misformed only 7 of 500 queries, so it had no grammar problem for a reference to fix, and handing it one cost it more than it could possibly win.

2. The gains are smaller than the error counts suggest: The skill nearly eliminated the biggest failure in the run: across all 6,000 queries, join errors fell from 939 to 354. Aggregate accuracy didn’t move. The reason shows up one column over, as a class of error that barely existed before: Found ambiguous reference went from 120 to 706. The errors weren’t fixed so much as renamed, and we take that apart in the next section. Documentation teaches a model the grammar of ES|QL; it doesn’t teach it the shape of your data.

Four ways that ES|QL generation breaks

The percentages below are out of the misses. A miss is any query that didn’t come back with the right data, whether it failed to run or it ran and returned the wrong rows. Getting the data right and the shape wrong doesn’t count.

1. ES|QL LOOKUP JOIN needs the same field name on both sides

Join key naming is the largest failure class: 134 of Opus's 298 base misses, roughly 45% of them. SQL joins columns with different names, and BIRD's schemas rely on it (superhero.skin_colour_id joins to colour.id). ES|QL's bare join form does not. LOOKUP JOIN <index> ON <field> takes a single field name that must exist on both sides, closer to SQL's JOIN ... USING than to JOIN ... ON. Elasticsearch 9.2 added a second form that lifts this restriction, but only when the two keys have different names.  That condition is where our run went sideways, and we come back to it at the end of this section.

A model reaching for SQL join syntax writes this:

FROM superhero__superhero
| LOOKUP JOIN superhero__colour ON skin_colour_id = id
| WHERE colour == "Green"

line 2:51: mismatched input '=' expecting {<EOF>, '|', 'and', ...}

Give it the skill, and it learns the ON <field> form and then trips one step later, still reaching for the left-hand name (expense.link_to_budget points at budget.budget_id):

line 3:39: Unknown column [link_to_budget] in right side of join

Across the run, 212 join queries failed to parse at all, the SQL-shaped ON a = b among them, and another 226 parsed but were rejected for the right-hand name. There are three ways out, depending on what you control.

1. Rename at query time. One line before the join:

FROM student_club__expense
| WHERE expense_description == "Post Cards, Posters" AND expense_date == "2019-8-20"
| RENAME link_to_budget AS budget_id
| LOOKUP JOIN student_club__budget ON budget_id
| KEEP event_status

One wrinkle: RENAME replaces the target column if the name you rename to already exists on the left. Joining superhero to colour is exactly that case, since both carry an id, so move the collision out of the way in the same command (renames apply left to right):

FROM superhero__superhero
| RENAME id AS hero_id, skin_colour_id AS id
| LOOKUP JOIN superhero__colour ON id
| WHERE colour == "Green"

2. Fix it in the data model. Give foreign keys the same name as the primary key that they point at, and the problem disappears permanently. It’s cheap at design time, which is why we call it a modeling habit.

3. Use a join predicate (Elasticsearch 9.2 and later). Elasticsearch 9.2 added complex join predicates that compare differently named fields directly, the way that SQL taught you to:

| LOOKUP JOIN student_club__budget ON link_to_budget == budget_id

Every name in a predicate must be unambiguous, so this form doesn’t rescue the case where the key already exists on both sides. That case is most of BIRD, and it’s where our edited skill backfired. Telling the models to prefer the predicate over RENAME dropped gpt-5.5's use of RENAME from 47% of its joins to 11%, and the error that RENAME was preventing showed up in its place:

Found ambiguous reference to [id]; matches any of [line 1:1 [id], line 3:15 [id]]

Across the run, that error went from 120 to 706, while the join bucket fell from 939 to 354; almost exactly a wash. The 9.2 release write-up covers the new join forms.

2. SQL syntax the ES|QL parser rejects

The models that struggle most are the ones reaching for SQL habits. This is where gpt-5.4-mini lost 236 of its 500 base queries and Sonnet lost 181.

Single quotes are the sneakiest of these, because in ES|QL, double quotes delimit strings and single quotes do not. Here Sonnet reached for both habits at once, a bare = and a single-quoted string, and the parser stopped at the quote:

FROM financial__client
| WHERE gender = 'F'

line 2:18: token recognition error at: '''

This is the one category that in-context documentation helps with. Sonnet's 181 syntax errors became 86, and gpt-5.4-mini's 236 became 150. If you’re pointing a smaller model at ES|QL, a one-page syntax card is the single highest-return thing that you can put in the prompt. A full language reference isn’t better.

3. Counting across a one-to-many LOOKUP JOIN

LOOKUP JOIN fans out the way that a SQL join does; when a row on the left matches several rows in the lookup index, you get one output row per match. A model that forgets this counts the joined rows instead of the entity that the question asked about. This is around 32% of the runs-but-wrong bucket.

Join a member to their expenses, and you get one row per expense, so a plain count answers How many expenses, when the question asked How many members:

FROM student_club__expense
| RENAME link_to_member AS member_id
| LOOKUP JOIN student_club__member ON member_id
| STATS members = COUNT(*)

COUNT(*) here counts expense rows. The fix is to count the entity that you actually mean or to aggregate before the join rather than after:

| STATS members = COUNT_DISTINCT(member_id)

4. The model guessed a value that isn't in your data

The fourth failure class is the model inventing a value that looks plausible and isn’t what’s stored.

BIRD's databases are full of these. A transaction type is stored as 'VYBER', not 'withdrawal'. Ask for withdrawals, and gpt-5.4-mini writes the English word, which matches nothing:

FROM financial__trans
| WHERE account_id == 3 AND type == "withdrawal"
| STATS requests = COUNT(*) BY k_symbol

The query is valid ES|QL and runs cleanly. It just comes back empty, because nothing in that column says withdrawal. The same pattern shows up across the dataset:

• A lab result is 'negative', not '-' or false

• Dates frequently live as keyword strings rather than date fields, which breaks any date function that the model reaches for and accounts for roughly 17% to 24% of wrong results on its own

• Ranking questions confuse position with a stored rank column

A schema block cannot fix this, because a schema tells you that a column is a keyword and never tells you which keywords are in it. The fix is sample values in the prompt: a handful of rows or the distinct values of low-cardinality columns. This is the failure class where retrieval helps and documentation does not.

Conclusion: What this means for text-to-ES|QL in production

We draw three conclusions from the run:

1. The newest models already write good ES|QL: Single call, zero shot, nothing but a schema, and the strongest model still gets the data right on most questions, while almost never writing one that fails to run. ES|QL isn’t exotic to frontier models anymore.

2. A new language feature doesn’t automatically become a win: Elasticsearch 9.2's join predicate removes the restriction behind our largest failure bucket, and pointing the models at it did cut that bucket from 939 errors to 354. Accuracy didn’t move, because the models spent the savings on a new error. A feature only pays once the guidance says when not to use it.

3. Prompt additions pay off for every model except the strongest: A compact syntax reference is worth around 10 points to a smaller model, a fuller one doesn’t help more, and the frontier model needs neither. Give it advice it didn’t need, and you can take 10 points off it.

Roughly half of the failures throw a precise, actionable Elasticsearch error (Unknown column [gender_id] in right side of join tells you exactly what to fix), so a single retry with the error text appended should recover a large share of them when using an agent like Elastic Agent Builder. 

The other half fail silently on guessed values, and no retry helps; those need sample rows and value lookups in the prompt. Grammar is no longer the ceiling. Knowing your schema and your values is, and that’s a retrieval problem, a much more tractable one.

Resources

• Browse the benchmark harness and all 6,000 scored queries, or filter them in the live interactive report

• LOOKUP JOIN command reference, including multi-field joins and the predicate form

• The Elasticsearch 9.2 ES|QL release write-up for the new join forms

• Text-to-ES Bench (ACL 2025), the Query DSL benchmark that this work builds on