AQL Operators - Specifications

This is a working draft of using some ADL (Archetype Definition Language) operators in AQL WHERE clause in order to improve AQL flexibility. This proposal is still under review, any comments are welcome.

matches

Clinical Scenario 1	Blood glucose > 11 mmol/L or blood glucose >= 200 mg/dL
EHR path	o/data[at0001]/events[at0002 and name/value='Any event']/data[at0003]/items[at0013.1]

AQL Expression Fragment	ehr_path/value matches { C_DV_QUANTITY< list=< ["1"]< units=<"mmol/L"> magnitude=<\|>=11\|> > < ["2"]< units=<"mg/dL"> magnitude=<\|>=200\|> > > > }

Clinical Scenario 2	Blood glucose level is between 5-6 mmol/L or between 90-110 mg/dL
EHR path	o/data[at0001]/events[at0002 and name/value='Any event']/data[at0003]/items[at0013.1]

AQL Fragment	ehr_path/value matches { C_DV_QUANTITY< list=< ["1"]< units=<"mmol/L"> magnitude=<\|5.0..6.0\|> > < ["2"]< units=<"mg/dL"> magnitude=<\|90..110\|> > > > }

Clinical Scenario 3	HbA1c > 7.0%
EHR path	o/data[at0001]/events[at0002 and name/value='Any event']/data[at0003]/items[at0013.1]
AQL Fragment	ehr_path/value matches { DV_PROPORTION matches { numerator matches {\|>7.0\|} denominator matches {\|100.0\|} } OR preferably ehr_path/value matches { DV_PROPORTION matches { numerator matches {\|>7.0\|} type = percent }

Clinical Scenario 4	Total cholesterol >= 5.0 mmol/L or LDL-C >= 3.0 mmol/L
Total cholesterol EHR path	o/data[at0001]/events[at0002 and name/value='Any event']/data[at0003]/items[at0013.1]
LDL-C EHR path	o/data[at0001]/events[at0002 and name/value='Any event']/data[at0003]/items[at0011.1 and name/value='Fractions']/items[at0013.4 and name/value='LDL-Cholesterol']
AQL Fragment	Total cholesterol EHR path/value matches{ DV_QUANTITY matches{ units matches {"mmol/L"} magnitude matches {\|>=5.0\|} } } OR LDL-C EHR path/value matches{ DV_QUANTITY matches{ units matches {"mmol/L"} magnitude matches {\|>=3.0\|} } }

Clinical Scenario 5	Require all HbA1c values done in 1999
EHR path	o/data/origin
AQL Fragment	EHR path/value matches {"^1999"} OR EHR path/value matches {\|1999-??-??\|} OR EHR path/value matches {\|1999-01-01..1999-12-31\|}

Clinical Scenario 6	Require all blood glucose values done in the morning of 1st December, 1999
EHR path	o/data/origin
AQL Fragment	EHR path/value matches {"^19991201T(((0[0-9])\|10\|11\|) (([0-5][0-9])?([0-5][0-9])?) \|(1200)) $"} OR EHR path/value matches {\|1999-12-01T00:00:00..1999-12-01T11:59:59\|}

Clinical Scenario 7	Require all blood glucose values done in the morning of December, 1999
EHR path	o/data/origin
AQL Fragment	EHR path/value matches {"^199912 (([0-2][1-9])\|10\|20\|30\|31)? (T ((0[0-5])\|10\|11) (([0-5][0-9])?([0-5][0-9])?) ) $"} OR EHR path/value matches {\|1999-12-ddT00:00:00..1999-12-ddT11:59:59\|}

Clinical Scenario 8	Require all HbA1c values done after 1999
EHR path	o/data/origin
AQL Fragment	EHR path/value matches {"^[2-9]\d\d\d"} OR EHR path/value matches {\|>1999-??-??\|}

Clinical Scenario 9	Require all HbA1c values done before 1999
EHR path	o/data/origin
AQL Fragment	EHR path/value matches {"^1\d\d[0-8]"} OR EHR path/value matches {\|<1999-??-??\|}

Clinical Scenario 10	Requires all reports composed by patient self
AQL Expression	SELECT c FROM EHR e[ehr_id=$ehrUid] CONTAINS COMPOSITION c WHERE c/composer matches {PARTY_SELF}

arithmetic operator

Clinical Scenario 1

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

AQL Fragment

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{"^[pP]([1-2][dD])|(\d+[hH])|(\d+[sS])"}          }       }

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}}}

|

nested query & not in

Clinical Scenario 1

all patients who have not been discharged

AQL Statement

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])

alternatively,

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

exists

Clinical Scenario 1	requires all blood pressure values with position recorded
AQL Expression	SELECT o FROM EHR e CONTAINS COMPOSITION CONTAINS OBSERVATION o [openEHR-EHR-OBSERVATION.blood_pressure.v1] WHERE exists {"o//*/state[at0007]/items[at0008]"} I suggest using either no {} or () around the path - preferably no brackets.