How to use Elasticsearch to prompt ChatGPT with natural language

By

Enrico Zimuel

blog-thumb-elasticsearch-gears-light-blue.png

These days everyone is talking about ChatGPT. One of the cool features of this large language model (LLM) is the ability to generate code. We used it to generate Elasticsearch DSL queries. The goal is to search in Elasticsearch® with sentences like “Give me the first 10 documents of 2017 from the stocks index.” This experiment showed that it is possible, with some limitations. In this post, we describe this experiment and the open source library that we published for this use case.

Can ChatGPT generate Elasticsearch DSL?

We start the experiment with some tests focusing on the ability of ChatGPT to generate Elasticsearch DSL query. For this scope, you need to provide some context to ChatGPT about the structure of the data that you want to search. 

In Elasticsearch, data is stored in an index, which is similar to a "table" in a relational database. It has a mapping that defines multiple fields and their types. This means we need to provide the mapping information of the index that we want to query. By doing so, ChatGPT has the necessary context to translate the query into Elasticsearch DSL.

Elasticsearch offers a get mapping API to retrieve the mapping of an index. In our experiment, we used a stocks index data set available here. This data set contains five years of stock prices of 500 Fortune companies, spanning from February 2013 to February 2018.

Here we reported the first five lines of the CSV file containing the data set:

date,open,high,low,close,volume,name
2013-02-08,15.07,15.12,14.63,14.75,8407500,AAL
2013-02-11,14.89,15.01,14.26,14.46,8882000,AAL
2013-02-12,14.45,14.51,14.1,14.27,8126000,AAL
2013-02-13,14.3,14.94,14.25,14.66,10259500,AAL

Each line contains the date of the stock, the open value of the day, the high and the low values, the close value, the volume of the stocks exchanged, and finally the stock name — for example, American Airlines Group Inc. (AAL).

The mapping associated to the stocks index is as follows:

{
  "stocks": {
    "mappings": {
      "properties": {
        "close": {"type":"float"},
        "date" : {"type":"date"},
        "high" : {"type":"float"},
        "low"  : {"type":"float"},
        "name" : {
          "type": "text",
          "fields": {
            "keyword":{"type":"keyword", "ignore_above":256}
          }
        },
        "open"  : {"type":"float"},
        "volume": {"type":"long"}
      }
    }
  }
}

We can use the GET /stocks/_mapping API to retrieve the mapping from Elasticsearch.

[Related article: ChatGPT and Elasticsearch: OpenAI meets private data]

Let's build a prompt to find out

In order to translate a query expressed in human language to Elasticsearch DSL, we need to find the right prompt to give to ChatGPT. This is the most difficult part of the process: to actually program ChatGPT using the correct question format (in other words, the right prompt).

After some iterations, we ended up with the following prompt that seems to work quite well:

Given the mapping delimited by triple backticks ```{mapping}``` translate the text delimited by triple quotes in a valid Elasticsearch DSL query """{query}""". Give me only the json code part of the answer. Compress the json output removing spaces.

The value {mapping} and {query} in the prompt are two placeholders to be replaced with the mapping json string (for example, returned by the GET /stocks/_mapping in the previous example) and the query expressed in human language (for example: Return the first 10 documents of 2017).

Of course, ChatGPT is limited and in some cases it won’t be able to answer a question. We found that, most of the time, this happens because the sentence used in the prompt is too general or ambiguous. To solve this situation, we need to enhance the prompt using more details. This process is called iteration, and it requires multiple steps to define the proper sentence to be used.

If you want to try out how ChatGPT can translate a search sentence in an Elasticsearch DSL query (or even SQL), you can use dsltranslate.com.

Putting it all together

Using the ChatGPT API offered by OpenAI and the Elasticsearch API for mapping and search, we put it all together in an experimental library for PHP.

This library exposes a search() function with the following API:

search(string $index, string $prompt, bool $cache = true)

Where $index is the index name to be used, $prompt is the query expressed in human language and $bool is an optional parameter for using a cache (enabled by default).

The process of this function is reported in the following diagram:

elasticsearch openai diagram

The inputs are index and prompt (on the left). The index is used to retrieve the mapping from Elasticsearch (using the get mapping API). The result is a mapping in JSON that is used to build the query string to send to ChatGPT using the following API code. We used the gpt-3.5-turbo model of OpenAI that is able to translate in code.

