A newer version of Hazelcast Platform is available.

View latest

Expressions, Functions and Operators

Hazelcast supports a subset of standard SQL expressions, functions, and operators as well as non-standard functions for special cases such as windowed aggregation.

For information about input and return data types, see Data Types.

Operators

Operators are used to evaluate and/or compare one or more operands. For example, in the expression (X + Y), the + operator evaluates the addition of the X and Y operands.

Hazelcast supports the following SQL operators.

Logical Operators

Hazelcast supports the AND, OR, and NOT logical operators. Logical operators allow only BOOL or NULL data types as input. The result can be TRUE, FALSE, or NULL:

Table 1. Truth table for AND/OR operators
X Y X AND Y X OR Y

TRUE

TRUE

TRUE

TRUE

TRUE

FALSE

FALSE

TRUE

TRUE

NULL

NULL

TRUE

FALSE

FALSE

FALSE

FALSE

FALSE

NULL

FALSE

NULL

NULL

NULL

NULL

NULL

Table 2. Truth table for the NOT operator
X NOT Y

TRUE

FALSE

FALSE

TRUE

NULL

NULL

Comparison Operators

Comparison operators compare two or more operands, and always return a BOOL. Comparisons require operands to be of comparable type.

Table 3. Descriptions of comparison operators
Operator name Syntax Description

Less Than

X < Y

Returns TRUE if X is less than Y.

Less Than or Equal To

X <= Y

Returns TRUE if X is less than or equal to Y.

Greater Than

X > Y

Returns TRUE if X is greater than Y.

Greater Than or Equal To

X >= Y

Returns TRUE if X is greater than or equal to Y.

Equal

X = Y

Returns TRUE if X is equal to Y.

Not Equal

X != Y

X <> Y

Returns TRUE if X is not equal to Y.

BETWEEN

X [NOT] BETWEEN [SYMMETRIC] Y AND Z

Returns TRUE if X is within the given range of Y and Z. The SYMMETRIC keyword allows Y and Z to be in any order, rather than Y being the lower bound and Z being the upper bound.

LIKE

X [NOT] LIKE Y [ESCAPE Z]

Returns TRUE if the string in the X operand matches the pattern specified by the Y operand. Expressions can contain the following special characters:

  • A percent sign (%): Matches any number of characters.

  • An underscore (_): Matches a single character.

To escape a special character, use the ESCAPE keyword followed by the escape character to use. For example 'text' LIKE '!ext' ESCAPE '!' returns FALSE because the character is escaped by the exclamation mark (!).

IN

EXISTS Operator

The EXISTS operator returns TRUE if a given subquery returns one or more records.

The NOT keyword inverts the result.

Syntax

The EXISTS operator supports the following syntax:

[NOT] EXISTS (<query>)

IS Operator

The IS operator returns TRUE or FALSE for a given condition. This operator never returns NULL.

The NOT keyword inverts the result.

Table 4. Descriptions of the IS operator syntax
Syntax Description

X IS [NOT] NULL

Returns TRUE if the X operand evaluates to NULL, and returns FALSE otherwise.

X IS [NOT] TRUE

Returns TRUE if the X operand evaluates to TRUE. Returns FALSE otherwise.

X IS [NOT] FALSE

Returns TRUE if the X operand evaluates to FALSE. Returns FALSE otherwise.

IN Operator

The IN operator allows you to check for a certain value in a given set of values, and returns TRUE if an equal value is found.

The NOT keyword inverts the result.

At the moment, Hazelcast does not support subqueries in IN clauses. For example, you cannot do SELECT column_names FROM table_name WHERE column_name IN (SELECT STATEMENT);

Syntax

The IN operator supports the following syntax:

<search_value> [NOT] IN (<value_set>)

UNION and UNION ALL Operators

The UNION operator is used to combine the result set of two or more SELECT statements, excluding any duplicate values.

The UNION ALL operator is used to combine the result set of two or more SELECT statements, including any duplicate values.

UNION ALL typically performs much better, because duplicate elimination is an expensive operation. Use UNION only if you actually need to remove duplicates.

Syntax

The UNION and UNION ALL Operators support the following syntax:

<query> UNION [ALL] <query>

Mathematical Operators

Mathematical operators perform operations on numbers.

Table 5. Descriptions of mathematical operators
Operator Name Syntax

Addition

X + Y

Subtraction

X - Y

Multiplication

X * Y

Division

X / Y

Conditional Expressions

Conditional expressions allow you to evaluate only certain output values, depending on given conditions.

CASE

