...
Developer
Team: Chunlan Ma MD, Heath Frankel, Thomas Beale
Introduction
Integrated Care EHR (Electronic Health Record) is defined as:
"a repository of information regarding the health of a subject of care in computer processable form, stored and transmitted securely, and accessible by multiple authorised users" [1].
In any realistic health computing environment, EHRs will be maintained in systems of varying implementation, in terms of database (e.g. objects, relational), database schema (actual table design) and access mechanisms (variants of SQL, service interfaces etc). This will be true even for implementations all based on openEHR, since the choices for the back-end are still open to system designers. For a health authority that funds such systems, most of the economic value in the data is only available if the EHRs can be queried a) independent of the context of capture - i.e. screen form etc, and b) longitudinally for a given patient. As a result, the method of querying is of crucial importance. Clearly persistence details and even system vendors could change over time within the environment, and yet queries built to achieve a certain job must continue to function during and after such changes. This leads to the requirement for a query language independent of persistence design. We state the requirements as follows:
- the query language used to search and retrieve EHR data must be neutral to system design and implementation. In another words, regardless of the persistence data model/schema design of a particular EHR system, the query statements should be same for all EHR systems. This is the key requirement. It helps to achieve:
- users are able to write a query statement without knowing actual system design or data structure;
- the query statement is sharable within a system or across multiple systems;
- the query statement is reusable and future proof, which means that the query statement doesn't need to be changed whenever actual EHR system achitecture is changed or EHR system environment is changed.
- the query language is able to:
- express the queries used for a single EHR as well as for all EHRs;
- express the queries used for retrieving an entire EHR, a clinical report, or fine-grained data within a clinical report or an entry;
- express the query criteria on both coarse-grained and fine-grained data items. For example, users can set query criteria on the name of the clinical report, or query criteria on a particular observation value (e.g. systolic blood pressure >= 140);
- express complicated query criteria. Some query criteria are very complicated. For example, this query scenario - Get the contact details of the women of child-bearing age who have not had a Pap smear in the past two years - is a population query, which has the criteria on gender, age, lab test name and date. It may also have criteria on terminology system, terminology id, data value, units etc.
- express the queries with logical time-based data rollback. This feature constrains the query to data that was available in the system within the specified time criteria. It allows the queries to be executed as though they were performed at a specified time.
- the query language is archetype-aware, i.e. enables the knowledge of location and type of data items as provided by archetypes (in the form of paths and type & other constraints) to be used when constructing queries.
- the query language is terminology-aware, i.e. can be used with terminology to retrieve items on the basis of coding.
Currently, the available query languages, such as SQL, XQuery, or Object-Oriented Query Language, have dependencies on particular system data structure and working environment. Users must know the persistence data structure of an EHR in order to write an appropriate query. The query statement cannot be used by other systems which have different data store. Consequently, none of the these languages meet the aforementioned requirements and none of these can be used directly as the query language required by integrated care EHRs
Overview
What is AQL?
Archetype Query Language (AQL) is a declarative query language developed specifically for expressing queries used for searching and retrieving the clinical data found in archetype-based EHRs. It is applied to the openEHR EHR Reference Model (RM) and the openEHR clinical archetypes, but the syntax is independent of applications, programming languages, system environment, and storage models. The minimum requirement for data to be querying with AQL (including with archetype structures and terminology) is for the data to be marked at a fine granularity with the appropriate archetype codes and terminology codes. This may be native openEHR-structured data, or legacy system data to which the relevant data markers (mainly archetype paths and terminology codes) have been added. Unlike other query languages, such as SQL or XQuery, AQL expresses the queries at the archetype level, i.e. semantic level, other than at the data instance level. This is the key in achieving sharing queries across system boundaries or enterprise boundaries.
AQL has the following distinctive features:
- the utilization of openEHR archetype path syntax in AQL. openEHR path syntax is used to locate clinical statements and data values within them using Archetypes. This path syntax is used to represent the query criteria and returned results. It allows setting query criteria using archetype and node identifiers, data values within the archetypes, and class attributes defined within the openEHR RM. It also allows the returned results to be top-level archetyped RM objects, data items within the archetypes or RM attribute values.
- the utilization of containment mechanisms to indicate the data hierarchy and constrain the source data to which the query is applied.
- the utilization of ADL-like operator syntaxes, such as matches, exists, in, negation.
- Neutral expression syntax. AQL does not have any dependencies on the underlying RM of the archetypes. It is neutral to system implementation and environment.
- Supporting queries with logical time-based data rollback.
AQL has some other features which can be found from other query languages:
- Supporting naming returned results.
- Supporting query criteria parameters.
- Supporting arithmetic operations (such as count, addition, subtraction, multiplication, and division), relational operations (>, >=, =, !=, <=, <) and Boolean operations (or, and, xor, not).
- Supporting some functions that are supported in XQuery, such as current-date().
- Users can specify their preference on the retrieved data, such as ordering preferences, or total number of retrieved results.
- Supporting queries for individual clinical subjects at the point of care, administrative purposes and clinical research purposes.
Structure & Example
Like SQL, AQL has five clauses: SELECT, FROM, WHERE, ORDER BY, and TIMEWINDOW. The SELECT clause specifies the data elements to be returned. The FROM clause specifies the result source and the corresponding containment criteria. The WHERE clause specifies data value criteria within the result source. The ORDER BY clause indicates the data items used to order the returned result set. TIMEWINDOW clause is to constrain the query to data that was available in the system within the specified time criteria.
Here is an example of AQL statement. This statement returns all blood pressure values where systolic value is greater or equal to 140 or diastolic value is greater or equals to 90 within a specified EHR. AQL syntax is a synthesis of SQL structural syntax and openEHR path syntax.
AQL Syntax
Overview
AQL has SELECT, FROM, WHERE and ORDER BY clauses. openEHR path syntax is used to locate any node or data values within archetypes. The SELECT clause uses openEHR path syntax to indicate expected archetypes, elements, or data values that are need to be returned. FROM clause uses containment mechanisms indicating the scope of the query data source. WHERE clause utilises archetype path to set query criteria on archetypes or any node within the archetypes. The following sections introduce the AQL syntax in detail.
Reserved words and characters
AQL has the following reserved keywords and characters:
- select, from, where, order by, top, and, or
- contains, max, now, current-date, current-date-time
- timewindow (need to be confirmed)
- matches, in, exists, not in
- '"' and ''': double quote characters and single quote characters are used to delimit string values;
- '|': bar characters are used to delimit intervals;
- []: brackets are used to delimit coded terms, archetype id values or openEHR reference model class attribute values.
- {}: brackets are used with MATCHES delimiting matches criteria
- <, >, =, %, !, $
openEHR path syntax
The openEHR path syntax has been described in a detail (http://www.openehr.org/svn/specification/TRUNK/publishing/architecture/overview.pdf
...
).
...
AQL
...
utilises
...
the
...
basic
...
openEHR
...
path
...
syntax
...
to
...
represent
...
both
...
coarse-grained
...
and
...
fine-grained
...
archetype
...
nodes
...
and
...
openEHR
...
class
...
instances
...
and
...
attributes.
...
The
...
syntax
...
details
...
are
...
not
...
repeated here.
...
A
...
set
...
of
...
openEHR
...
path
...
examples
...
are
...
provided.
...
openEHR
...
path
...
examples
...
-
...
archetype
...
path
...
Archetype
...
path
...
is
...
the
...
path
...
referring
...
to
...
any
...
nodes
...
within
...
an
...
archetype.
...
The
...
picture
...
below
...
is
...
an
...
ADL
...
workbench
...
screen
...
shot
...
of
...
apgar
...
observation
...
node
...
map.
...
The
...
table
...
below
...
shows
...
some
...
examples.
...
Archetype RM type | Archetype node name | Path syntax |
|---|---|---|
OBSERVATION | Apgar score | / |
OBSERVATION | 1 minute | /data[at0002]/events[at0003] |
OBSERVATION | Heart rate | /data[at0002]/events[at0003]/data[at0001]/items[at0005] |
openEHR path examples - path to RM class attributes
Another type of openEHR path is the path pointing to an attribute of openEHR Reference Model classes. The examples shown below uses the attributes from COMPOSITION class.
OpenEHR RM attribute name | Path syntax |
|---|---|
COMPOSITION.category | /category |
COMPOSITION.context.start_time |
...
/context/start_time |
...
COMPOSITION.uid.value |
...
/uid/value |
...
AQL variables
AQL variables are used to refer a specific archetype or openEHR class defined within an AQL statement. It has following featueres:
- AQL variable names must be defined in an AQL FROM clause.
- A variable name must be unique within an AQL statement.
- Not all classes or archetypes defined within an AQL statement need a variable name. It is required only when other clauses within the AQL statement need to refer to the class or archetype defined in the FROM clause. For instance, the example doesn't define a variable name for EHR class. It has a variable name - c - indicating the composition archetype with 'openEHR-EHR-COMPOSITION.encounter.v1'
...
- as
...
- the
...
- archetype
...
- id,
...
- and
...
- varialbe
...
- name
...
- -
...
- o
...
- -
...
- referring
...
- to
...
- the
...
- observation
...
- archetype
...
- with
...
- archetype
...
- id
...
- 'openEHR-EHR-OBSERVATION.blood_pressure.v1'.
...
- Variable
...
- name
...
- o
...
- is
...
- used
...
- by
...
- both
...
- WHERE
...
- clause
...
- and
...
- SELECT
...
- clause.
...
- Variable names are used by other clauses within the AQL statement along with openEHR path to form a complete AQL path - Identified path. Refer back to Section 2.2. example, o/data[at0001/events[at0006]/data[at0003]/items[at0004]/value/value
...
- ]
...
- points
...
- to
...
- the
...
- systolic
...
- value
...
- node
...
- of
...
- the
...
- observation
...
- with
...
- 'openEHR-EHR-OBSERVATION.blood_pressure.v1'.
...
- Except for the reserved words and characters, and space, any letters, Alphanumeric, or underscore can be used for a variable name. It is not case sensitive. Digit numbers are not recommenced to be used at the beginning of a variable name even though no restriction on this at the current stage.
Parameters
AQL syntax supports parameterization. AQL parameters are used to indicate a criteria, which will be substituted with real criteria value at run time. Supporting parameterization is one of the mechanism used to realise sharing AQL statements within a system or cross system boundaries.
When parameters are needed
Parameters are needed when the same AQL query statement is used with different criteria values. This AQL example is to return all abnormal blood pressure values for a single specific EHR. This query has a parameter $ehrUid. This parameter will be substituted by a real EHR.ehr_id value at run time. Consequently, this query can be reused for all EHRs either within an EHR system or by other EHR systems (Assuming these EHR systems support AQL).
Parameter syntax
A parameter always starts with a dollar sign $ followed by the parameter name, e.g. $ehrUid. Expect for AQL reserved words or characters, parameter name can be any letter(s), alphanumeric values or underscore _. It cannot have a space.
A parameter can be used for all criteria values within an AQL statement, e.g.
- within a predicate
No Format [$archetypeId], [at0003, $nameValue], [ehr_id/value=$ehrId]
...
- outside a predicate
No Format o/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value/value>$systolicCriteria
...
Predicate
AQL has three types of predicates: standard predicate, archetype predicate, and node predicate.
Standard predicate
Standard predicate always has left operand, operator and right operand, e.g. [ehr_id/value='123456'
...
].
...
- left
...
- operand
...
- is
...
- normally
...
- an
...
- openEHR
...
- path,
...
- such
...
- as
...
- ehr_id/value,
...
- name/value
...
- right
...
- operand
...
- is
...
- normally
...
- a
...
- criteria
...
- value
...
- or
...
- a
...
- parameter,
...
- such
...
- as
...
- '123456',
...
- $ehrUid.
...
- It
...
- can
...
- also
...
- be
...
- an
...
- openEHR
...
- path
...
- (based
...
- on
...
- the
...
- BNF),
...
- but
...
- we
...
- do
...
- not
...
- have
...
- an
...
- example
...
- of
...
- this
...
- situation
...
- yet.
...
- operators
...
- include:
...
No Format
...
>, >=, =, <, <=, !=
...
Archetype predicate
Archetype predicate is a shortcut of standard predicate, i.e.
...
the
...
predicate
...
does
...
not
...
have
...
left
...
operand
...
and
...
operator.
...
It
...
only
...
has
...
an
...
archetype
...
id,
...
e.g.
...
[openEHR-EHR-COMPOSITION.encounter.v1
...
].
...
Archetype
...
predicate
...
is
...
a
...
specific
...
type
...
of
...
query
...
criteria
...
indicating
...
what
...
archetype
...
instances
...
are
...
relevant
...
to
...
this
...
query.
...
It
...
is
...
used
...
to
...
scope
...
the
...
the
...
data
...
source
...
from
...
which
...
the
...
query
...
expected
...
data
...
is
...
to
...
be
...
retrieved.
...
Therefore,
...
an
...
archetype
...
predicate
...
is
...
only
...
used
...
within
...
an
...
AQL
...
FROM
...
clause,
...
for
...
example,
...
FROM |
...
EHR |
...
[ehr_id/value='1234' |
...
] |
...
CONTAINS |
...
COMPOSITION |
...
c [openEHR-EHR-COMPOSITION.encounter.v1 |
...
] |
...
CONTAINS |
...
] |
Node predicate
Node predicate is also a shortcut of standard predicate. It has the following forms:
- containing an archetype node id (known as atcode) only, e.g.
No Format [at0002] - containing an archetype node id and a name value criteria, e.g.
No Format [at0002 and name/value=$nameValue] [at0002 and name/value='real name value']
...
- containing an archetype node id and a shortcut of name value criteria, e.g.
...
No Format
...
[at0002, $nameValue] [at0002, 'real name value']
...
- The above three forms are the most common node predicates. A more advanced form is to include a general criteria instead of the name/value criteria within the predicate. The general criteria consists of left operand, operator, and right operand, e.g.
No Format [at0002 and value/defining_code/terminology_id/value=$terminologyId]
...
Node
...
predicate
...
defines
...
criteria
...
on
...
fine-grained
...
data.
...
It
...
is
...
only
...
used
...
within
...
an
...
identified
...
path.
...
AQL
...
operators
Basic AQL operators
The table below shows the basic AQL operators, meaning and example.
Operator | Meaning | Example | ||
|---|---|---|---|---|
= | Equal |
|
...
> | Greater than |
|
...
>= |
...
Greater |
...
than |
...
or |
...
equal |
...
to |
...
|
...
< | Smaller than |
|
...
<= |
...
Smaller than or equal to |
|
...
!= | not equals to |
|
...
Advanced AQL operators
These advanced operators are not yet supported by the grammar. These operator syntax are borrowed from ADL specifications. These are proposed to improve the richness and flexibility of AQL so that AQL syntax supports more complicated query scenarios. This link shows these operators, scenarios and the syntax.
AQL identified paths
Usage
Identified paths are used to locate data items within an archetype RM class. Except for FROM clause, an identified path could appears in any clauses of an AQL statement. For example, it can be used to indicate the data to be returned in a SELECT clause, or the data item on which query criteria are applied in a WHERE clause.
Syntax
AQL identified path has the following forms:
- consisting an AQL variable name defined within the FROM clause, followed by an openEHR path, e.g.
No Format o/data[at0001]/.../data[at0003]/items[at0004]/value/value
...
- consisting an AQL variable name followed by a predicate, e.g.
...
No Format
...
o[name/value=$nameValue]
...
- consisting an AQL variable name followed by a predicate and an openEHR path, e.g.
...
No Format
...
o[name/value=$nameValue]/data[at0001]/.../data[at0003]/items[at0004]/value/value
...
AQL structure
Overview
AQL structure has been briefly introduced in Section 2.2.
...
This
...
section
...
introduces
...
AQL
...
structure
...
in
...
details.
...
AQL
...
structure
...
has
...
the
...
following
...
clauses
...
and
...
these
...
clauses
...
must
...
be
...
listed
...
in
...
the
...
same
...
order
...
as
...
the
...
list
...
below
...
in
...
an
...
AQL
...
statement.
...
An
...
AQL
...
statement
...
must
...
contain
...
SELECT
...
and
...
FROM
...
clauses.
...
- SELECT (mandatory)
...
- FROM
...
- (mandatory)
...
- WHERE
...
- (optional)
...
- ORDER
...
- BY
...
- (optional)
...
- TIMEWINDOW
...
- (optional)
...
FROM
The FROM clause utilises class expressions and a set of containment criteria to specify the data source from which the query required data is to be retrieved. Its function is similar as the FROM clause of an SQL expression.
FROM Syntax
A FROM clause consists of three parts: keyword - FROM, class expression and/or containment constraints, e.g.
| No Format |
|---|
FROM EHR e [ehr_id/value=$ehrId] CONTAINS COMPOSITION c[openEHR-EHR-COMPOSITION.report.v1]
{noformat}
h5. Class expressions
Two examples of a class expression are shown below:
\\
{noformat} |
Class expressions
Two examples of a class expression are shown below:
| No Format |
|---|
EHR e [ehr_id/value=$ehrId]\\
COMPOSITION c[openEHR-EHR-COMPOSITION.report.v1]
{noformat}
\\
Class expressions are used for two purposes:
\\
# indicating the constraints on RM classes so as to scope the data source for the query. For instance, EHR e\ |
Class expressions are used for two purposes:
- indicating the constraints on RM classes so as to scope the data source for the query. For instance, EHR e[ehr_id/value='123456'
...
- ]
...
- indicates
...
- that
...
- the
...
- required
...
- data
...
- must
...
- be
...
- from
...
- a
...
- specific
...
- EHR
...
- with
...
- ehr_id
...
- value
...
- '123456';
...
- while
...
- "COMPOSITION
...
- c
...
- [openEHR-EHR-COMPOSITION.report.v1
...
- ]
...
- "
...
- indicates
...
- the
...
- required
...
- data
...
- must
...
- be
...
- from
...
- or
...
- must
...
- be
...
- associated
...
- with
...
- a
...
- composition
...
- instance
...
- with
...
- archetype
...
- id
...
- -
...
- openEHR-EHR-COMPOSITION.report.v1.
...
- defining
...
- a
...
- RM
...
- class
...
- variable
...
- that
...
- may
...
- be
...
- used
...
- by
...
- other
...
- clauses
...
- to
...
- indicate
...
- the
...
- required
...
- data
...
- or
...
- data
...
- items
...
- on
...
- which
...
- query
...
- criteria
...
- are
...
- applied.
...
- The
...
- example
...
- below
...
- uses
...
- the
...
- class
...
- expression
...
- to
...
- define
...
- a
...
- variable
...
- e
...
- which
...
- is
...
- used
...
- by
...
- the
...
- SELECT
...
- clause
...
- indicating
...
- all
...
- relevant
...
- ehr_id
...
- values
...
- are
...
- retrieved,
...
- and
...
- a
...
- variable
...
- c
...
- used
...
- by
...
- WHERE
...
- clause
...
- indicating
...
- that
...
- the
...
- query
...
- criteria
...
- is
...
- set
...
- on
...
- the
...
- composition
...
- template
...
- id
...
- value.
...
No Format
...
SELECT e/ehr_id/value FROM EHR e CONTAINS COMPOSITION c[openEHR-EHR-COMPOSITION.referral.v1] WHERE c/archetype_details/template_id/value=$templateId
...
Class
...
expressions
...
syntax
...
include
...
three
...
parts.
...
A
...
class
...
expression
...
must
...
have
...
part
...
one
...
and
...
at
...
least
...
one
...
of
...
part
...
two
...
or
...
part
...
three.
...
- part
...
- one
...
- (mandatory):
...
- openEHR
...
- RM
...
- class
...
- name,
...
- such
...
- as
...
- EHR,
...
- COMPOSITION,
...
- OBSERVATION
...
- etc.
...
- part
...
- two
...
- (optional):
...
- AQL
...
- variable
...
- name
...
- part
...
- three
...
- (optional):
...
- a
...
- standard
...
- predicate
...
- or
...
- an
...
- archetype
...
- predicate.
...
Containment
Since archetypes are in hierarchical structure, AQL has a containment constraint which specifies the hierarchical relationships between parent and child data items. 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 is very simple: using keyword CONTAINS between two class expressions. Left class expression is the the parent object of the right class expression, e.g.
| No Format |
|---|
EHR e CONTAINS COMPOSITION c [openEHR-EHR-COMPOSITION.referral.v1]
{noformat}
\\
Boolean operators |
Boolean operators (AND,
...
OR,
...
NOT)
...
and
...
parentheses
...
are
...
used
...
when
...
multiple
...
containment
...
constrains
...
are
...
required,
...
e.g.
| No Format |
|---|
\\ {noformat} EHR EHR e CONTAINS COMPOSITION c [openEHR-EHR-COMPOSITION.referral.v1] AND COMPOSITION c1 [openEHR-EHR-COMPOSITION.report.v1] { |
| No Format |
|---|
EHRR e CONTAINS COMPOSITION c [openEHR-EHR-COMPOSITION.referral.v1] CONTAINS (OBSERVATION o openEHR-EHR-OBSERVATION-laboratory-hba1c.v1 AND OBSERVATION o1 openEHR-EHR-OBSERVATION-laboratory-glucose.v1) {noformat}\\ \\ \\ \\ \\ h4. WHERE AQL WHERE clause is used to represent further criteria applied to the data items within the objects declared in the FROM clause. WHERE clause expresses the query criteria that cannot be represented in other AQL clauses, such as criteria on archetype id, composition committal date time, and the criteria on in which order the returned results should be listed. h5. Syntax WHERE clause syntax has the following parts (in order): keyword WHERE and identified expression(s). Boolean operator (AND, OR, NOT) and parenthesis can be used to represent multiple identified expressions. The examples are listed below. \\ {noformat} |
WHERE
An AQL WHERE clause is used to represent further criteria applied to the data items within the objects declared in the FROM clause. A WHERE clause expresses the query criteria that cannot be represented in other AQL clauses, such as criteria on archetype id, composition committal date time, and the criteria on in which order the returned results should be listed.
Syntax
The WHERE clause syntax has the following parts (in order): keyword WHERE and identified expression(s). Boolean operator (AND, OR, NOT) and parenthesis can be used to represent multiple identified expressions. Examples:
| No Format |
|---|
WHERE c/name/value=$nameValue AND c/archetype_details/template_id/value=$templateId
{noformat}\\
{noformat} |
| No Format |
|---|
WHERE (c/name/value=$nameValue OR c/archetype_details/template_id/value=$templateId) AND
o/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value/value >= 140
{noformat}\\
\\
\\
\\
\\
h5. Identified expression
Identified expression specifies the criteria within WHERE clause. It consists of left operand, operator and right operand.
# Left operand is normally an identified path,
# Operator is normally the basic operator,
# Right operand is the criteria value, which normally is primitive type, such as string, integer, boolean, double, or float. When it is a string value, single quotation marks or double quotation marks are required. Right operand can also be a parameter or an identified path as well.
Examples are listed below:
* Left operand is an identified path and right operand is a real criteria value - primitive type:
{noformat} |
Identified expression
Identified expression specifies the criteria within WHERE clause. It consists of left operand, operator and right operand.
- Left operand is normally an identified path,
- Operator is normally the basic operator,
- Right operand is the criteria value, which normally is primitive type, such as string, integer, boolean, double, or float. When it is a string value, single quotation marks or double quotation marks are required. Right operand can also be a parameter or an identified path as well.
Examples:
- Left operand is an identified path and right operand is a real criteria value - primitive type:
No Format o/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value/value >= 140 c/archetype_details/template_id/value='health_encounter'
...
- Left operand is an identified path and right operand is a parameter:
No Format c/archetype_details/template_id/value=$templateParameter
...
- Both left operand and right operand are an identified path (this is an advanced feature):
No Format o/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value/value > o1/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value/value
...
SELECT
A SELECT clause specifies what data is to be retrieved by the AQL query. The data can be any types from openEHR RM and any primitive data types. In addition, SELECT clause uses TOP to indicate the number of result setS that should be returned and name alias to rename the retrieved data.
The SELECT syntax always starts with keyword SELECT, followed by TOP (optional), and identified path(s) or variable name(s) defined in the FROM clause. Each variable name or an identified path may have a name alias renaming the associated data.
If variable name(s) is(are) required, the full object of the type associated with the variable is retrieved, such as a COMPOSITION, an OBSERVATION object etc. If identified paths are required, the path-associated data items are returned. Multiple identifiers or identified paths are separated using a comma.
Some examples are shown below.
Example 1: retrieve all compositions' name value, context start time and composer name from a specific EHR.
| No Format |
|---|
SELECT c/name/value AS Name, c/context/start_time AS date_time, c/composer/name AS Composer
FROM EHR e[ehr_id/value=$ehrUid] CONTAINS COMPOSITION c
{noformat}
|
Example
...
2:
...
Retrieve
...
all
...
composition
...
objects
...
of
...
a
...
specific
...
EHR.
...
| No Format |
|---|
SELECT c
FROM EHR e[ehr_id/value=$ehrUid] CONTAINS COMPOSITION c
{noformat}
h5. TOP
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.
\\
{noformat} |
TOP
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.
| No Format |
|---|
SELECT TOP 10 c/name/value AS Name, c/context/start_time AS date_time, c/composer/name AS Composer
FROM EHR e[ehr_id/value=$ehrUid] CONTAINS COMPOSITION c
{noformat}
h5. Name alias
Like SQL, AQL supports using name alias to rename the retrieved data. It starts with keyword - AS, followed by the name which conforms to the syntax rule of AQL variable.
h4. ORDER BY
The ORDER BY clause is used to sort the returned results. It starts with the keyword ORDER BY, followed by an identified path and the keyword DESC, DESCENDING, ASCE, or ASCENDING, e.g.
\\
{noformat} |
Name alias
Like SQL, AQL supports using name alias to rename the retrieved data. It starts with keyword - AS, followed by the name which conforms to the syntax rule of AQL variable.
ORDER BY
The ORDER BY clause is used to sort the returned results. It starts with the keyword ORDER BY, followed by an identified path and the keyword DESC, DESCENDING, ASCE, or ASCENDING, e.g.
| No Format |
|---|
ORDER BY c/name/value
{noformat}
h4. TIMEWINDOW
In addition to FROM clause, TIMEWINDOW helps to scope the data source from which the required data is retrieved. TIMEWINDOW is an addition query clause used in AQL to constrain the query to data that was available in the system (also know the data committal time) within the specified time criteria. This supports a timebased logical system rollback allowing a query to be executed as though it was performed at that specified time, which is essential for medico-legal reporting.
It starts with the keyword - TIMEWINDOW, and followed by a string compatible with the ISO 8601 representation of time interval.
The first example below constrains the query source to data committed to the system before 2006-01-01.
\\
{noformat} |
TIMEWINDOW
In addition to FROM clause, TIMEWINDOW helps to scope the data source from which the required data is retrieved. TIMEWINDOW is an addition query clause used in AQL to constrain the query to data that was available in the system (also know the data committal time) within the specified time criteria. This supports a timebased logical system rollback allowing a query to be executed as though it was performed at that specified time, which is essential for medico-legal reporting.
It starts with the keyword - TIMEWINDOW, and followed by a string compatible with the ISO 8601 representation of time interval.
The first example below constrains the query source to data committed to the system before 2006-01-01.
| No Format |
|---|
TIMEWINDOW /2006-01-01
{noformat}
|
The
...
second
...
example
...
constrains
...
the
...
query
...
source
...
to
...
data
...
committed
...
within
...
the
...
period
...
of
...
two
...
years
...
before
...
2006-01-01.
...
| No Format |
|---|
TIMEWINDOW P2Y/2006-01-01
{noformat}
h2. BNF
AQL BNF grammar file can be found from the attachment. For the sake of reading purpose, the wiki page of AQL BNF grammar is also generated.
h2. How to write an AQL query statement manually
Ocean Informatics has implemented an AQL query builder which can be used to generate AQL query statements based on archetypes automatically. We strongly recommend to use this tool to generate an AQL query statement because it can be very complicated. This section is for the people who either do not have the access to AQL query builder or for the people who want to have more understanding on AQL so that they can edit an existing AQL query manually.
h2. Query scenario
An example is used to illustrate how to write an AQL statement. The query scenario is:
*"Get all abnormal blood pressure values that are recorded in a health encounter for a specific patient."*
h2. How to write AQL query statement - step by step
h3. Step 1. Write FROM clause
FROM clause is to scope the data source for the query. Normally the first step is to shape FROM clause, which has EHR class expression and archetype class expressions.
* EHR class expression. You need to determine whether this query is applied to a single EHR or all EHRs. The latter is called population queries. If it is for all EHRs, you don't need to specify ehr_id/value in FROM clause. Otherwise, you need to either specify a specific value or use a query parameter for ehr_id/value in FROM clause. For this particular scenario, it is for a single EHR. Parameter called ehrUid is used for the ehr_id/value, I would have this AQL query statement:
{noformat} |
Grammar
How to write an AQL query statement manually
Ocean Informatics has implemented an AQL query builder which can be used to generate AQL query statements based on archetypes automatically. We strongly recommend to use this tool to generate an AQL query statement because it can be very complicated. This section is for the people who either do not have the access to AQL query builder or for users who want to have more understanding on AQL so that they can edit an existing AQL query manually.
Query scenario
An example is used to illustrate how to write an AQL statement. The query scenario is:
"Get all abnormal blood pressure values that are recorded in a health encounter for a specific patient."
Step 1. Write FROM clause
A FROM clause is to scope the data source for the query. Normally the first step is to shape FROM clause, which has EHR class expression and archetype class expressions.
- EHR class expression. You need to determine whether this query is applied to a single EHR or all EHRs. The latter is called population queries. If it is for all EHRs, you don't need to specify ehr_id/value in FROM clause. Otherwise, you need to either specify a specific value or use a query parameter for ehr_id/value in FROM clause. For this particular scenario, it is for a single EHR. Parameter called ehrUid is used for the ehr_id/value, I would have this AQL query statement:
No Format FROM EHR [ehr_id/value=$ehrUid
...
]- Archetype expressions.
- To write archetype expressions in the FROM clause, we need to determine what archetypes are required in the scenario. Since archetypes are used to represent clinical concepts. The easy way to identify archetypes from a scenario is to identify clinical concepts mentioned in the scenario. Each clinical concept is generally associated with one archetype. Two clinical concepts are mentioned in the aforementioned scenario: blood pressure and health encounter. These concepts are used to identify two archetypes used in query: an observation archetype with archetype id - openEHR-EHR-OBSERVATION.blood_pressure.v1,
...
- and
...
- a
...
- composition
...
- archetype
...
- with
...
- archetype
...
- id
...
- -
...
- openEHR-EHR-COMPOSITION.encounter.v1.
...
- Determine
...
- whether
...
- we
...
- need
...
- a
...
- variable
...
- name
...
- for
...
- each
...
- archetype
...
- class
...
- expression
...
- or
...
- not.
...
- We
...
- may
...
- need
...
- one
...
- if
...
- the
...
- reference
...
- to
...
- the
...
- archetype
...
- class
...
- expression
...
- is
...
- required
...
- by
...
- other
...
- AQL
...
- clauses.
...
- At
...
- the
...
- moment,
...
- I
...
- don't
...
- put
...
- any
...
- variable
...
- names
...
- for
...
- the
...
- archetype
...
- expressions.
...
- The
...
- two
...
- archetype
...
- expressions
...
- are:
...
No Format
...
COMPOSITION [openEHR-EHR-COMPOSITION.encounter.v1] OBSERVATION [openEHR-EHR-OBSERVATION.blood_pressure.v1]
...
- Containment expression.
...
- openEHR
...
- RM
...
- is
...
- used
...
- to
...
- identify
...
- the
...
- hierarchical
...
- relationships
...
- among
...
- the
...
- found
...
- archetypes
...
- so
...
- as
...
- to
...
- define
...
- the
...
- containment
...
- expression.
...
- For
...
- example,
...
- a
...
- composition
...
- archetype
...
- is
...
- the
...
- parent
...
- of
...
- an
...
- observation
...
- archetype,
...
- so
...
- the
...
- AQL
...
- query
...
- would
...
- look
...
- like:
...
No Format
...
FROM EHR [ehr_id/value=$ehrUid] CONTAINS COMPOSITION [openEHR-EHR-COMPOSITION.encounter.v1] CONTAINS OBSERVATION [openEHR-EHR-OBSERVATION.blood_pressure.v1]
...
Step 2.
...
Write
...
WHERE
...
clause
...
WHERE
...
clause
...
represents
...
all
...
query
...
criteria
...
that
...
cannot
...
be
...
represented
...
in
...
the
...
FROM
...
clause.
...
To
...
write
...
WHERE
...
clause
...
expression,
...
you
...
need
...
to:
...
- Find
...
- the
...
- criteria.
...
- The
...
- criteria
...
- required
...
- by
...
- the
...
- scenario
...
- are
...
- abnormal
...
- blood
...
- pressure
...
- values
...
- .
...
- Different
...
- guidelines
...
- may
...
- have
...
- different
...
- definitions
...
- for
...
- abnormal
...
- blood
...
- pressure
...
- values.
...
- Here
...
- we
...
- interpret
...
- that
...
- abnormal
...
- blood
...
- pressure
...
- means
...
- 1)
...
- the
...
- systolic
...
- pressure
...
- value
...
- is
...
- greater
...
- than
...
- or
...
- equal
...
- to
...
- 140;
...
- OR
...
- 2)
...
- diastolic
...
- pressure
...
- value
...
- is
...
- greater
...
- than
...
- or
...
- equal
...
- to
...
- 90.
...
- Write
...
- an
...
- identified
...
- expression
...
- for
...
- criterion
...
- 1).
...
- Write
...
- the
...
- Identified
...
- pathfor
...
- systolic
...
- data
...
- value.
...
- We
...
- need
...
- a
...
- class
...
- variable
...
- name
...
- as
...
- the
...
- reference
...
- to
...
- the
...
- blood
...
- pressure
...
- class
...
- expression
...
- defined
...
- in
...
- the
...
- FROM
...
- clause,
...
- so
...
- a
...
- variable
...
- name
...
- -
...
- obs
...
- -
...
- is
...
- added
...
- into
...
- the
...
- FROM
...
- clause.
...
- A
...
- path
...
- to
...
- systolic
...
- data
...
- value
...
- is
...
- also
...
- required
...
- to
...
- form
...
- the
...
- identified
...
- path.
...
- Two
...
- tools
...
- can
...
- be
...
- used
...
- to
...
- get
...
- the
...
- path:
...
- openEHR
...
- ADL
...
- WORKBENCHor
...
- Ocean
...
- Template
...
- Designer.
...
- The
...
- path
...
- to
...
- the
...
- systolic
...
- data
...
- value
...
- is
...
- /data
...
- [at0001
...
- ]/events
...
- [at0006
...
- ]/data
...
- [at0003
...
- ]/items
...
- [at0004
...
- ]/value/value.
...
- Get appropriate operator:
...
- '>='
...
- is
...
- chosen
...
- for
...
- criterion
...
- 1.
...
- Get
...
- the
...
- criteria
...
- value
...
- -
...
- 140.
...
- The
...
- query
...
- statement
...
- including
...
- the
...
- identified
...
- expression
...
- for
...
- criterion
...
- 1)
...
- is
...
- shown
...
- below:
...
No Format
...
FROM EHR [ehr_id/value=$ehrUid] CONTAINS COMPOSITION [openEHR-EHR-COMPOSITION.encounter.v1] CONTAINS OBSERVATION obs [openEHR-EHR-OBSERVATION.blood_pressure.v1] WHERE obs/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value/value >= 140
...
- Write an identified expression for criterion 2).
...
- Following
...
- the
...
- previous
...
- instruction
...
- to
...
- write
...
- the
...
- identified
...
- expression
...
- for
...
- criterion
...
- 2)
...
- which
...
- is
...
- shown
...
- as:
...
No Format
...
obs/data[at0001]/events[at0006]/data[at0003]/items[at0005]/value/value >= 90
...
- Find
...
- the
...
- appropriate
...
- boolean
...
- operator
...
- to
...
- join
...
- criterion
...
- 1)
...
- and
...
- 2)
...
- -
...
- OR.
...
- The
...
- query
...
- statement
...
- looks
...
- like:
...
No Format
...
FROM EHR [ehr_id/value=$ehrUid] CONTAINS COMPOSITION [openEHR-EHR-COMPOSITION.encounter.v1] CONTAINS OBSERVATION obs [openEHR-EHR-OBSERVATION.blood_pressure.v1] WHERE obs/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value/value >= 140 OR obs/data[at0001]/events[at0006]/data[at0003]/items[at0005]/value/value >= 90
...
Step 3.
...
Write
...
SELECT
...
clause
...
Writing
...
the
...
SELECT
...
clause
...
needs
...
to:
...
- Find
...
- what
...
- data
...
- is
...
- required.
...
- The
...
- aforementioned
...
- scenario
...
- requires
...
- abnormal
...
- blood
...
- pressure
...
- values
...
- are
...
- returned,
...
- including
...
- both
...
- systolic
...
- pressure
...
- and
...
- diastolic
...
- pressure.
...
- Write
...
- the
...
- appropriate
...
- identified
...
- path
...
- to
...
- the
...
- required
...
- item
...
- (variable
...
- name
...
- is
...
- required
...
- if
...
- the
...
- class
...
- object
...
- is
...
- required).
...
- Multiple
...
- identified
...
- path
...
- is
...
- separated
...
- using
...
- comma.
...
- For
...
- this
...
- particular
...
- scenario,
...
- two
...
- identified
...
- paths
...
- are
...
- found
...
- for
...
- systolic
...
- and
...
- diastolic
...
- data
...
- value
...
- by
...
- following
...
- Step
...
- 2.
...
- The
...
- completed
...
- query
...
- statement
...
- looks
...
- like:
...
No Format
...
SELECT obs/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value/value, obs/data[at0001]/events[at0006]/data[at0003]/items[at0005]/value/value FROM EHR [ehr_id/value=$ehrUid] CONTAINS COMPOSITION [openEHR-EHR-COMPOSITION.encounter.v1] CONTAINS OBSERVATION obs [openEHR-EHR-OBSERVATION.blood_pressure.v1] WHERE obs/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value/value >= 140 OR obs/data[at0001]/events[at0006]/data[at0003]/items[at0005]/value/value >= 90
...
AQL features that are not supported in the AQL Parser implemented by Ocean Informatics
This is the list of all features that are not yet supported in the AQL Parser implemented by Ocean Informatics (Jun 2008):
- TOP
- Name alias
- Using boolean operators in FROM clause CONTAINS is not supported. For instance, the second and third example in CONTAINMENT are not supported.
- XOR, NOT boolean operators. Currently only AND, OR are supported in WHERE clause.
- All functions are not supported.
- Advanced operators
- ORDER BY
- TIMEWINDOW
AQL features that are proposed, but not finalised
Arithmetic functions
It has been proposed that AQL would support some basic arithmetic functions, such as addition 
, subtraction 
. Multiplication, and division may be supported as well. We do not have any scenario requiring for these two functions yet.
Most cases, it is required that the left operand and the right operand must be the same type in order to do the calculation, e.g. numeric types. openEHR RM supports addition and subtraction for the types of: DV_QUANTITY, DV_COUNT, DV_INTERVAL, DV_PROPORTION, DV_DATE_TIME, DV_DATE, and DV_TIME. Again, it is required that the left and right operands must be the same type. However, one exception to this is that openEHR RM also supports subtraction between all subtypes of DV_TEMPORAL and type of DV_DURATION. AQL would support these arithmetic functions that openEHR RM supports.
The example below shows a scenario which requires that a composition report (c1) is issued in the last year. It illustrates that a type of ISO 8601 date string (current-date()) subtracts a type of ISO 8601 interval string (P1Y).
| No Format |
|---|
c1/context/other_context/items[at0006]/items[at0013]/value > current-date() - PIY
{noformat}
h3. Other functions
It is proposed that AQL may also support other functions, such as:
* |
Other functions
It is proposed that AQL may also support other functions, such as:
- current-date():
...
- a
...
- build-in
...
- function
...
- returning
...
- the
...
- current
...
- date
...
- value
...
- in
...
- ISO
...
- date
...
- string
...
- format.
...
- current-date-time():
...
- a
...
- build-in
...
- function
...
- returning
...
- the
...
- current
...
- date
...
- time
...
- value
...
- in
...
- ISO
...
- date/time
...
- string
...
- format.
...
- max:
...
- a
...
- build-in
...
- function
...
- returning
...
- the
...
- max
...
- value
...
- out
...
- of
...
- an
...
- expression.
...
Advanced
...
operators
...
All
...
advanced
...
operators
...
have
...
been
...
proposed,
...
but
...
not
...
supported
...
yet.
...
References
- ISO/TC
...
- 215
...
- Technical
...
- Report:
...
- Electronic
...
- Health
...
- Record
...
- Definition,
...
- Scope,
...
- and
...
- Context.
...
- Second
...
- Draft,
...
- August
...
- 2003.
...
- (Accessed
...
- on
...
- 14/01/2008)