[FEATURE] Support non-keyword fields for text

[Yury-Fridlyand]

Is your feature request related to a problem?

Search doesn't work properly for fields of non-keyword data type.
Mapping

{
  "mappings": {
    "properties": {
      "numbers": {
        "type": "text",
        "fields": {
          "values": {
            "type": "integer"
          }
        }
      }
    }
  }
}

Create new index:

$ curl -s -H 'Content-Type: application/json' -XPUT "http://localhost:9200/tmp?pretty" -d '{"mappings": {"properties": {"numbers": {"type": "text", "fields": {"values": {"type": "integer"}}}}}}'
{
  "acknowledged" : true,
  "shards_acknowledged" : true,
  "index" : "tmp"
}

Add a doc:

$ curl -s -H 'Content-Type: application/json' -XPOST "http://localhost:9200/tmp/_doc?pretty" -d '{"numbers": [12, 20, 30]}'

Validate data:

$ curl -s -XGET "http://localhost:9200/tmp/_search?pretty" | jq .hits.hits

[
  {
    "_index": "tmp",
    "_id": "wNh6soQBeC3wK8rxBHNm",
    "_score": 1,
    "_source": {
      "numbers": [
        12,
        20,
        30
      ]
    }
  }
]

$ curl -s -H 'Content-Type: application/json' -XGET "http://localhost:9200/tmp/_search?pretty" -d '{"query": {"match_all": {}}, "fields": ["numbers", "numbers.values"], "_source": false}' | jq .hits.hits

[
  {
    "_index": "tmp",
    "_id": "wNh6soQBeC3wK8rxBHNm",
    "_score": 1,
    "fields": {
      "numbers.values": [
        12,
        20,
        30
      ],
      "numbers": [
        "12",
        "20",
        "30"
      ]
    }
  }
]

Try an SQL query:

opensearchsql> select numbers from tmp;
fetched rows / total rows = 1/1
+-----------+
| numbers   |
|-----------|
| 12        |
+-----------+
opensearchsql> select numbers.values from tmp;
{'reason': 'Invalid SQL query', 'details': "can't resolve Symbol(namespace=FIELD_NAME, name=numbers.values) in type env", 'type': 'SemanticCheckException'}
opensearchsql> select * from tmp where numbers.values = 20;
{'reason': 'Invalid SQL query', 'details': "can't resolve Symbol(namespace=FIELD_NAME, name=numbers.values) in type env", 'type': 'SemanticCheckException'}

$ curl -s -XPOST http://localhost:9200/_plugins/_sql -H 'Content-Type: application/json' -d '{"query": "select numbers from tmp;"}'

{
  "schema": [
    {
      "name": "numbers",
      "type": "text"
    }
  ],
  "datarows": [
    [
      "12"
    ]
  ],
  "total": 1,
  "size": 1,
  "status": 200
}

What solution would you like?

Listed above queries should return something like this:

• 12 20 30
• 12, 20, 30
• "12 20 30"
• [12, 20, 30]
• ["12", "20", "30"]

What alternatives have you considered?

Add limitation section to documentation and return error on querying such kind of fields (aka columns).

Do you have any additional context?

Related to #794, #1038 and #1113.

[Yury-Fridlyand]

Mapping could be even more complex:

"fields": {
    "keyword": {
        "ignore_above": 128,
        "type": "keyword"
    },
    "float": {
        "ignore_malformed": true,
        "type": "float"
    }
}