The CASE expression evaluates the condition of each WHEN clause and returns the first result where the condition is TRUE. If all conditions are FALSE or NULL, the result of the ELSE clause is returned.

Each condition must be a boolean expression.

Syntax

The CASE expression has two forms:

CASE value
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  ELSE elseResult
END

This form for returns result1, when value = value1, result2 when value = value2 and elseResult, if value isn’t equal to any of the values in the WHEN clause.

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  ELSE elseResult
END

This form returns result1 when condition1 is TRUE, result2 when condition2 is TRUE and elseResult, if no condition evaluated to TRUE.

NULLIF

The NULLIF expression returns NULL if the two operands are equal and returns the first operand, if operands are not equal. The data type of the returned NULL value is the same as the X expression.

Syntax

NULLIF(X, Y)

Examples

sql> SELECT NULLIF('foo', 'bar');
+--------------------+
|EXPR$0              |
+--------------------+
|foo                 |
+--------------------+
1 row(s) selected
sql> SELECT NULLIF('foo', 'foo');
+--------------------+
|EXPR$0              |
+--------------------+
|NULL                |
+--------------------+

COALESCE

The COALESCE function returns the first non-null operand. If all operands are null, it returns NULL. Arguments to the right of the first non-null argument are not evaluated.

Syntax

COALESCE(X, Y, Z, ...)

Aggregate Functions

Aggregate functions perform calculations such as returning the mean of all data in a particular row.

Table 6. Descriptions of aggregate function
Function :: Returns Description

COUNT(*) :: BIGINT

Calculates the number of input rows.

COUNT(field) :: BIGINT

Calculates the number of input rows in which the field is not null.

COUNT(DISTINCT field) :: BIGINT

Calculates the number of distinct values of the given field (ignores the NULL value).

SUM(TINYINT | SMALLINT | INT) :: BIGINT, SUM(BIGINT | DECIMAL) :: DECIMAL, SUM(REAL) :: REAL, SUM(DOUBLE) :: DOUBLE

Calculates the sum of the non-null input values.

AVG(DECIMAL) :: DECIMAL, AVG(DOUBLE) :: DOUBLE

Calculates the mean of all the non-null input values.

MIN(any) :: same type as the input

Calculates the minimum of the non-null input values. Applicable also to OBJECT type, if the underlying value is java.lang.Comparable.

MAX(any) :: same type as the input

Calculates the maximum of the non-null input values. Applicable also to OBJECT type, if the underlying value is java.lang.Comparable.

You can use DISTINCT keyword with all aggregate functions. It causes that before calculating the aggregate, duplicates are removed from the set of input values. For example, SUM(DISTINCT) applied to input values 1, 1, 2 will produce 3, because it will add the 1 only once.

For examples of how to use aggregate functions, see the SELECT statement documentation.

Conversion Functions

Conversion functions allow you to convert the result type of one expression to another explicit type.

Table 7. Descriptions of conversion functions
Function Description Example Result

CAST(expression AS data_type)

Converts the result type of expression to data_type.

SELECT CAST('2020-06-02 17:00:53.110' AS TIMESTAMP)

2020-06-03T00:00:53.11

Date and Time Functions

Table 8. Descriptions of date functions
Function Description Example Result
`EXTRACT(<element> FROM <temporal_value>)

DOUBLE`

Returns the element date part from the date expression.

Supported elements: MILLENIUM, CENTURY, DECADE, YEAR, ISOYEAR, QUARTER, MONTH, WEEK, DAY, DOW, ISODOW, DOY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, EPOCH

Supported temporal types: DATE, TIME, TIMESTAMP, TIMESTAMP WITH TIME ZONE

EXTRACT(WEEK FROM date'2017-06-15')

24

`TO_TIMESTAMP_TZ(BIGINT)

TIMESTAMP WITH TIME ZONE`

Converts a BIGINT value to TIMESTAMP_WITH_TIMEZONE. The value is interpreted as number of seconds, milliseconds, microseconds or nanoseconds since the epoch, that is since 1970-01-01 00:00 UTC.

The actual time unit of the conversion is determined by the magnitude of the input value:

  • less than the number of milliseconds in a year: seconds

  • less than the number of microseconds in a year: milliseconds

  • less than the number of nanoseconds in a year: microseconds

  • otherwise: nanoseconds

This logic causes that any time value between years 1971 up to year 2968 is converted using the correct time unit. Negative values are always converted as seconds.

SELECT TO_TIMESTAMP_TZ(v) FROM TABLE (generate_series(1,3))

1970-01-01T03:00:01+03:00

