Skip to content

Automate Elasticsearch Query Writing with AI Assistants

Elasticsearch is widely used and performs well in the search engine industry, however, we all know that writing Elasticsearch queries is a painful thing even for experienced DBA, unlike SQL, there are too many terminologies and reserved keywords to memorize, and the query JSON can be surprisingly long and complex based on your business context. With the popularity of AI technologies nowadays, how can it help/empower the writing of Elasticsearch DSL query experiences?

We all know that AI is pretty good for generating text-based content, and Elasticsearch query is also a special type of text with several disciplines. Can we just open the ChatGPT to let it generate the queries for you? The answer is yes and no.

Well, LLM products like ChatGPT can definitely generate Elasticsearch queries for you, but it does not mean that can meet your requirements. And in most cases, you can not directly use the query generated by ChatGPT, except for some very simple queries like the query to list all indices, ChatGPT can give you the correct one that meets your need: GET _cat/indices

Why? Because LLM lacks the context of your Elasticsearch server's context, if you like ChatGPT to generate a query for the index you recently working on, ChatGPT at least needs to know the index name, and to meet the specific query conditions, index mapping also becomes prerequisite of the correct query. Some engineers may say " That's fine I can cat the mapping and copy both index name & index mapping for the ChatGPT together with my query expectations represented by natural language". That's also my thoughts and I tried that before, but soon I got tired from copying and pasting those contexts and gave up on using them. Life back to the suffering and pain again just like before ChatGPT Birth。

Are there any tools available?

Yes, there is, today, I will work through with you How to level up your user experience of Elasticsearch queries by DocKit - an AI-empowered NoSQL GUI desktop client, open-source and cross-platform support for Mac, windows, and Linux.

DocKit supports integrating OpenAI and DeepSeek with your own API keys, the architecture is as follows:

DocKit AI assistant archetecture

Here is the flow of how it works:

  1. When you establish a connection to open a new editor tab in DocKit, it will try to fetch the indices and related mappings for the target Elasticsearch/OpenSearch, the mapping info will cached on the memory for later use.
  2. when you ask an AI assistant to generate queries or any Elasticsearch/OpenSearch-related questions, DocKit will help you to ingest the index name and mapping info automatically into the conversation context, hence the LLM knows what the natural language against index and index mapping looks like, it will help you get more accurate quires compare to directly ask the LLM.
  3. DocKit won't send any data inside an index unless you explicitly provide it in conversation messages, your data is still kept safe and private.
  4. Unless you configure and enable the AI assistant and send questions to it, even your index name and mapping info won't be exposed to LLM.

Let's try it out

You may question the performance of the above solution, let's use an example to have a quick look! Here is a sample data you can use and quickly ingest some sample data(you can also use your existing index as well) sample-stocks run the script ./samples/stocks/ingest.sh and follow the prompt to input related server info to ingest sample data.

The ingested sample data is structured as below:

json
{
  "mappings": {
    "dynamic": false,
    "properties": {
      "close": { "type": "float" },
      "date": { "type": "date" },
      "high": { "type": "float" },
      "low": { "type": "float" },
      "name": { "type": "keyword" },
      "open": { "type": "float" },
      "volume": { "type": "long" }
    }
  }
}

Before use start ask questions in DocKit AI Assistant, you need to config the proper keys for it. DocKit currently supports keys from OpenAI or DeepSeek. step into settings -> [OpenAI|DeepSeek] -> input model and API Key -> click Save & Enable, then you can enjoy the AI Assistant!

DocKit AI assistant config

Send prompt to DocKit AI Assistant

Once you configured the API key and related information, you can open a new connection, in the connection code editor, click the right-side logo to open AI Assistant.

DocKit AI assistant config

Open the AI Assistant, input your requirement in natural language(in this case: Return the first 30 names of all the different stock names), and click the send button, DocKit will ingest the selected index context together with your questions.

Then you can get contexture-related queries, which gives you more accurate and smooth experiences compared to copying & pasting a bunch of context information to LLM across different apps, DocKit did it for you.

This is just a very basic example, we encourage you to explore more possibilities by yourself, and we are actively working on enriching the functionalities including MCP support. If you are interested, feel free to raise questions/feature requests or contribute to the project DocKit

Dockit is an open-source desktop GUI client for Elasticsearch and OpenSearch across Mac, windows and Linux