Elasticsearch, Elasticsearch SQL, SHOW COLUMNS or DESCRIBE - is there a posibility to filter the output

Question

I have simple elastic SQL query like this:

GET /_sql?format=txt
{
  "query" :"""
    DESCRIBE "index_name"
  """
}

and it works, and the output is like this:

    column                      | type          | mapping    
-----------------------------------------------------------
column_name1                    | STRUCT        | object         
column_name1.Id                 | VARCHAR       | text           
column_name1.Id.keyword         | VARCHAR       | keyword

Is there a possibility to the prepare above query using filter or where, for example something like this:

GET /_sql?format=txt
{
  "query":"""
  DESCRIBE "index_name"
  """,
  "filter": {"terms": {"type.keyword": ["STRUCT"]}}
}

or

GET /_sql?format=txt
{
  "query":"""
  DESCRIBE "index_name"
  WHERE "type" = 'STRUCT'
  """
}

buddemat · Answer 1 · 2023-02-10T12:57:15.977

That is not possible, no.

While the DESCRIBE sql command seems to return tabular data, it is not a query and it does not support WHERE clauses or can be used within a SELECT statement. That is actually not specific to Elasticsearch, but the same in RDBMs.

The same apparently is true for the Elasticsearch filter clause. This again will work with SELECT SQL statements, but with DESCRIBE or SHOW COLUMNS - while not producing an error - it simply will have no effect on the results.

In "real" SQL, you could work around this by querying information_schema.COLUMNS, but that is not an option in Elasticsearch.

Elasticsearch, Elasticsearch SQL, SHOW COLUMNS or DESCRIBE - is there a posibility to filter the output

1 Answers1