ES|QL type conversion functions

ES|QL supports these type conversion functions:

• TO_BOOLEAN
• TO_CARTESIANPOINT
• TO_CARTESIANSHAPE
• [preview] TO_DATEPERIOD
• TO_DATETIME
• TO_DATE_NANOS
• TO_DEGREES
• TO_DOUBLE
• TO_GEOPOINT
• TO_GEOSHAPE
• TO_INTEGER
• TO_IP
• TO_LONG
• TO_RADIANS
• TO_STRING
• [preview] TO_TIMEDURATION
• [preview] TO_UNSIGNED_LONG
• TO_VERSION

Syntax

×

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input value to a boolean value. A string value of true will be case-insensitive converted to the Boolean true. For anything else, including the empty string, the function will return false. The numerical value of 0 will be converted to false, anything else will be converted to true.

Supported types

Example

ROW str = ["true", "TRuE", "false", "", "yes", "1"]
| EVAL bool = TO_BOOLEAN(str)

Syntax

×

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input value to a cartesian_point value. A string will only be successfully converted if it respects the WKT Point format.

Supported types

Example

ROW wkt = ["POINT(4297.11 -1475.53)", "POINT(7580.93 2272.77)"]
| MV_EXPAND wkt
| EVAL pt = TO_CARTESIANPOINT(wkt)

Syntax

×

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input value to a cartesian_shape value. A string will only be successfully converted if it respects the WKT format.

Supported types

Example

ROW wkt = ["POINT(4297.11 -1475.53)", "POLYGON ((3339584.72 1118889.97, 4452779.63 4865942.27, 2226389.81 4865942.27, 1113194.90 2273030.92, 3339584.72 1118889.97))"]
| MV_EXPAND wkt
| EVAL geom = TO_CARTESIANSHAPE(wkt)

Syntax

×

Parameters

field

Input value. The input is a valid constant date period expression.

Description

Converts an input value into a date_period value.

Supported types

Example

ROW x = "2024-01-01"::datetime
| EVAL y = x + "3 DAYS"::date_period, z = x - TO_DATEPERIOD("3 days");

Syntax

×

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input value to a date value. A string will only be successfully converted if it’s respecting the format yyyy-MM-dd'T'HH:mm:ss.SSS'Z'. To convert dates in other formats, use DATE_PARSE.

Supported types

Examples

ROW string = ["1953-09-02T00:00:00.000Z", "1964-06-02T00:00:00.000Z", "1964-06-02 00:00:00"]
| EVAL datetime = TO_DATETIME(string)

Note that in this example, the last value in the source multi-valued field has not been converted. The reason being that if the date format is not respected, the conversion will result in a null value. When this happens a Warning header is added to the response. The header will provide information on the source of the failure:

"Line 1:112: evaluation of [TO_DATETIME(string)] failed, treating result as null. "Only first 20 failures recorded."

A following header will contain the failure reason and the offending value:

"java.lang.IllegalArgumentException: failed to parse date field [1964-06-02 00:00:00] with format [yyyy-MM-dd'T'HH:mm:ss.SSS'Z']"

If the input parameter is of a numeric type, its value will be interpreted as milliseconds since the Unix epoch. For example:

ROW int = [0, 1]
| EVAL dt = TO_DATETIME(int)

Syntax

×

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input to a nanosecond-resolution date value (aka date_nanos).

Supported types

Example

FROM date_nanos
| WHERE MV_MIN(nanos) < TO_DATE_NANOS("2023-10-23T12:27:28.948Z")
    AND millis > "2000-01-01"
| SORT nanos DESC

Syntax

×

Parameters

number

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts a number in radians to degrees.

Supported types

Example

ROW rad = [1.57, 3.14, 4.71]
| EVAL deg = TO_DEGREES(rad)

Syntax

×

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input value to a double value. If the input parameter is of a date type, its value will be interpreted as milliseconds since the Unix epoch, converted to double. Boolean true will be converted to double 1.0, false to 0.0.

Supported types

Example

ROW str1 = "5.20128E11", str2 = "foo"
| EVAL dbl = TO_DOUBLE("520128000000"), dbl1 = TO_DOUBLE(str1), dbl2 = TO_DOUBLE(str2)

Note that in this example, the last conversion of the string isn’t possible. When this happens, the result is a null value. In this case a Warning header is added to the response. The header will provide information on the source of the failure:

"Line 1:115: evaluation of [TO_DOUBLE(str2)] failed, treating result as null. Only first 20 failures recorded."

A following header will contain the failure reason and the offending value: "java.lang.NumberFormatException: For input string: "foo""

Syntax

×

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input value to a geo_point value. A string will only be successfully converted if it respects the WKT Point format.

Supported types

Example

ROW wkt = "POINT(42.97109630194 14.7552534413725)"
| EVAL pt = TO_GEOPOINT(wkt)

Syntax

