[role="xpack"]
[testenv="basic"]
[[sql-data-types]]
== Data Types

Most of {es} <<mapping-types, data types>> are available in {es-sql}, as indicated below:

[cols="^,^,^",options="header"]

|===
| {es} type | SQL type | SQL precision

3+h| Core types

| <<null-value, `null`>>      | `null`      | 0
| <<boolean, `boolean`>>      | `boolean`   | 1
| <<number, `byte`>>          | `tinyint`   | 3
| <<number, `short`>>         | `smallint`  | 5
| <<number, `integer`>>       | `integer`   | 10
| <<number, `long`>>          | `bigint`    | 19
| <<number, `double`>>        | `double`    | 15
| <<number, `float`>>         | `real`      | 7
| <<number, `half_float`>>    | `float`     | 16
| <<number, `scaled_float`>>  | `float`     | 19
| <<keyword, `keyword`>>      | `varchar`   | based on <<ignore-above>>
| <<text, `text`>>            | `varchar`   | 2,147,483,647
| <<binary, `binary`>>        | `varbinary` | 2,147,483,647
| <<date, `date`>>            | `timestamp` | 24

3+h| Complex types

| <<object, `object`>>         | `struct`    | 0
| <<nested, `nested`>>         | `struct`    | 0

3+h| Unsupported types

| _types not mentioned above_  | `unsupported`| 0

|===

Obviously, not all types in {es} have an equivalent in SQL and vice-versa hence why, {es-sql}
uses the data type _particularities_ of the former over the latter as ultimately {es} is the backing store.


[[sql-multi-field]]
[float]
=== SQL and multi-fields

A core concept in {es} is that of an `analyzed` field, that is a full-text value that is interpreted in order
to be effectively indexed. These fields are of type <<text, `text`>> and are not used for sorting or aggregations as their actual value depends on the <<analyzer, `analyzer`>> used hence why {es} also offers the <<keyword, `keyword`>> type for storing the _exact_
value.

In most case, and the default actually, is to use both types when for strings which {es} supports through <<multi-fields, multi fields>>, that is the ability to index the same string in multiple ways; for example index it both as `text` for search but also as `keyword` for sorting and aggregations.

As SQL requires exact values, when encountering a `text` field {es-sql} will search for an exact multi-field that it can use for comparisons, sorting and aggregations.
To do that, it will search for the first `keyword` that it can find that is _not_ normalized and use that as the original field _exact_ value.

Consider the following `string` mapping:

[source, js]
----
{
    "first_name" : {
        "type" : "text",
        "fields" : {
            "raw" : {
                "type" : "keyword"
            }
        }
    }
}
----
// NOTCONSOLE

The following SQL query:

[source, sql]
----
SELECT first_name FROM index WHERE first_name = 'John'
----

is identical to:

[source, sql]
----
SELECT first_name FROM index WHERE first_name.raw = 'John'
----

as {es-sql} automatically _picks_ up the `raw` multi-field from `raw` for exact matching.

