AQL Operators

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 matches {pk_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.



Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.

Comments (3)

 

  1. 26-Nov-2009

    Erik Sundvall says:

    Was the heading of this page perhaps menant to be "ADL operators" rather than "A...

    Was the heading of this page perhaps menant to be "ADL operators" rather than "AQL operators"?

  2. 11-Dec-2009

    Daniel Karlsson says:

    The ADL-fragment syntax used in examples are not compliant with the AQL syntax s...

    The ADL-fragment syntax used in examples are not compliant with the AQL syntax suggestions. Will there be a sugestion for an extended AQL syntax?

    E.g. should it be:

    (ehr_path/value/units = "mmol/L" AND ehr_path/value/magnitude >= 11) OR (ehr_path/value/units = "mg/dL" AND ehr_path/value/magnitude >=2)

    or

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

    }

    and shouldn't the list be outside the DV_QUANTITY?

  3. 16-Dec-2009

    Thomas Beale says:

    These are indeed AQL operators. I have corrected some formatting errors in the m...

    These are indeed AQL operators. I have corrected some formatting errors in the match operator sections; the values being matched are either in dADL (i.e. exact instances) or cADL (constraints) - exactly as in archetypes.