AQL Syntax Description

The LIKE binary operator is used to compare a value of type string (or dates and times) against a simple pattern. The left-hand operand is an AQL identified path to a data element that is either a String or has a String representation, while the right-hand operand is a String value, representing the pattern to be matched. It returns true if the value matches the supplied pattern.

Below is an example using a simple pattern matching:

The ? wildcard in the pattern matches any single character, while the * wildcard matches any sequence of zero or more characters. If the pattern does not contain any of these wildcards, then LIKE acts like the = (equal) operator. The LIKE operator always tries to match the entire string value, therefore if the intention is to match a sequence anywhere within the string, the pattern must start and end with the * wildcard, e.g. "*test*".

To match a literal ? or *, the respective character in a pattern must be escaped by using the backslash \ character, e.g. "test \?".

The matches binary operator is used in the WHERE clause. The left-hand operand is an AQL identified path. The right-hand operand is enclosed within curly braces ({}), and may take the following forms:

AND is a binary operator used to combine two Boolean expressions. It represents a logical 'and' (conjunction) and evaluates to true when both operands evaluate to true, and it evaluates to false otherwise.

OR is a binary operator used to combine two Boolean expressions. It represents a logical 'or' (disjunction) and evaluates to true when any of the operands evaluate to true, and it evaluates to false otherwise.

NOT is a unary operator used to negate a Boolean expression operand. It represents a logical 'not' (negation) and returns a Boolean result: true means the operand is false.

When used in the WHERE clause, a NOT expression is a kind of identified expression. The example below uses NOT operator followed by a Boolean identified expression:

The above example is equivalent to the two expressions shown in the following WHERE clause:

The NOT operand in combination with CONTAINS can be used also in the FROM clause to express an exclusion constraint, e.g.

EXISTS is a unary operator, whose operand is an identified path. It returns a Boolean result: true means the data associated with the specified path exists, false otherwise. An EXISTS expression is a kind of identified expression that can be used only in WHERE clause.

In the AQL example below, EXISTS is used in negated form to filter out COMPOSITIONs (and therefore EHRs) that do not contain a discharge ADMIN_ENTRY instance:

The COUNT function returns the number of values of given expression argument. The syntax is COUNT([DISTINCT] expression|*).

The DISTINCT keyword can be used to calculate the number of only distinct values of expression. The COUNT(*) is used to calculate the number of all rows of the result set, including duplicates and NULL values.

If there are no matching rows, then this function returns 0. Input values type can be anything, while the return type is always an Integer.

The MIN function returns the minimum value of given expression argument. The syntax is MIN(expression).

If there are no matching rows, then this function returns NULL. Input values type should be either String, Date, Time, Integer or Real, and it will also determine the return type.

The MAX function returns the maximum value of given expression argument. The syntax is MAX(expression).

If there are no matching rows, then this function returns NULL. Input values type should be either String, Date, Time, Integer or Real, and it will also determine the return type.

The SUM function returns the sum value of given expression argument. The syntax is SUM(expression).

If there are no matching rows, then this function returns NULL. Input values type should be either Integer or Real, and it will also determine the return type.

The AVG function returns the average value of given expression argument. The syntax is AVG(expression).

If there are no matching rows, then this function returns NULL. Input values type should be either Integer or Real, and it will also determine the return type.

The LENGTH function returns the number of characters of a given expression argument. The syntax is LENGTH(expression), where the argument is of type String. The result type is Integer.

The CONTAINS function with syntax CONTAINS(expression, substring) returns true if the expression argument contains the substring argument. The arguments are of type String, while the result type is Boolean.

The POSITION function with syntax POSITION(substring, expression) returns the position (index) of the first occurrence of substring argument in the value of the expression argument, or 0 (zero) if is not present. The position of the first character (start) of a string is considered position 1. The arguments are of type String, while the result type is Integer.