1970-01-01T03:00:02+03:00

1970-01-01T03:00:03+03:00

TO_EPOCH_MILLIS(TIMESTAMP_WITH_TIMEZONE)

Converts TIMESTAMP_WITH_TIMEZONE values to an EPOCH value in milliseconds.

SELECT TO_EPOCH_MILLIS(date'2022-02-22')

1645484400000

File Table Functions

To execute an ad-hoc query against data in files you can use one of the following table functions:

  • csv_file

  • json_flat_file

  • avro_file

  • parquet_file

File table functions create a temporary mapping to a file, which is valid for the duration of the query. These functions accept the same options as those available for the file connector.

To configure the temporary mapping, you can use either positional arguments or named arguments:

Positional arguments in a file table function
SELECT * FROM TABLE(
  CSV_FILE('/path/to/directory', '*.csv', MAP['key', 'value'])
);
Named arguments in a file table function
SELECT * FROM TABLE(
  CSV_FILE(path => '/path/to/directory', options => MAP['key', 'value'])
);

JSON Functions

Hazelcast supports the following functions, which can retrieve JSON data.

JSON_QUERY

The JSON_QUERY() function extracts a JSON value from a JSON document or a JSON-formatted string that matches a given JsonPath expression.

Syntax

JSON_QUERY(jsonArg:{VARCHAR | JSON}, jsonPath:VARCHAR [<wrapperBehavior>] [<onClauseArg> ON ERROR] [<onClauseArg> ON EMPTY])` :: JSON
  • jsonArg: JSON value or a JSON-formatted string.

  • jsonPath: A JsonPath expression that identifies the data that you want to get from the jsonArg parameter.

  • wrapperBehavior: What to do with return results.

    • WITHOUT [ARRAY] WRAPPER (default): Up to one matched value is returned without wrapping in an array. Fails, if multiple values match.

    • WITH [CONDITIONAL] [ARRAY] WRAPPER: Returns a single match directly without wrapping. If there are multiple matches, they are returned as a JSON array.

    • WITH UNCONDITIONAL [ARRAY] WRAPPER: Always wrap matched values in a JSON array.

  • onClauseArg: A value to return in case of the given ON condition:

    • EMPTY ARRAY

    • EMPTY OBJECT

    • ERROR

    • NULL

Examples

JSON-formatted string
SELECT
  JSON_QUERY('{"company" : {"employees" : [{"id" : "1"}]}}', '$.company.employees[0]');

-- Result
-- {"id":"1"}
WITH CONDITIONAL WRAPPER
SELECT
  JSON_QUERY('[1,2,3]', '$[*]?(@ > 1)' WITH CONDITIONAL ARRAY WRAPPER);

-- Result
-- [2,3]
WITHOUT WRAPPER
SELECT
  JSON_QUERY('[1,2,3]', '$[*]?(@ > 1)' WITHOUT ARRAY WRAPPER);

-- Result
-- This example throws an error because you cannot return multiple values without an array wrapper.
WITH UNCONDITIONAL WRAPPER
SELECT
  JSON_QUERY('[1,"rainbow",3]', '$[1]' WITH UNCONDITIONAL ARRAY WRAPPER);

-- Result
-- ["rainbow"]

JSON_VALUE

The JSON_VALUE() function extracts a primitive value, such as a string, number, or boolean that matches a given JsonPath expression. This function returns NULL if a non-primitive value is matched, unless the ON ERROR behavior is changed.

Syntax

JSON_VALUE(jsonArg:{VARCHAR | JSON}, jsonPath:VARCHAR [RETURNING dataType] [<onClauseArg> ON ERROR] [<onClauseArg> ON EMPTY])` :: VARCHAR
  • jsonArg: JSON value or a JSON-formatted string

  • jsonPath: A JsonPath expression that identifies the data that you want to get from the jsonArg parameter.

  • RETURNING: Converts the result to the dataType (VARCHAR by default). If the value cannot be converted to the target type, throws an error.

  • onClauseArg: What to return in case of the ON condition:

    • DEFAULT <literal | column | parameter>

    • ERROR

    • NULL

Examples

JSON-formatted string
SELECT
  JSON_VALUE('{"company" : {"employees" : [{"id" : "1","name":"jake"}]}}', '$.company.employees[0].id');

-- Returns
-- 1 (as a VARCHAR)

JSON_ARRAY

The JSON_ARRAY() function returns a JSON array from a list of input data.

Syntax

