...
Example 1: retrieve all compositions' name value, context start time and composer name from a specific EHR.
SELECT c/name/value AS Name, c/context/start_time AS date_time, c/composer/name AS Composer
FROM EHR eehr_id/value=$ehrUid CONTAINS COMPOSITION c
Example 2: Retrieve all composition objects of a specific EHR.
| No Format |
|---|
SELECT c |
...
FROM EHR e[ehr_id/value=$ehrUid] CONTAINS COMPOSITION c |
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 |
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 |
...
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.
...
The first example below constrains the query source to data committed to the system before 2006-01-01.
| No Format |
|---|
TIMEWINDOW /2006-01-01 |
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 |
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.
...
- 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 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
...
- 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
...
- 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
...
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 |
Other functions
It is proposed that AQL may also support other functions, such as:
...