Elasticsearchを使用してChatGPTへのプロンプトを自然言語で用意する方法

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

Copy

{
  "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"}
      }
    }
  }
}

Copy

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.

Copy

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

Copy

Query: Return the first 10 documents of 2017

Result:
{
  "query":{
    "range":{
      "date":{
        "gte":"2017-01-01",
        "lte":"2017-12-31"
      }
    }
  },
  "size":10
}

Copy

Query: Return the first 30 names of all the different stock names

Result:
{
  "size":0,
  "aggs":{
    "unique_names":{
      "terms":{
        "field":"name.keyword",
        "size":30
      }
    }
  }
}

Copy

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"
          }
        }
      }]
    }
  }
}

Copy

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"
              }
            }
          }
        }
      }
    }
  }
}

Copy

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"
      }
    }
  }
}

Copy

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"
            }
          }
        }
      ]
    }
  }
}

Copy

# 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');

Copy

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

Copy

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"
            }
          }
        }
      ]
    }
  }
}

Copy

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"
          }
        }
      ]
    }
  }
}

Copy