Archetype Query Language Examples

Issuer: openEHR Specification Program
Release: QUERY Release-1.1.0	Status: DEVELOPMENT
Revision: [latest_issue]	Date: [latest_issue_date]
Keywords: query, AQL, example, openehr

Issuer: openEHR Specification Program

Release: QUERY Release-1.1.0

Status: DEVELOPMENT

Keywords: query, AQL, example, openehr

© 2020 - 2021 The openEHR Foundation
The openEHR Foundation is an independent, non-profit foundation, facilitating the sharing of health records by consumers and clinicians via open specifications, clinical models and open platform implementations.
Licence	Creative Commons Attribution-NoDerivs 3.0 Unported. https://creativecommons.org/licenses/by-nd/3.0/
Support	Issues: Problem Reports Web: specifications.openEHR.org

The openEHR Foundation is an independent, non-profit foundation, facilitating the sharing of health records by consumers and clinicians via open specifications, clinical models and open platform implementations.

Licence

Creative Commons Attribution-NoDerivs 3.0 Unported. https://creativecommons.org/licenses/by-nd/3.0/

Support

Issues: Problem Reports
Web: specifications.openEHR.org

Amendment Record

Issue	Details	Raiser	Completed
Release 1.1.0
1.1.0	SPECQUERY-8 Improve readability and clean up text (non-semantic).	S Iancu	01 Dec 2020
	SPECQUERY-12 Improve terminology support by adding a new TERMINOLOGY function.	L Marco-Ruiz, H Frankel S Iancu	24 Nov 2020
	SPECQUERY-31 Move AQL examples to a new separate specification.	openEHR SEC	09 Nov 2020

Issue

Details

Raiser

Completed

Release 1.1.0

1.1.0

SPECQUERY-8 Improve readability and clean up text (non-semantic).

S Iancu

01 Dec 2020

SPECQUERY-12 Improve terminology support by adding a new TERMINOLOGY function.

L Marco-Ruiz,
H Frankel
S Iancu

24 Nov 2020

SPECQUERY-31 Move AQL examples to a new separate specification.

openEHR SEC

09 Nov 2020

Acknowledgements

Contributors

This specification benefited from wide formal and informal input from the openEHR and wider health informatics community. The openEHR Foundation would like to recognise the following people for their contributions.

Heath Frankel, Ocean Health Systems, Australia
Ian McNicoll MD, FreshEHR, UK
Luiz Marco-Ruiz, PhD, Norwegian Centre for E-health Research, Norway
Sebastian Iancu, Architect, Code24, Netherlands
Thomas Beale, Ars Semantica UK, openEHR Foundation Management Board

Support

The work reported in this paper has been funded by the following organisations:

Code24, Netherlands

Trademarks

'openEHR' is a trademark of the openEHR Foundation
'Java' is a registered trademark of Oracle Corporation
'Microsoft' and '.Net' are trademarks of the Microsoft Corporation

1. Preface

1.1. Purpose

This document documents examples of use of the openEHR Archetype Query Language (AQL).

1.2. Status

This specification is in the DEVELOPMENT state. The latest development version of this document can be found at https://specifications.openehr.org/releases/QUERY/latest/AQL_examples.html.

Known omissions or questions are indicated in the text with a 'to be determined' paragraph, as follows:

TBD: (example To Be Determined paragraph)

1.3. Tools

Various tools that can be used to work with archetypes and templates, including extracting 'archetype paths' for use in AQL statements, are listed on the openEHR website modelling tools page.

1.4. Feedback

Feedback may be provided on the openEHR AQL forum.

Issues may be raised on the specifications Problem Report tracker.

To see changes made due to previously reported issues, see the QUERY component Change Request tracker.

1.5. Original Source

The original text of this document was taken from the openEHR AQL specification.

2. Overview

Archetype Query Language (AQL) is a declarative query language developed specifically for expressing queries used for searching and retrieving the data found in archetype-based repositories.