The result from ChatGPT contains an Elasticsearch DSL query that we use to query Elasticsearch. The result is then returned to the user. To query Elasticsearch, we utilized the official elastic/elasticsearch-php client.

To optimize response time and reduce the cost of using the ChatGPT API, we used a simple caching system based on files. We used a cache to:

  • Store the mapping JSON returned by Elasticsearch: We store this JSON in a file named after the index. This allows us to retrieve the mapping information without making additional calls to Elasticsearch.
  • Store the Elasticsearch DSL generated by ChatGPT: To cache the generated Elasticsearch DSL, we named the cache file using the hash (MD5) of the prompt used. This approach enables us to reuse previously generated Elasticsearch DSL for the same query, eliminating the need to call the ChatGPT API again.

We also added the possibility to retrieve the Elasticsearch DSL programmatically using the getLastQuery() function.

Running the experiment with financial data

We used Elastic Cloud to store the stocks value reported here. In particular, we used a simple bulk script to read the stocks file in CSV and send it to Elasticsearch using the bulk API.

For more details on how to set up an Elastic Cloud and retrieve the API key, read the documentation.

Once we stored the stocks index, we used a simple PHP script for testing some query expressed in English. The script we used is examples/test.php.

To execute this examples/test.php script, we need to set three environment variables:

  • OPENAI_API_KEY: the API key of OpenAI
  • ELASTIC_CLOUD_ENDPOINT: the url of the Elasticsearch instance
  • ELASTIC_CLOUD_API_KEY: the API key of Elastic Cloud

Using the stocks mapping, we tested the following queries recording all the Elasticsearch DSL responses:

Query: Return the first 10 documents of 2017

Result:
{
  "query":{
    "range":{
      "date":{
        "gte":"2017-01-01",
        "lte":"2017-12-31"
      }
    }
  },
  "size":10
}
Query: Return the first 30 names of all the different stock names

Result:
{
  "size":0,
  "aggs":{
    "unique_names":{
      "terms":{
        "field":"name.keyword",
        "size":30
      }
    }
  }
}
Query: Return the max value of the field "high" for each stock in 2015

Result: 
{
  "aggs":{
    "stocks":{
      "terms":{"field":"name.keyword","size":100},
      "aggs":{
        "max_high":{"max":{"field":"high"}}
      }
    }
  },
  "query":{
    "bool":{
      "must":[{
        "range":{
          "date":{
            "gte":"2015-01-01",
            "lt":"2016-01-01"
          }
        }
      }]
    }
  }
}
Query: Return the average value of the field "high" for each stock in 2015

Result:
{
  "size":0,
  "aggs":{
    "stocks":{
      "terms":{
        "field":"name.keyword",
        "size":10000
      },
      "aggs":{
        "2015":{
          "filter":{
            "range":{
              "date":{
                "gte":"2015-01-01",
                "lt":"2016-01-01"
              }
            }
          },
          "aggs":{
            "avg_high":{
              "avg":{
                "field":"high"
              }
            }
          }
        }
      }
    }
  }
}
Query: Return the max value of the field "high" for all the documents with name MON in 2014

Result:

{
  "query":{
    "bool":{
      "must":[
        {
          "match":{
            "name.keyword":"MON"
          }
        },
        {
          "range":{
            "date":{
              "gte":"2014-01-01",
              "lt":"2015-01-01"
            }
          }
        }
      ]
    }
  },
  "size":0,
  "aggs":{
    "max_high":{
      "max":{
        "field":"high"
      }
    }
  }
}
Query: Return the documents that have the difference between close and open fields > 20

Response:
{
  "query":{
    "bool":{
      "must":[
        {
          "script":{
            "script":{
              "lang":"painless",
              "source":"doc['close'].value - doc['open'].value > 20"
            }
          }
        }
      ]
    }
  }
}

As you can see, the results are pretty good. The last one about the difference between closed and open fields was quite impressive! 

All the requests have been translated in a valid Elasticsearch DSL query that is correct according to the question expressed in natural language.

Use the language you speak!

A very nice feature of ChatGPT is the ability to specify questions in different languages.

That means you can use this library and specify the query in different natural languages, like Italian, Spanish, French, German, and so on.