JSON_ARRAY([columnOrParameterOrLiteral:ANY], [...more columns/parameters/literals:ANY] [{ABSENT|NULL} ON NULL]) :: JSON
  • columnOrParameterOrLiteral: A list of input data.

  • ON NULL: What to do with null values:

    • ABSENT ON NULL (default): Do not include NULL values in the array.

    • NULL ON NULL: Include NULL values in the array.

Examples

SELECT
  JSON_ARRAY(1, null, 3);

-- Result
-- [1,3]

JSON_OBJECT

The JSON_OBJECT() function returns a JSON object from the given key/value pairs.

Syntax

JSON_OBJECT([key : value] [, ...] [{ABSENT|NULL} ON NULL]) :: JSON

Or

JSON_OBJECT([[KEY] key VALUE value] [{ABSENT|NULL} ON NULL]) :: JSON
  • key: A name for the key, must be a VARCHAR

  • value: A value for the key, can be any type.

  • ON NULL: What to do with NULL values.

    • NULL ON NULL (default): Include NULL values in the array.

    • ABSENT ON NULL: Do not include NULL values in the array.

Examples

SELECT JSON_OBJECT(KEY 'id' VALUE 1, 'name' VALUE null ABSENT ON NULL)

-- Result
-- {"id": 1}
SELECT JSON_OBJECT('id': 1, 'name': 'jake')

-- Result
-- {"id": 1, "name":"jake"}

JSON_ARRAYAGG

The JSON_ARRAYAGG() returns a JSON array containing an element for each value in a given set of SQL values. It takes as its input a column of SQL expressions, converts each expression to a JSON value, and returns a single JSON array that contains those JSON values.

Syntax

JSON_ARRAY(value [ORDER BY value {ASC|DESC}] [{ABSENT|NULL} ON NULL]) :: JSON
  • value: A value for the key, can be any type.

  • ON NULL: What to do with null values:

    • ABSENT ON NULL (default): Do not include NULL values in the array.

    • NULL ON NULL: Include NULL values in the array.

Examples

Assuming you have the following table:

name, number
--------
Alice, 1
Bob, 2
Alice, 3
Bob, 6
null, 7
SELECT name,  JSON_ARRAYAGG(number ORDER BY number ASC) arr_no
FROM test GROUP BY name

-- Result
-- name, arr_no
-- ------------
-- Alice, [1, 3]
-- Bob, [2, 6]
-- null, [7]

JSON_OBJECTAGG

The JSON_OBJECTAGG() function constructs an object member for each key-value pair and returns a single JSON object that contains those object members. It takes as its input a property key-value pair. Typically, the property key, the property value, or both are columns of SQL expressions.

Syntax

JSON_OBJECTAGG([key : value] [, ...] [{ABSENT|NULL} ON NULL]) :: JSON

Or