This document provides AQL syntax examples as well as example of use of AQL in various complex scenarios, for both single-subject and population queries.

These examples mostly relate to the openEHR Reference Model (RM) and the openEHR clinical archetypes, but the syntax is independent of information model, application, programming language, system environment, and storage model.

2.1. Operator Syntax Examples

The following sub-sections illustrate various AQL operators with examples.

2.1.1. matches

The following examples show how the ADL matches operator may be used within AQL statements.

Note	the ADL expressions on the right-hand side of the `matches` operator are in ADL 1.4 format.

Scenario: Get all blood glucose values and their corresponding subject ids, where blood glucose > 11 mmol/L or blood glucose >= 200 mg/dL

SELECT
    e/ehr_status/subject/external_ref/id/value,
    o/data[at0001]/events[at0002 and name/value='Any event']/data[at0003]/items[at0013.1]/value
FROM EHR e CONTAINS COMPOSITION c
    CONTAINS OBSERVATION o [openEHR-EHR-OBSERVATION.laboratory-glucose.v1]
WHERE
    o/data[at0001]/events[at0002 and name/value='Any event']/data[at0003]/items[at0013.1]/value matches {
        C_DV_QUANTITY<
            list = <
               ["1"] = <
                   units = <"mmol/L">
                   magnitude = <|>=11|>
               >
               ["2"] = <
                   units=<"mg/dL">
                   magnitude=<|>=200|>
               >
            >
        >
    }

SELECT
    e/ehr_status/subject/external_ref/id/value as subjectId,
    a/items[at0001]/value as analyteName,
    a/items[at0001]/value as analyteResult
FROM EHR e
    CONTAINS COMPOSITION c
        CONTAINS OBSERVATION o[openEHR-EHR-OBSERVATION.laboratory_test_result.v1]
            CONTAINS CLUSTER a[openEHR-EHR-CLUSTER.laboratory_test_analyte.v1]
WHERE
    (a/items[at0001]/value/defining_code/code_string matches {'14743-9','2345-7'} AND a/items[at0001]/value/defining_code/terminology_id = 'LOINC')
    AND
    ((a/items[at0024]/value/magnitude > 11 AND a/items[at0024]/value/units matches {'mmol/L'})
        OR (a/items[at0024]/value/magnitude >= 200 AND a/items[at0024]/value/units matches {'mg/dL'}))

Scenario: Get all blood glucose values and their corresponding ehr ids, where blood glucose level is between 5-6 mmol/L or between 90-110 mg/dL

SELECT
    e/ehr_id,
    o/data[at0001]/events[at0002 and name/value='Any event']/data[at0003]/items[at0013.1]/value
EHR e CONTAINS COMPOSITION c
    CONTAINS OBSERVATION o [openEHR-EHR-OBSERVATION.laboratory-glucose.v1]
WHERE
    o/data[at0001]/events[at0002 and name/value='Any event']/data[at0003]/items[at0013.1]/value matches {
        C_DV_QUANTITY<
            list = <
                ["1"] = <
                    units = <"mmol/L">
                    magnitude = <|5.0..6.0|>
                >
                ["2"] = <
                    units = <"mg/dL">
                    magnitude = <|90..110|>
                >
            >
        >
    }

Alternative

SELECT
    e/ehr_id/value as ehrId,
    a/items[at0001]/value as analyteName,
    a/items[at0001]/value as analyteResult
FROM EHR e
    CONTAINS COMPOSITION c
        CONTAINS OBSERVATION o[openEHR-EHR-OBSERVATION.laboratory_test_result.v1]
            CONTAINS CLUSTER a[openEHR-EHR-CLUSTER.laboratory_test_analyte.v1]
WHERE
    (a/items[at0001]/value/defining_code/code_string matches {'14743-9','2345-7'} AND a/items[at0001]/value/defining_code/terminology_id = 'LOINC')
    AND
    ((a/items[at0024]/value/magnitude >= 5 AND a/items[at0024]/value/magnitude <=6 AND a/items[at0024]/value/units matches {'mmol/L'})
        OR (a/items[at0024]/value/magnitude >= 90 AND a/items[at0024]/value/magnitude >= 110 AND a/items[at0024]/value/units matches {'mg/dL'}))