Here is an example:

# English
$result = $chatGPT->search('stocks', 'Return the first 10 documents of 2017');
# Italian
$result = $chatGPT->search('stocks', 'Restituisci i primi 10 documenti del 2017');
# Spanish
$result = $chatGPT->search('stocks', 'Devuelve los 10 primeros documentos de 2017');
# French
$result = $chatGPT->search('stocks', 'Retourner les 10 premiers documents de 2017');
# German
$result = $chatGPT->search('stocks', 'Senden Sie die ersten 10 Dokumente des Jahres 2017 zurück');

All the previous search have the same results producing the following Elasticsearch query (more or less):

{"size":10,"query":{"range":{"date":{"gte":"2017-01-01","lt":"2018-01-01"}}}}

Important: ChatGPT is an LLM that has been optimized for English, which means the best results are obtained using queries entered in English.

Limitations of LLMs

Unfortunately, ChatGPT and LLMs in general are not capable of verifying the correctness of the answer from a semantic point of view. They give answers that look right from a statistical point of view. This means, we cannot test if the Elasticsearch DSL query generated by ChatGPT is the right translation of the query in natural language.

Of course, this is a big limitation at the moment. In some other use cases, like mathematical operations, we can solve the correctness problem using an external plugin, like the Wolfram Plugin of ChatGPT. In this case, the result of ChatGPT uses the Wolfram engine that checks the correctness of the response, using a mathematical symbolic model.

Apart from the correctness limitation, which implies we should always check ChatGPT’s answers, there are also limitations to the ability to translate a human sentence in an Elasticsearch DSL query.

For instance, using the previous stocks data set if we ask something as follows:

Query: Return the first 10 documents of 2017 and 2015

Result:
{
  "from":0,
  "size":10,
  "query":{
    "bool":{
      "must":[
        {
          "range":{
            "date":{
              "gte":"2015-01-01",
              "lte":"2015-12-31",
              "format":"yyyy"
            }
          }
        },
        {
          "range":{
            "date":{
              "gte":"2017-01-01",
              "lte":"2017-12-31",
              "format":"yyyy"
            }
          }
        }
      ]
    }
  }
}

The DSL query generated by ChatGPT is not valid producing this Elasticsearch error:

Failed to parse date field [2015-01-01] with format [yyyy].

If we rephrase the sentence using more specific information, removing the apparent ambiguity about the date format, we can retrieve the correct answer, as follows:

Query: Return the first 10 documents with year 2017 and 2015 in "date" field

Result:
{
  "size":10,
  "query":{
    "bool":{
      "should":[
        {
          "term":{
            "date":"2017"
          }
        },
        {
          "term":{
            "date":"2015"
          }
        }
      ]
    }
  }
}

Basically, the sentence must be expressed using a description of how the Elasticsearch DSL should be rather than a real human sentence.

Wrapping up

In this post, we presented an experimental use case of ChatGPT for translating natural language search sentences into Elasticsearch DSL queries. We developed a simple library in PHP for using the OpenAI API to translate the query under the hood, providing also a caching system.

The results of the experiment are promising, even with the limitation on the correctness of the answer. That said, we will definitely investigate further the possibility to query Elasticsearch in natural language using ChatGPT, as well as other LLM models that are becoming more and more popular.

Learn more about the possibilities with Elasticsearch and AI.



In this blog post, we may have used third party generative AI tools, which are owned and operated by their respective owners. Elastic does not have any control over the third party tools and we have no responsibility or liability for their content, operation or use, nor for any loss or damage that may arise from your use of such tools. Please exercise caution when using AI tools with personal, sensitive or confidential information. Any data you submit may be used for AI training or other purposes. There is no guarantee that information you provide will be kept secure or confidential. You should familiarize yourself with the privacy practices and terms of use of any generative AI tools prior to use.  

Elastic, Elasticsearch and associated marks are trademarks, logos or registered trademarks of Elasticsearch N.V. in the United States and other countries. All other company and product names are trademarks, logos or registered trademarks of their respective owners.

Sign up for Elastic Cloud free trial

Spin up a fully loaded deployment on the cloud provider you choose. As the company behind Elasticsearch, we bring our features and support to your Elastic clusters in the cloud.

Start free trial