AQL: querying data from several archetypes

Specifications AQL
1

Hi everyone!
In Catalonia we are working to evolve our Shared Electronic Health Record (HC3) to OpenEHR and we have faced a problem that we would like to consult.
The way in which we currently collect the clinical variables of a patient such as weight, height, body temperature… is in a single information structure of the type “code + result” so that querying by SQL all the clinical variables of a specific patient, to be able to visualise them in another system, is a simple query since the path is always the same for any type of variable. However, OpenEHR proposes the use of different archetypes for each of these variables, so querying them to display them all on screen makes this query more difficult.
Our doubt is: is there any way, using AQL, to request the values of all these archetypes that will be contained in the same composition? To our understanding, and please correct me, in order to query each element of each different archetype we would need to know their atXXX codes to be able to include them in the query, and this is what worries us as it implies a high maintenance cost.

I hope I have explained myself well, I will be happy to read your opinions and answers, I am very new in this AQL stuff and any contribution is welcome!
Thank you very much in advance!

5 Likes
2

Hi Tara!

That is correct, you need to know the path of the relevant data elements in each archetype/template to get the data through AQL. Archetypes are not name/value pairs, but (prospectively) maximal data set models of clinical concepts.

Here’s a sample AQL for getting the main quantity elements of each archetype for body weight, height/length and body temp, plus the contextual start time for the composition, given a super simple template where each of the three archetypes are contained on the root level of the composition:

SELECT
    t/data[at0002]/events[at0003]/data[at0001]/items[at0004]/value as temp,
    w/data[at0002]/events[at0003]/data[at0001.1]/items[at0004]/value as weight,
    h/data[at0001]/events[at0002]/data[at0003.1]/items[at0004]/value as height,
    c/context/start_time as start_time
FROM COMPOSITION c[openEHR-EHR-COMPOSITION.encounter.v1]
    CONTAINS
        OBSERVATION t[openEHR-EHR-OBSERVATION.body_temperature.v2] AND
        OBSERVATION w[openEHR-EHR-OBSERVATION.body_weight.v2] AND
        OBSERVATION h[openEHR-EHR-OBSERVATION.height.v2]
WHERE c/archetype_details/template_id/value = "aql_demo_template"

This is the template I used as the basis for this query:
https://tools.openehr.org/designer/#/viewer/shared/Pz9zaGFyZWRJZD0xJDkzNjY5YjJjZDdmNTRiN2E5OTY1ZjA1NWE1NmFmNmQx

2 Likes
3

Nice example, Silje!

Just a small correction in that this query would actually work even if the OBSERVATIONS were nested inside SECTIONS as the CONTAINS statement in AQL basically says, we don’t care how deeply this nested … give us this OBSERVATION.

@tarabonet - you do raise an issue that I have been muttering about for some time, which I think does need addressed, in that increasingly people are expecting to be able to query on individual ELEMENT name/value pairs via a SNOMED or LOINC code, without having to know the atCode or the detailed path within an archetype.

On thing you can do, is to add a term mapping into the patient data for the particular ELEMTs that you want to query this way. I think @bna has some experience with this.

The name of an ELEMENT in an archetype is held in a little structure called LOCATABLE

name : “temperature”
archetype_nodeId: at0004

now because ‘name’ is a DV_TEXT datatype it can carry term_mappings which you can add when the data is committed

like (rough idea!)

name : “temperature”
name.mapping[0].target = SNOMED-CT:: 123456:: Body temperature (observable)
name.mapping[0].target = LOINC:: 123456:: Body temperature
archetype_nodeId: at0004

These mappings can be retrieved and filtered via AQL

SELECT
    t/data[at0002]/events[at0003]/data[at0001]/items[at0004]/value as temp,
    t/data[at0002]/events[at0003]/data[at0001]/items[at0004]/name/mappings[0]/target/code_string as temp_code,

    w/data[at0002]/events[at0003]/data[at0001.1]/items[at0004]/value as weight,
    h/data[at0001]/events[at0002]/data[at0003.1]/items[at0004]/value as height,
    c/context/start_time as start_time