The SUBSTRING function with syntax SUBSTRING(expression, position, length) extracts the substring from expression argument, starting at the position-th character, and optionally stopping after a given length characters. The first argument is of type String, while position and length arguments are Integers. The result type is String. The position of the first character (start) of a string is considered position 1.

The length argument is optional, and when not provided this function will extract the substring from the given position until the end of the given expression argument.

The CONCAT function with syntax CONCAT(expr1, expr2, …​) returns the string that results from concatenating all the arguments. It may have one or more expression arguments, all of type String.

The CONCAT_WS function with syntax CONCAT_WS(separator, expr1, expr2, …​) returns the string that results from concatenating all the string expression arguments with separator string, i.e. the separator is added between the strings to be concatenated.

The ABS function returns the absolute value of given expression argument. The syntax is ABS(expression), where the argument is of type Real or Integer. The result type is derived from the argument type.

The MOD function with the syntax MOD(x, y) returns the remainder of x divided by y. The x and y expression arguments are of type Real or Integer, while the result type is derived from the arguments.

The CEIL function returns the Integer greater than or equal to expression argument. The syntax is CEIL(expression), where the argument is of type Real or Integer.

The FLOOR function returns the Integer less than or equal to expression argument. The syntax is FLOOR(expression), where the argument is of type Real or Integer.

The ROUND function with the syntax ROUND(expression, decimal) returns the rounded expression argument to decimal places. The expression argument is of type Real or Integer, and the result type of the function is derived from it. The decimal argument is of type Integer, and defaults to value 0 if not specified.

The function with syntax CURRENT_DATE() accepts no arguments and returns the current date as String value with the format 'YYYY-MM-DD'.

The function with syntax CURRENT_TIME() accepts no arguments and returns the current time as String value with the format 'hh:mm:ss'.

The function with syntax CURRENT_DATE_TIME(), or NOW(), accepts no arguments and returns the current datetime as String value with the format 'YYYY-MM-DDThh:mm:ss.sss±hh:mm'.

The function with syntax CURRENT_TIMEZONE() accepts no arguments and returns the current timezone as String value with the format '±hh:mm'.

Often the matches operator may need to perform a request to a terminology server, in order to execute some operations over a value set or code system (a synonym of terminology). Several operations may be possible: the expansion of a ValueSet (or Reference Set), checking that a concept belongs to a value set or code system, testing if one concept subsumes another one, or the mapping of concepts across different terminologies. While matching value by a pure terminology concept specified as a terminology URI is possible, the invocation of external terminology servers from AQL requires a special function, to perform all these operations.

The TERMINOLOGY function is used to invoke operations of an external terminology server and return the results for further processing by AQL. The syntax is TERMINOLOGY(operation, service_api, params_uri), having all arguments of type String, while the return type depends on the invoked operation and service_api and is considered to be of Any type.

The operation argument specifies the action to perform over the specified value set or code system. It is not restricted to any particular value as different terminology servers may use different ways of specifying the operation and its parameters. The specified operation can be properly dereferenced by the implementation of the service_api. Some examples of typical operations are:

The service_api argument represents an identifier of a type of terminology service. This is not the service endpoint as such, neither a service name, but an identifier for the kind/flavour of terminology syntax that is being sent. It provides the information to send requests related to the ValueSet, operation and parameters to the right terminology service. In addition, it is closely related to the strategy to parse the terminology service response in a format acceptable by the query processor. Some examples of typical values for service_api are:

The params_uri argument consists of URI path and query sections in compliance with IETF RFC 3986. Most common operations use this to identify the value set on which to perform the operation. A value set may be a full code system such as the full set of codes in SNOMED-CT or LOINC. The value set URI may include the version and edition sections (e.g. Australian, US, etc). When the release and version identifiers are not provided, it is up to the external terminology server to decide which default version will be used. Some examples of typical URI parameters are:

The TERMINOLOGY function can be used in the following ways in the WHERE clause:

More examples can be found in the openEHR AQL examples document.

A simple FROM clause consists of three parts: keyword FROM, class expression and/or containment constraints, e.g.

