Proposal for a LET keyword in AQL

Specifications AQL
1

A SQL dialect I’m using to query openEHR, supports declaring variables with a LET keyword. It might be helpful to consider adding a similar keyword to AQL.

For example this AQL query:

SELECT
   o/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value/magnitude,
   o/data[at0001]/events[at0006]/data[at0003]/items[at0005]/value/magnitude
FROM
   EHR [ehr_id/value='1234']
      CONTAINS COMPOSITION [openEHR-EHR-COMPOSITION.encounter.v1]
         CONTAINS OBSERVATION o [openEHR-EHR-OBSERVATION.blood_pressure.v1]
WHERE
   o/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value/magnitude >= 140 OR
   o/data[at0001]/events[at0006]/data[at0003]/items[at0005]/value/magnitude >= 90

Could be written as:

SELECT
   $bp[at0004]/value/magnitude,
   $bp[at0005]/value/magnitude
FROM
   EHR [ehr_id/value='1234']
      CONTAINS COMPOSITION [openEHR-EHR-COMPOSITION.encounter.v1]
         CONTAINS OBSERVATION o [openEHR-EHR-OBSERVATION.blood_pressure.v1]
LET $bp = o/data[at0001]/events[at0006]/data[at0003]/items
WHERE
   $bp[at0004]/value/magnitude >= 140 OR
   $bp[at0005]/value/magnitude >= 90

Or with an additional variable which would improve self-documentability:

SELECT $systolic, $diastolic
FROM
   EHR [ehr_id/value='1234']
      CONTAINS COMPOSITION [openEHR-EHR-COMPOSITION.encounter.v1]
         CONTAINS OBSERVATION o [openEHR-EHR-OBSERVATION.blood_pressure.v1]
LET   $systolic = o/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value/magnitude,
      $diastolic = o/data[at0001]/events[at0006]/data[at0003]/items[at0005]/value/magnitude
WHERE $systolic >= 140 OR $diastolic >= 90

The last example is easily understood by anyone even if they are not familiar with the blood pressure archetype.

Since AQL syntax uses an ANTLR4 grammar, I’m linking to the public ANTLR4 grammar for the SQL dialect used for SQL openEHR queries.

2

With support for predefined variables, the query could become:

SELECT $systolic, $diastolic
FROM
   EHR [ehr_id/value='1234']
      CONTAINS COMPOSITION [openEHR-EHR-COMPOSITION.encounter.v1]
         CONTAINS OBSERVATION o [openEHR-EHR-OBSERVATION.blood_pressure.v1]
WHERE $systolic >= 140 OR $diastolic >= 90

The LET part would move to a list of “global” or system-wide variables.

3

Could the current AS alias syntax be extended for this purpose?

https://specifications.openehr.org/releases/QUERY/latest/AQL.html#_name_alias
https://specifications.openehr.org/releases/QUERY/latest/AQL.html#_aql_example

4

The AS clause isn’t available in the WHERE clause. It could be extended but it would force to always return the data used for the alias.

The LET clause avoids this and is independent of what is in the SELECT clause.

5

Is there a reason why it shouldn’t be available in WHERE though? I think that could be very useful.

6

In the Better CDR the AS is available to be used in WHERe and ORDER BY etc clauses, as you suggest.

SELECT
bp/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value as Systolic
FROM EHR e
CONTAINS COMPOSITION c
CONTAINS OBSERVATION bp[openEHR-EHR-OBSERVATION.blood_pressure.v1]
WHERE
Systolic/magnitude>110

Perhaps both this approach and LET are helpful?

7

This is great news for the Better’s clients but bad for vendor-neutrality if the SEC doesn’t add it to the AQL specifications in a timely manner.

Using AS in the WHERE clause is practical when the query returns the data for the “shortcut”. The LET allows using “shortcuts” even if they are not returned in the SELECT clause.

What AS cannot handle is the use case where LET declares a partial path to which other paths are added. This is the first example above:

LET $bp = o/data[at0001]/events[at0006]/data[at0003]/items

The SELECT clause (it doesn’t define ...AS bp):

SELECT
   $bp[at0004]/value/magnitude,
   $bp[at0005]/value/magnitude
8

I couldn’t find a Jira ticket for adding this (welcome) feature to AQL.

@birger.haarbrandt stumbled upon these aliases in 2021: Usage of Aliases in WHERE clause. He thoroughly analyzed the pros and cons.

@yampeku Wanted to use them in 2024 but they weren’t supported.

Has any other vendor implemented this since then?