FROM COMPOSITION c[openEHR-EHR-COMPOSITION.encounter.v1]
    CONTAINS
        OBSERVATION t[openEHR-EHR-OBSERVATION.body_temperature.v2] AND
        OBSERVATION w[openEHR-EHR-OBSERVATION.body_weight.v2] AND
        OBSERVATION h[openEHR-EHR-OBSERVATION.height.v2]
WHERE c/archetype_details/template_id/value = "aql_demo_template"
AND
 t/data[at0002]/events[at0003]/data[at0001]/items[at0004]/name/mappings[0]/target/code_string = '123456'

However , the big problem remains that you still need to know the path to the Temperature atCode inside the OBSERVATION archetype.

In other projects, we have set up a wee mapping table that handles this

but this still requires a bit of overhead

What I have been suggesting, is that we need to be able to do something like this

SELECT
    t/value as temp,
    w/value as weight,
    h/value as height,
    c/context/start_time as start_time
FROM COMPOSITION c[openEHR-EHR-COMPOSITION.encounter.v1]
    CONTAINS
        OBSERVATION CONTAINS (ELEMENT t OR ELEMENT h or ELEMENT w)
WHERE c/archetype_details/template_id/value = "aql_demo_template"
AND
 t/name/mappings/target/code_string = '123456' //Temp
OR
 w/name/mappings/target/code_string = '2344545456' //Weight
 OR 
  h/name/mappings[0]/target/code_string = 234232345' //Height

better still

SELECT
    t/value as temp,
    w/value as weight,
    h/value as height,
    c/context/start_time as start_time
FROM COMPOSITION c[openEHR-EHR-COMPOSITION.encounter.v1]
    CONTAINS
        OBSERVATION CONTAINS (ELEMENT t[code = 'LOINC::123456' OR ELEMENT h[code = '234456'] or ELEMENT w [code= '45684')
WHERE c/archetype_details/template_id/value = "aql_demo_template"

In this case ‘code’ is really an alias for

name/mappings/target/code_string = ‘123456’

Using CONTAINS to hit ELEMENTS directly is valid AQL but it would probably require quite a bit of work by the CDR vendors to make it work (and especially work fast( as they would not normally index individual values this way.

One option might be to only support fast CONTAINS on elements which had code mappings inserted, alternatively, I think as modellers we could probably indicate those Elements that are likely to need indexing e.h systolic, diastolic, map but not comment or body position or cuff size as these are ? never likely to be the target for an ELEMENT level retrieve.

3 Likes
4

While currently not yet implemented in EHRbase, we have the support of ELEMENTs on the roadmap to allow queries like:

SELECT f from EHR e 
CONTAINS COMPOSITION c
CONTAINS OBSERVATION o
CONTAINS ELEMENT f

Then you could apply (if needed) some further filters on Element to only receive those with a proper code as @ian.mcnicoll demonstrated.

So Ian’s proposal is a good one, but I don’t expect this to become available before Q1 2024.

3 Likes
5

Of course, thanks! :smiley:

6

That’s great to hear @birger.haarbrandt

Do you think the performance issue is solvable without any further clinical advice on which Elements are good targets or using mapping terms as a proxy for ‘significant’ elements. ?

7

As long as the query filters using EHR ID and especially Composition ID (as I understood would be feasible for the use-case), I suspect performance might even be fine without specific secondary indexes.

1 Like
8

We are looking at putting a second code field in LOCATABLE in the Graphite architecture for just that reason. Might be worth considering in openEHR as well.

That would then become: t/code = '123456' //Temp

Where code is the name of the new field (better names could be found obviously).

9

Also need something to make it easier to query on both terminologyid and code_string in a single clause

1 Like
10

Indeed, you are right. We are contemplating using URIs, but they are not as good as the namespace::code strings we use in openEHR, because they are not standard across terminologies (AFAIK), so you can’t reliably match parts of them, e.g. the terminology ID.