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

**URL:** <https://discuss.elastic.co/t/why-arent-flattened-fields-usable-via-sql/288520>\
**Category:** Elasticsearch\
**Created:** [November 5, 2021, 7:06pm UTC](https://discuss.elastic.co/t/why-arent-flattened-fields-usable-via-sql/288520 "2021-11-05T19:06:28Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jamshid](https://avatars.discourse-cdn.com/v4/letter/j/ecd19e/32.png) [@Jamshid](https://discuss.elastic.co/u/Jamshid)\
**Post date:** [November 5, 2021, 7:06pm UTC](https://discuss.elastic.co/t/why-arent-flattened-fields-usable-via-sql/288520/1 "2021-11-05T19:06:28Z")

</div>

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

> **[SQL Limitations | Elasticsearch Guide \[7.15\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-limitations.html)**

I have an index with these fields:

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

```

And a document like:

```auto
          "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:

```auto
{
  "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`).

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

---

<div class="post-metadata">

**Author:** ![Jamshid](https://avatars.discourse-cdn.com/v4/letter/j/ecd19e/32.png) [@Jamshid](https://discuss.elastic.co/u/Jamshid)\
**Post date:** [November 15, 2021, 9:54pm UTC](https://discuss.elastic.co/t/why-arent-flattened-fields-usable-via-sql/288520/2 "2021-11-15T21:54:44Z")

</div>

Anybody? I guess I should file at least a documentation bug at [Issues · elastic/elasticsearch · GitHub](https://github.com/elastic/elasticsearch/issues) ?

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [November 17, 2021, 10:31am UTC](https://discuss.elastic.co/t/why-arent-flattened-fields-usable-via-sql/288520/3 "2021-11-17T10:31:06Z")

</div>

@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](https://www.elastic.co/guide/en/elasticsearch/reference/7.16/sql-data-types.html#sql-data-types).

If you'd like to have supported for `flattened` data types, please create an issue in [github](https://github.com/elastic/elasticsearch/issues) and we'll evaluate it. Thank you.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [December 15, 2021, 10:31am UTC](https://discuss.elastic.co/t/why-arent-flattened-fields-usable-via-sql/288520/4 "2021-12-15T10:31:54Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.