Scenario: HbA1c > 7.0%

SELECT
    e/ehr_id,
    o/data[at0001]/events[at0002 and name/value='Any event']/data[at0003]/items[at0013.1]/value
EHR e CONTAINS COMPOSITION c
    CONTAINS OBSERVATION o [openEHR-EHR-OBSERVATION.laboratory-glucose.v1]
WHERE
    o/data[at0001]/events[at0002 and name/value='Any event']/data[at0003]/items[at0013.1]/value matches {
        DV_PROPORTION matches {
            numerator matches {|>7.0|}
            denominator matches {|100.0|}
        }
    }

Alternative

SELECT
    e/ehr_id/value as ehrId,
    p/data[at0001]/events[at0002]/data[at0003]/items[at0006]/value as spo2Numerator
FROM EHR e
    CONTAINS COMPOSITION c
        CONTAINS OBSERVATION p[openEHR-EHR-OBSERVATION.pulse_oximetry.v1]
WHERE
    p/data[at0001]/events[at0002]/data[at0003]/items[at0006]/value/numerator <= 96

Scenario: Total cholesterol >= 5.0 mmol/L or LDL-C >= 3.0 mmol/L

SELECT
    e/ehr_id,
    o/data[at0001]/events[at0002]/data[at0003]/items[at0013.1] AS TotalC,
    o/data[at0001]/events[at0002]/data[at0003]/items[at0011.1, 'Fractions']/items[at0013.4, 'LDL-Cholesterol'] AS LDLC
EHR e CONTAINS COMPOSITION c
    CONTAINS OBSERVATION o [openEHR-EHR-OBSERVATION.laboratory-hba1c.v1]
WHERE
    o/data[at0001]/events[at0002, 'Any event']/data[at0003]/items[at0013.1]/value matches {
        DV_QUANTITY matches {
            units matches {"mmol/L"}
            magnitude matches {|>=5.0|}
        }
    }
    OR
    o/data[at0001]/events[at0002]/data[at0003]/items[at0011.1, 'Fractions']/items[at0013.4, 'LDL-Cholesterol']/value matches {
        DV_QUANTITY matches {
            units matches {"mmol/L"}
            magnitude matches {|>=3.0|}
        }
    }

2.1.2. Arithmetic operators

The following example shows how arithmetic operators may be used within AQL statements.

Scenario: For each recorded administration of ampicillin check for problem diagnosis of skin rash that is within 2 days of the administration date

SELECT e/ehr_id
FROM EHR e CONTAINS (COMPOSITION c1
   CONTAINS ACTION a [openEHR-EHR-ACTION.medication.v1]
      CONTAINS ITEM_TREE it [openEHR-EHR-ITEM_TREE.medication.v1]) AND
      CONTAINS (COMPOSITION c2 CONTAINS EVALUATION eval [openEHR-EHR-EVALUATION.problem-diagnosis.v1])
WHERE
   it/description[openEHR-EHR-ITEM_TREE.medication.v1]/items[at0001]/value matches {"SNOMED::31087008"} AND
   eval/data[at0001]/items[at0002.1]/value/value/defining_code matches {
      CODE_PHRASE matches {[SNOMED::294506009, 21626009]}
   } AND
   eval/data[at0001]/items[at0010]/value - it/description[openEHR-EHR-ITEM_TREE.medication.v1]/items[at0018]/items[at0019]/value matches {
      DV_DURATION matches {
         value matches{<=P2d}
      }
   }

2.1.3. Nest Query and 'Not in'

The following example shows the use of a nested query and the not-in operator.

Scenario: All patients who have not been discharged

SELECT e/ehr_id
FROM
   EHR e CONTAINS
      ADMIN_ENTRY ae1 [openEHR-EHR-ADMIN_ENTRY.admission.v1]