JSON_OBJECTAGG([[KEY] key VALUE value] [{ABSENT|NULL} ON NULL]) :: JSON
  • key: A name for the key, must be a VARCHAR

  • value: A value for the key, can be any type.

  • ON NULL: What to do with NULL values.

    • NULL ON NULL (default): Include `NULL values in the array.

    • ABSENT ON NULL: Do not include NULL values in the array.

Examples

SELECT JSON_OBJECTAGG(KEY 'name' VALUE age) "Names"
  FROM employees
  WHERE age <= 30;

--Result
--{"Mary":28,"John":29,"Jake":27}

Mathematical Functions

Table 9. Descriptions of mathematical functions
Function Description Example Result

ABS(number)

Absolute value of the argument

ABS(-5)

5

CBRT(number)

Returns the cube root of the input

CBRT(343)

7

CEIL(number)

Returns the nearest integer greater than or equal to argument

CEIL(25.3)

26

DEGREES(DOUBLE)

Converts radians to degrees

DEGREES(0.67)

38.38817227376516

EXP(number)

Exponential

EXP(2.5)

12.182493960703473

FLOOR(number)

Returns the nearest integer less than or equal to argument

FLOOR(25.3)

25

LN(number)

Natural logarithm

LN(2.5)

0.9162907318741551

LOG10(number)

Base 10 logarithm

LOG(2.5)

0.3979400086720376

MOD(x:number, y:number)

Returns the remainder of x / y

MOD(20,3)

2

POWER(x:number, y:number)

Returns x to the power of y

POWER(20,3)

8000

RADIANS(DOUBLE)

Converts degrees to radians

RADIANS(38.39)

0.6700318998406232

RAND

Random value in the range [0.0; 1.0)

RAND()

0.6324099982812553

RAND(number)

Random value in the range [0.0; 1.0) using the given seed

RAND(10)

0.7304302967434272

ROUND(number)

Rounds to an integer

ROUND(34.5678)

35

ROUND(number, s:integer)

Rounds to s decimal places

ROUND(34.5678, 2)

34.57

SIGN(number)

Returns -1, 0 or 1 for negative, zero or positive argument, respectively

SIGN(-25)

-1

SQUARE(number)

Squares the input

SQUARE(2)

4

SQRT(number)

Returns the square root of the input

SQRT(4)

2

TRUNCATE(number)

Truncates to an integer

TRUNC(34.5678)

34

TRUNCATE(number, s:integer)

Truncates to s decimal places

TRUNC(34.5678, 2)

34.56

String Functions

Table 10. Descriptions of string functions
Function Description Example Result

string || string

Concatenates two strings

'John' || ' ' || 'Doe'

John Doe

ASCII(string)

Returns the ASCII code of the first character of the argument

ASCII('a')

97

BTRIM(string)

Equivalent to TRIM(BOTH ' ' FROM string)

CONCAT_WS(separator_string, string1, string2, …​)

Returns a string that consists of the arguments string1 separator_string string2

CONCAT_WS('-', 'John', 'Doe')

John-Doe

INITCAP(string)

Converts the first letter of each word to upper case, and the rest to lower case

INITCAP('john DOE')

John Doe

LENGTH(string)

Length of the string

LENGTH('John Doe')

8

LOWER(string)

Converts the string to lower case

LOWER('John Doe')

john doe

LTRIM(string)

Removes the empty spaces from the left-hand side of string. This function is equivalent to TRIM(LEADING ' ' FROM string)

RTRIM( John Doe')

John Doe

POSITION(substring IN string [FROM position])

Returns the position of the first occurrence of substring in string

POSITION('Doe' IN 'John Doe')

5

REPLACE(substring, old_string, new_string)

Replaces all occurrences of substring in old_string with new_string

REPLACE('John', 'John Doe', 'Jane')

Jane Doe

RTRIM(string)

Removes the empty spaces from the right-hand side of string. This function is equivalent to TRIM(TRAILING ' ' FROM string)

RTRIM(John Doe ')

John Doe

SUBSTRING(string FROM integer)

Extracts a substring starting with the given position

SUBSTRING('John Doe' FROM 6)

Doe

SUBSTRING(string FROM integer FOR integer)

Extracts a substring starting with the given position for the given length

SUBSTRING('John Doe' FROM 1 FOR 4)

John

TRIM([LEADING|TRAILING|BOTH] [characters FROM] string)

Removes characters (a space by default) from the start/end/both ends of the string

TRIM(BOTH '[]' FROM '[John Doe]')

John Doe

TRIM(characters FROM string)

Equivalent to TRIM(BOTH characters FROM string)

TRIM('[]' FROM '[John Doe]')

John Doe

TRIM(string)

Equivalent to TRIM(BOTH ' ' FROM string)

TRIM(' John Doe ')

John Doe

UPPER(string)

Converts a string to upper case

UPPER('John Doe')

JOHN DOE

Table-Valued Functions

Table-valued functions return tables of batch or streaming sources that you can use in SQL statements.

Table 11. Descriptions of table-valued functions
Function Description

generate_series(start_number, end_number)

Returns a table that contains a series of numbers, starting from the start_number argument and ending with the stop_number arguments.

generate_stream(numbers_per_second)

Returns a table that contains a stream of numbers, starting from 0 at a rate of numbers_per_second every second, without an upper bound

To use a table-valued function in FROM clause, you must wrap it in a TABLE keyword. For example:

SELECT *
FROM TABLE(generate_series(1, 3));

Trigonometric Functions

Table 12. Descriptions of trigonometric functions
Function Description

ACOS(double)

Inverse cosine

ASIN(double)

Inverse sine

ATAN(double)

Inverse tangent

ATAN2(x:number, y:number)

Arc tangent

COS(double)

Cosine

COT(double)

Cotangent

SIN(double)

Sine

TAN(double)

Tangent

Windowing Table-Valued Functions

Windowing functions assign input records from the input table into windows. Their output contains all the input columns, with two added columns: window_start and window_end. You can use the added columns in the GROUP BY expression when doing streaming aggregation.

For a guide about streaming windowed aggregations in SQL, see Stream Processing in SQL.

Function Description

TUMBLE(TABLE(input),DESCRIPTOR(time_col),window_size)

Assigns input records to tumbling windows.

HOP(TABLE(input),DESCRIPTOR(time_col),window_size, slide_step)

Assigns input records to hopping windows.