Since archetypes are in hierarchical structure, AQL has a containment constraint which specifies the hierarchical relationships between parent and child data items. The FROM clause utilises this hierarchical constraint along with class expression to determine the data source to which the AQL query is applied.

The syntax of containment constraint expression is very simple: using CONTAINS operator between two class expressions. Left-hand operand class expression is the parent object of the right-hand operand class expression, e.g.

Logical operators AND and OR and parentheses () are used when multiple containment constrains are required, e.g.

The logical operator NOT can be used in combination with CONTAINS to express an exclusion constraint, i.e. the absence of any containment relationship between a parent and potential child items. The following example matches referral COMPOSITIONs that do not contain laboratory test result OBSERVATIONs:

The WHERE clause syntax has the following parts (in order): keyword WHERE and one or more identified expressions. Logical operators AND, OR, NOT and parenthesis () can be used to combine multiple identified expressions. Examples:

The syntax always starts with the keyword SELECT, optionally followed by DISTINCT, optionally followed by TOP (deprecated - see below), followed by one or more column expressions.

A column expression is formed by an identified path, a function, a literal value or plain variable name defined in the FROM clause. Where a variable name is specified, the full object of the type associated with the variable is retrieved, such as a COMPOSITION, OBSERVATION etc. Where a function is specified, the call has to be done using the specified arguments, and the results are returned. Where an identified path is specified, the data item(s) having that archetype path are returned.

Each column expression may have a name alias renaming the associated data. When the SELECT clause contains multiple column expressions, they are separated using a comma.

Some examples are shown below.

The DISTINCT modifier specify whether duplicate rows should be filtered out from result set.

By default, an AQL query returns all data items selected by the FROM and WHERE clauses as rows in the result set. A row is considered to be duplicated in the result set if there is at least one other row with the same value for each corresponding column expression. DISTINCT specifies removal of all such duplicate rows from the result set.

Below is an example using DISTINCT modifier to filter out duplicate rows:

The TOP syntax was borrowed from SQL language for representing the number of result sets that should be returned by the AQL query. It uses BACKWARD and FORWARD to indicate the direction where to start to get the number of results to be returned.

It starts with keyword TOP, followed by an integer number and/or the direction (i.e. BACKWARD, FORWARD), e.g.

As in SQL, AQL supports the use of a name alias for the retrieved data. This is done with the keyword AS, followed by the name which conforms to the syntax rule of AQL variable.

The syntax starts with keyword ORDER BY followed by one or more sorting expressions. A sorting expression consists of an identified path, optionally followed by one of the DESC, DESCENDING, ASC, or ASCENDING keyword, indicating the sorting type (descending or ascending), e.g.

Sorting rows assumes that data identified by the path (from the sorting expression) are comparable. It implies the use of a specific operators like equal, less-than and greater-than (all available to primitives and Ordered types) on data identified by path.

When sorting type is not specified, ASC (ascending) is assumed by default.

Multiple sorting expressions are separated using a comma. If two rows are equal according to the leftmost expression, they are compared according to the next sorting expression.

The syntax was borrowed from SQL language, similar to PostgreSQL and MySQL implementation. It consists of two parts: keyword LIMIT followed by number, optionally followed by OFFSET followed by a number:

Both row_count and offset are integer numbers, row_count minimal value is 1, while minimal value for offset is 0.

If a LIMIT row_count is given, no more than that many rows will be returned (but possibly fewer, if the query itself yields fewer rows). The OFFSET offset is optional, and when not specified then offset = 0 is assumed.

The LIMIT row_count OFFSET offset is used to get results in a paginated way. For instance, when offset = 0 and row_count = 10, the result will have at most 10 rows, starting with first row. The offset of the first row is 0 (not 1). Then with offset = 10 and row_count = 10, the result will contain next 10 rows at most (i.e. rows 11-20 respectively). So increasing offset by row_count, allows to get all the results in a paginated way.

The following example will return 10 rows, representing the second page of result set, ordered by event start time: