# Elasticsearch-sql can't query unindexed field

**URL:** <https://discuss.elastic.co/t/elasticsearch-sql-cant-query-unindexed-field/251635>\
**Category:** Elasticsearch\
**Tags:** elastic-stack-sql\
**Created:** [October 10, 2020, 9:17am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cant-query-unindexed-field/251635 "2020-10-10T09:17:14Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![ITzhangqiang](https://avatars.discourse-cdn.com/v4/letter/i/2bfe46/32.png) [@ITzhangqiang](https://discuss.elastic.co/u/ITzhangqiang)\
**Post date:** [October 10, 2020, 9:17am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cant-query-unindexed-field/251635/1 "2020-10-10T09:17:14Z")

</div>

As we know , unindexed field will store in \_source field,so we can get those unindexed fields in \_source.But Elasticsearch-sql can't get unindexed field.

> GET /\_sql/translate  
> {  
> "query":"""  
> select \* from "view-laohu-sdk" limit 1  
> """  
> }  
> look the picture blow，“\_source”:false lead to we can't get unindexed field  
> ![image](https://us1.discourse-cdn.com/elastic/original/3X/2/0/20a81c08523fa946f643e5ea779fc06cc532c799.png)

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [October 10, 2020, 9:54am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cant-query-unindexed-field/251635/2 "2020-10-10T09:54:38Z")

</div>

That's probably true. If you don't store the content, the content is searchable but you can't retrieve it.

Any reason you are doing this?

---

<div class="post-metadata">

**Author:** ![ITzhangqiang](https://avatars.discourse-cdn.com/v4/letter/i/2bfe46/32.png) [@ITzhangqiang](https://discuss.elastic.co/u/ITzhangqiang)\
**Post date:** [October 12, 2020, 1:52am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cant-query-unindexed-field/251635/3 "2020-10-12T01:52:29Z")

</div>

Please look at the problem carefully，I unindexed these fields and these fields stored in \_source  
default. But I can't retrieve these fields when I use elasticsearch-sql.  
 ![image](https://us1.discourse-cdn.com/elastic/original/3X/c/5/c5bbb69638212b80fccff7456140fb56293a2cc3.png)

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [October 12, 2020, 5:54am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cant-query-unindexed-field/251635/4 "2020-10-12T05:54:32Z")

</div>

Could you provide a full recreation script as described in [About the Elasticsearch category](https://discuss.elastic.co/t/about-the-elasticsearch-category/21). It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script is something anyone can copy and paste in Kibana dev console, click on the run button to reproduce your use case. It will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [October 12, 2020, 5:56am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cant-query-unindexed-field/251635/5 "2020-10-12T05:56:59Z")

</div>

Reading unindexed fields has a significant performance impact and can be very slow and scale badly. I wonder if it is therefore disabled within the SQL interface.

---

<div class="post-metadata">

**Author:** ![ITzhangqiang](https://avatars.discourse-cdn.com/v4/letter/i/2bfe46/32.png) [@ITzhangqiang](https://discuss.elastic.co/u/ITzhangqiang)\
**Post date:** [October 12, 2020, 6:10am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cant-query-unindexed-field/251635/6 "2020-10-12T06:10:19Z")

</div>

Maybe,but this situation is very widely used .If I use SQL ，I must index all fields although there are some fields That I don't want to do search. I think index all fields has a more significant performance impact.

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [October 12, 2020, 6:20am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cant-query-unindexed-field/251635/7 "2020-10-12T06:20:28Z")

</div>

Elasticsearch is not a relational database and does not store non-indexed data in a columnar format. In order to access data that is not indexed it needs to retrieve and parse the full source document, which is a lot slower than access indexed fields. This is why I suspect it might have been disabled on purpose, but someone from Elastic can probably shed some light on this.

---

<div class="post-metadata">

**Author:** ![ITzhangqiang](https://avatars.discourse-cdn.com/v4/letter/i/2bfe46/32.png) [@ITzhangqiang](https://discuss.elastic.co/u/ITzhangqiang)\
**Post date:** [October 12, 2020, 6:26am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cant-query-unindexed-field/251635/8 "2020-10-12T06:26:15Z")

</div>

I don't think so .If you retrieve many fields, from “\_source” is faster than from docvalue.This is why Elasticsearch stored field in “\_source” default.

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [October 12, 2020, 6:28am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cant-query-unindexed-field/251635/9 "2020-10-12T06:28:35Z")

</div>

That does depend on the number of fields returned and the size of the documents.

---

<div class="post-metadata">

**Author:** ![ITzhangqiang](https://avatars.discourse-cdn.com/v4/letter/i/2bfe46/32.png) [@ITzhangqiang](https://discuss.elastic.co/u/ITzhangqiang)\
**Post date:** [October 12, 2020, 6:34am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cant-query-unindexed-field/251635/10 "2020-10-12T06:34:32Z")

</div>

However, SQL Language can control what fields are returned ,such as “select field1” or “select \*”，I think SQL interface should be flexible, not banned altogether

---

<div class="post-metadata">

**Author:** ![bogdan.pintea](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bogdan.pintea/32/45740_2.png) [@bogdan.pintea](https://discuss.elastic.co/u/bogdan.pintea)\
**Post date:** [October 12, 2020, 8:48am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cant-query-unindexed-field/251635/11 "2020-10-12T08:48:05Z")

</div>

Hello @ITzhangqiang,

As detailed in [SQL Limitations](https://www.elastic.co/guide/en/elasticsearch/reference/7.9/sql-limitations.html#fields-from-source), most columns will now always be retrieved from `_source`, with some exceptions (such as the `@timestamp` field of type `date`, in your example) .

And in accordance to that, as an example: if you run something like `SELECT "@timestamp", bytes FROM kibana_sample_data_logs`, you will get a translation like:

```auto
 "_source" : {
    "includes" : [
      "bytes"
    ],
    "excludes" : []
 },
 "docvalue_fields" : [
    {
      "field" : "@timestamp",
      "format" : "epoch_millis"
    }
 ],

```

since `bytes` is a `long` field, and a corresponding answer:

```auto
        "_source" : {
          "bytes" : 3298
        },
        "fields" : {
          "@timestamp" : [
            "1533540155581"
          ]
        },

```

---

<div class="post-metadata">

**Author:** ![ITzhangqiang](https://avatars.discourse-cdn.com/v4/letter/i/2bfe46/32.png) [@ITzhangqiang](https://discuss.elastic.co/u/ITzhangqiang)\
**Post date:** [October 12, 2020, 8:58am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cant-query-unindexed-field/251635/12 "2020-10-12T08:58:48Z")

</div>

You may not understand my question,you can reproduce the problem by following the steps below :

```
> ①PUT test

```

> ```
> ②PUT test/_mapping
> {
> "dynamic": "false",
> "properties": {
> "field1": {
> "type": "keyword"
> }
> }
> }
> 
> ③PUT test/_doc/1
> {
> "field1":"111",
> "field2":"222"
> }
> ④GET test/_search
> 
> ⑤GET /_sql?format=json
> {
> "query":"""
> select * from "test"
> """
> }
> 
> ```

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/8/2/822f60e5fff8a843ee5fd011b37367a9eebc9a59.png)

---

<div class="post-metadata">

**Author:** ![bogdan.pintea](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bogdan.pintea/32/45740_2.png) [@bogdan.pintea](https://discuss.elastic.co/u/bogdan.pintea)\
**Post date:** [October 12, 2020, 9:33am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cant-query-unindexed-field/251635/13 "2020-10-12T09:33:06Z")

</div>

I see.

As you can read in [Dynamic field mapping](https://www.elastic.co/guide/en/elasticsearch/reference/current/dynamic-field-mapping.html) or the [dynamic mapping parameters](https://www.elastic.co/guide/en/elasticsearch/reference/current/dynamic.html#dynamic), the "new" `dynamic2` field `will not be indexed so will not be searchable`.

So, returning the entire index as in your example will dump the \_source, but:

```auto
POST test/_search
{
  "query": {
    "match": {
      "field2": "222"
    }
  }
}

```

will return nothing.

And to show where's the problem: with your example index, `GET test/_field_caps?fields=field*` will only return:

```auto
{
  "indices" : [
    "test"
  ],
  "fields" : {
    "field1" : {
      "keyword" : {
        "type" : "keyword",
        "searchable" : true,
        "aggregatable" : true
      }
    }
  }
}

```

ES/SQL queries will also not run through all stored docs of an index to match what non searchable but \_source stored fields might be relevant. Besides performance, since this field is not indexed, how could SQL know the type of this field and how to handle it; ex.: should `field2+1=223` be allowed, or rather `CHAR_LENGTH(field2)=3`?

---

<div class="post-metadata">

**Author:** ![ITzhangqiang](https://avatars.discourse-cdn.com/v4/letter/i/2bfe46/32.png) [@ITzhangqiang](https://discuss.elastic.co/u/ITzhangqiang)\
**Post date:** [October 12, 2020, 9:45am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cant-query-unindexed-field/251635/14 "2020-10-12T09:45:18Z")

</div>

I see.  
But if a index has 50 fields（only 10 fields need search，so we just index these 10 fields).  
When I use SQL, I can't get the other 30 unindexed fields back,I think this is unreasonable.  
I must index all fields,this is a waste of storage.

---

<div class="post-metadata">

**Author:** ![ITzhangqiang](https://avatars.discourse-cdn.com/v4/letter/i/2bfe46/32.png) [@ITzhangqiang](https://discuss.elastic.co/u/ITzhangqiang)\
**Post date:** [October 12, 2020, 9:53am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cant-query-unindexed-field/251635/15 "2020-10-12T09:53:12Z")

</div>

"how could SQL know the type of this field and how to handle it; ex.: should `field2+1=223` be allowed, or rather `CHAR_LENGTH(field2)=3` ?"

First,if a field is unindexed,user will not do that like `field2+1=223`. If they do that ,SQL can throw Exception to prompt them the wrong usage.  
In addition, fetching unindexed fields is a very common use case,if SQL don't support this,That would be a great pity.

---

<div class="post-metadata">

**Author:** ![bogdan.pintea](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bogdan.pintea/32/45740_2.png) [@bogdan.pintea](https://discuss.elastic.co/u/bogdan.pintea)\
**Post date:** [October 12, 2020, 10:47am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cant-query-unindexed-field/251635/16 "2020-10-12T10:47:53Z")

</div>

> [@ITzhangqiang](#):
>
> But if a index has 50 fields（only 10 fields need search，so we just index these 10 fields).  
> When I use SQL, I can't get the other 30 unindexed fields back,I think this is unreasonable.

If you know in advance your index fields, you can add these to the mapping as stored, but not indexed:

```auto
PUT test/_mapping
{
  "dynamic": "false",
  "properties": {
    "field1": {
      "type": "keyword"
    },
    "field2": {
      "type":"keyword",
      "index": false,
      "store": true
    }
  }
}

```

That will return your `field2` in `SELECT * FROM cust` (but you of course won't be able to search by it).

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [October 12, 2020, 10:55am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cant-query-unindexed-field/251635/17 "2020-10-12T10:55:54Z")

</div>

> [@ITzhangqiang](#):
>
> I must index all fields,this is a waste of storage.

How much?  
Is that a real problem? I mean that in some corner use cases that could be a problem but I have often seen engineers (like myself) trying to solve problems they don't have actually.

---

<div class="post-metadata">

**Author:** ![ITzhangqiang](https://avatars.discourse-cdn.com/v4/letter/i/2bfe46/32.png) [@ITzhangqiang](https://discuss.elastic.co/u/ITzhangqiang)\
**Post date:** [October 13, 2020, 2:51am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cant-query-unindexed-field/251635/18 "2020-10-13T02:51:15Z")

</div>

In my usage scenario,a index have many fields that don't need to search.If these fields set "index": false,"store": true ,when I use "select \* from xx",many fields retrieve from 'doc\_value' not '\_source', this should have a significant impact on performance.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [October 13, 2020, 9:22am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cant-query-unindexed-field/251635/19 "2020-10-13T09:22:57Z")

</div>

> [@ITzhangqiang](#):
>
> this should have a significant impact on performance.

Just to be sure. What is the impact? Did you measure it?

---

<div class="post-metadata">

**Author:** ![ITzhangqiang](https://avatars.discourse-cdn.com/v4/letter/i/2bfe46/32.png) [@ITzhangqiang](https://discuss.elastic.co/u/ITzhangqiang)\
**Post date:** [October 13, 2020, 10:00am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cant-query-unindexed-field/251635/20 "2020-10-13T10:00:09Z")

</div>

????

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/9/8/987f44e0e322cfc404b2048b5a8a6c53e54708cd.png)

If an index has thousands of fields, do you think it is faster to fetch all the fields from \_source or from DocValue.  
Obviously, from \_source is faster 🤣

[Next page](https://discuss.elastic.co/t/elasticsearch-sql-cant-query-unindexed-field/251635.md?page=2)