Why aren't "flattened" fields usable via SQL?

[Jamshid]

Are flattened type fields really not usable with SQL queries? They're not mentioned in:

SQL Limitations | Elasticsearch Guide [7.15] | Elastic

I have an index with these fields:

...
    "mappings" : {
      "properties" : {
         "name" : {
          "type" : "keyword",
         }
       "paths" : {
          "type" : "flattened"
        },

And a document like:

          "name" : "1/2/3/4/4.txt",
          "paths" : {
            "p1" : "1",
            "p2" : "2",
            "p3" : "3",
            "p4" : "4",
            "p5" : "4.txt"
          },

that I expected to be returned from:

{
  "query": "SELECT name,\"paths.p5\" FROM \"my-index\" where name like '1/%'"
}

but Elasticsearch 7.15.1 returns this error (I do have another field named path).

"verification_exception",
                "reason": "Found 1 problem\nline 1:13: Unknown column [paths.p4], did you mean [path]?"```

[Jamshid]

Anybody? I guess I should file at least a documentation bug at Issues · elastic/elasticsearch · GitHub ?

[Andrei_Stefan]

@Jamshid I'm sorry you have difficulties with the SQL endpoint. At the moment, flattened data types are not supported. We chose not to document this type of support in the "Limitations" page since it could go out of sync with the Elasticsearch supported data types whenever a new data type is added there.
Instead, we do mention the supported data types and list everything else in bulk as "unsupported" here.

If you'd like to have supported for flattened data types, please create an issue in github and we'll evaluate it. Thank you.