JPQL Functions

JPQL provides an SQL-like query language. Just as with SQL, JPQL also supports a range of functions to enhance the querying possibilities.



Aggregate Functions

There are a series of aggregate functions for aggregating the values of a field for all rows of the results.

Function Name Description Standard
COUNT(field) Returns the aggregate count of the field (Long)
MIN(field) Returns the minimum value of the field (type of the field)
MAX(field) Returns the maximum value of the field (type of the field)
AVG(field) Returns the average value of the field (Double)
SUM(field) Returns the sum of the field value(s) (Long, Double, BigInteger, BigDecimal)


String Functions

There are a series of functions to be applied to String fields.

Function Name Description Standard
CONCAT(str_field, str_field2 [, str_fieldX]) Returns the concatenation of the string fields
SUBSTRING(str_field, num1 [, num2]) Returns the substring of the string field starting at position num1 , and optionally ending at position num2
TRIM([trim_spec] [trim_char] [FROM] str_field) Returns trimmed form of the string field
LOWER(str_field) Returns the lower case form of the string field
UPPER(str_field) Returns the upper case form of the string field
LENGTH(str_field) Returns the size of the string field (number of characters)
LOCATE(str_field1, str_field2 [, num]) Returns position of str_field2 in str_field1 optionally starting at num


Temporal Functions

There are a series of functions for use with temporal values

Function Name Description Standard
CURRENT_DATE Returns the current date (day month year) of the datastore server
CURRENT_TIME Returns the current time (hour minute second) of the datastore server
CURRENT_TIMESTAMP Returns the current timestamp of the datastore server
YEAR(dateField) Returns the year of the specified date
MONTH(dateField) Returns the month of the specified date
DAY(dateField) Returns the day of the month of the specified date
HOUR(dateField) Returns the hour of the specified date
MINUTE(dateField) Returns the minute of the specified date
SECOND(dateField) Returns the second of the specified date


Collection Functions

There are a series of functions for use with collection values

Function Name Description Standard
INDEX(field) Returns index number of the field element when that is the element of an indexed List field.
SIZE(collection_field) Returns the size of the collection field. Empty collection will return 0


Arithmetic Functions

There are a series of functions for arithmetic use

Function Name Description Standard
ABS(numeric_field) Returns the absolute value of the numeric field
SQRT(numeric_field) Returns the square root of the numeric field
MOD(num_field1, num_field2) Returns the modulus of the two numeric fields ( num_field1 % num_field2