WHERE
   ae1/encounter_id/value not in (
      SELECT ae2/encounter_id/value
      FROM
         EHR e CONTAINS
            ADMIN_ENTRY ae2 [openEHR-EHR-ADMIN_ENTRY.discharge.v1]
   )

2.2. Function Syntax Examples

The following sub-sections illustrate various AQL functions with examples.

2.2.1. Aggregate functions

Example 1: The COUNT() and MIN() functions are used to return the number of discharge letters and the date of their oldest event:

SELECT
   count(*) AS counter, min(c/context/start_time) as firstTime
FROM
   EHR [ehr_id/value=$ehrUid]
      CONTAINS COMPOSITION c[openEHR-EHR-COMPOSITION.administrative_encounter.v1]
         CONTAINS ADMIN_ENTRY [openEHR-EHR-ADMIN_ENTRY.admission.v1]

Example 2: The COUNT() function is used to return a counter of all distinct test analyte names for a given EHR:

SELECT
    COUNT(DISTINCT a/items[at0001]/value) AS counter
FROM
    EHR [ehr_id/value=$ehrUid]
    CONTAINS COMPOSITION c
        CONTAINS OBSERVATION [openEHR-EHR-OBSERVATION.laboratory_test_result.v1]
            CONTAINS CLUSTER a[openEHR-EHR-CLUSTER.laboratory_test_analyte.v1]

Example 3: Using MIN(), MAX() and AVG() functions to determine edge and mean values for systolic blood pressure:

SELECT
    MAX(o/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value/magnitude) AS maxValue,
    MIN(o/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value/magnitude) AS minValue,
    AVG(o/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value/magnitude) AS meanValue
FROM
    EHR e CONTAINS COMPOSITION c[openEHR-EHR-COMPOSITION.encounter.v1]
        CONTAINS OBSERVATION o[openEHR-EHR-OBSERVATION.blood_pressure.v1]

2.2.2. TERMINOLOGY

The following are examples of the use of TERMINOLOGY function related to FHIR terminology operations.

Example 1: Expand a value set

WHERE
    e/value/defining_code/code_string matches TERMINOLOGY('expand', 'hl7.org/fhir/r4', 'url=http://snomed.info/sct?fhir_vs=isa/50697003')

Example 2: Validate a code in a value set

WHERE
    TERMINOLOGY('validate', 'hl7.org/fhir/r4', 'system=http://snomed.info/sct&code=122298005&url=http://snomed.info/sct?fhir_vs&display=Astrovirus RNA assay') = true

Example 3: Look-up a code

WHERE
    e/value/defining_code/code_string matches TERMINOLOGY('lookup', 'hl7.org/fhir/r4', 'system=http://loinc.org&code=1963-8')

Example 4: Map a code

WHERE
    e/value/defining_code/code_string matches TERMINOLOGY('map', 'hl7.org/fhir/r4', 'system=http://hl7.org/fhir/composition-status&code=preliminary&source=http://hl7.org/fhir/ValueSet/composition-status&target=http://hl7.org/fhir/ValueSet/v3-ActStatus')

Example 5: Subsumption testing

WHERE
    TERMINOLOGY('subsumes', 'hl7.org/fhir/r4', CONCAT('system=http://snomed.info/sct&codeA=235856003&codeB=', e/value/defining_code/code_string)) = true

3. Single-Subject AQL Examples

Scenario: Get the latest 5 abnormal blood pressure values that were recorded in a health encounter for a specific patient.

SELECT
    obs/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value/magnitude AS systolic,
    obs/data[at0001]/events[at0006]/data[at0003]/items[at0005]/value/magnitude AS diastlic,
    c/context/start_time AS date_time
FROM
    EHR [ehr_id/value=$ehrUid]
        CONTAINS COMPOSITION c [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/magnitude >= 140 OR
    obs/data[at0001]/events[at0006]/data[at0003]/items[at0005]/value/magnitude >= 90
ORDER BY
    c/context/start_time DESC
LIMIT 5