×

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input value to a geo_shape value. A string will only be successfully converted if it respects the WKT format.

Supported types

Example

ROW wkt = "POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))"
| EVAL geom = TO_GEOSHAPE(wkt)

Syntax

×

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input value to an integer value. If the input parameter is of a date type, its value will be interpreted as milliseconds since the Unix epoch, converted to integer. Boolean true will be converted to integer 1, false to 0.

Supported types

Example

ROW long = [5013792, 2147483647, 501379200000]
| EVAL int = TO_INTEGER(long)

Note that in this example, the last value of the multi-valued field cannot be converted as an integer. When this happens, the result is a null value. In this case a Warning header is added to the response. The header will provide information on the source of the failure:

"Line 1:61: evaluation of [TO_INTEGER(long)] failed, treating result as null. Only first 20 failures recorded."

A following header will contain the failure reason and the offending value:

"org.elasticsearch.xpack.esql.core.InvalidArgumentException: [501379200000] out of [integer] range"

Syntax

×

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

options

(Optional) Additional options.

Description

Converts an input string to an IP value.

Supported types

Supported function named parameters

leading_zeros

(keyword) What to do with leading 0s in IPv4 addresses.

Examples

ROW str1 = "1.1.1.1", str2 = "foo"
| EVAL ip1 = TO_IP(str1), ip2 = TO_IP(str2)
| WHERE CIDR_MATCH(ip1, "1.0.0.0/8")

Note that in this example, the last conversion of the string isn’t possible. When this happens, the result is a null value. In this case a Warning header is added to the response. The header will provide information on the source of the failure:

"Line 1:68: evaluation of [TO_IP(str2)] failed, treating result as null. Only first 20 failures recorded."

A following header will contain the failure reason and the offending value:

"java.lang.IllegalArgumentException: 'foo' is not an IP string literal."

ROW s = "1.1.010.1" | EVAL ip = TO_IP(s, {"leading_zeros":"octal"})

Parse v4 addresses with leading zeros as octal. Like ping or ftp.

ROW s = "1.1.010.1" | EVAL ip = TO_IP(s, {"leading_zeros":"decimal"})

Parse v4 addresses with leading zeros as decimal. Java's InetAddress.getByName.

Syntax

×

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input value to a long value. If the input parameter is of a date type, its value will be interpreted as milliseconds since the Unix epoch, converted to long. Boolean true will be converted to long 1, false to 0.

Supported types

Example

ROW str1 = "2147483648", str2 = "2147483648.2", str3 = "foo"
| EVAL long1 = TO_LONG(str1), long2 = TO_LONG(str2), long3 = TO_LONG(str3)

Note that in this example, the last conversion of the string isn’t possible. When this happens, the result is a null value. In this case a Warning header is added to the response. The header will provide information on the source of the failure:

"Line 1:113: evaluation of [TO_LONG(str3)] failed, treating result as null. Only first 20 failures recorded."

A following header will contain the failure reason and the offending value:

"java.lang.NumberFormatException: For input string: "foo""

Syntax

×

Parameters

number

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts a number in degrees to radians.

Supported types

Example

ROW deg = [90.0, 180.0, 270.0]
| EVAL rad = TO_RADIANS(deg)

Syntax

×

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input value into a string.

Supported types

Examples

ROW a=10
| EVAL j = TO_STRING(a)

It also works fine on multivalued fields:

ROW a=[10, 9, 8]
| EVAL j = TO_STRING(a)

Syntax

×

Parameters

field

Input value. The input is a valid constant time duration expression.

Description

Converts an input value into a time_duration value.

Supported types

Example

ROW x = "2024-01-01"::datetime
| EVAL y = x + "3 hours"::time_duration, z = x - TO_TIMEDURATION("3 hours");

Syntax

×

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input value to an unsigned long value. If the input parameter is of a date type, its value will be interpreted as milliseconds since the Unix epoch, converted to unsigned long. Boolean true will be converted to unsigned long 1, false to 0.

Supported types

Example

ROW str1 = "2147483648", str2 = "2147483648.2", str3 = "foo"
| EVAL long1 = TO_UNSIGNED_LONG(str1), long2 = TO_ULONG(str2), long3 = TO_UL(str3)

Note that in this example, the last conversion of the string isn’t possible. When this happens, the result is a null value. In this case a Warning header is added to the response. The header will provide information on the source of the failure:

"Line 1:133: evaluation of [TO_UL(str3)] failed, treating result as null. Only first 20 failures recorded."

A following header will contain the failure reason and the offending value:

`"java.lang.NumberFormatException: Character f is neither a decimal digit number, decimal point,

• "nor "e" notation exponential mark."`

Syntax

×

Parameters

field

Input value. The input can be a single- or multi-valued column or an expression.

Description

Converts an input string to a version value.

Supported types

Example

ROW v = TO_VERSION("1.2.3")