# AQL to query the same node across multiple versions of any archetype

**URL:** <https://discourse.openehr.org/t/aql-to-query-the-same-node-across-multiple-versions-of-any-archetype/325>\
**Category:** Apps\
**Created:** [8 February 2020 10:15 UTC](https://discourse.openehr.org/t/aql-to-query-the-same-node-across-multiple-versions-of-any-archetype/325 "2020-02-08T10:15:32Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![Dileep\_V\_S](https://discourse.openehr.org/user_avatar/discourse.openehr.org/dileep_v_s/32/664_2.png) [@Dileep\_V\_S](https://discourse.openehr.org/u/Dileep_V_S)\
**Post date:** [8 February 2020 10:15 UTC](https://discourse.openehr.org/t/aql-to-query-the-same-node-across-multiple-versions-of-any-archetype/325/1 "2020-02-08T10:15:32Z")

</div>

Hi,  
What is the way to write an aql to query the same node in multiple versions of the same archetype? Is there a way to use a wild card or regex to include all versions of an archetype in the contains section of an archetype?

If that is not possible, can we include multiple versions as below and point the data nodes to the same key?

select  
b\_b/data[at0002]/events[at0003]/data[at0001]/items[at0004]/value/magnitude as weight,  
b\_c/data[at0002]/events[at0003]/data[at0001]/items[at0004]/value/magnitude as weight,  
EHR e CONTAINS COMPOSITION a CONTAINS (OBSERVATION b\_b[openEHR-EHR-OBSERVATION.body\_weight.v2] OR OBSERVATION b\_c[openEHR-EHR-OBSERVATION.body\_weight.v1])

In Ethercis the above does not seem to work as the key seems to get overwritten by the 2nd instance always.

regards

---

<div class="post-metadata">

**Author:** ![birger.haarbrandt](https://discourse.openehr.org/user_avatar/discourse.openehr.org/birger.haarbrandt/32/24_2.png) [@birger.haarbrandt](https://discourse.openehr.org/u/birger.haarbrandt)\
**Post date:** [8 February 2020 10:35 UTC](https://discourse.openehr.org/t/aql-to-query-the-same-node-across-multiple-versions-of-any-archetype/325/2 "2020-02-08T10:35:19Z")

</div>

Hi,

the wildcard approach you suggest works in the Better Platform/EHRSCAPE. At EtherCIS/EHRbase, it is not supported, yet. Hope that we can put this on the roadmap but unfortunately it might take some time util it becomes available.

Birger

---

<div class="post-metadata">

**Author:** ![ian.mcnicoll](https://discourse.openehr.org/user_avatar/discourse.openehr.org/ian.mcnicoll/32/4430_2.png) [@ian.mcnicoll](https://discourse.openehr.org/u/ian.mcnicoll)\
**Post date:** [8 February 2020 13:49 UTC](https://discourse.openehr.org/t/aql-to-query-the-same-node-across-multiple-versions-of-any-archetype/325/3 "2020-02-08T13:49:16Z")

</div>

I don’t think ehrscape would like that query either as it would object to the duplicate alias - what happens if you make this weight\_1 and weight\_2

b\_b/data[at0002]/events[at0003]/data[at0001]/items[at0004]/value/magnitude as weight\_1,  
b\_c/data[at0002]/events[at0003]/data[at0001]/items[at0004]/value/magnitude as weight\_2,

and then do a little post-processing? I might be a little more comfortable with that in any case. Doing a wildcard on a specialisation makes sense, doing it across multiple versions, which just happen to have the same paths, just feels a little more scary (though, of course that is not the case in reality!!).

---

<div class="post-metadata">

**Author:** ![birger.haarbrandt](https://discourse.openehr.org/user_avatar/discourse.openehr.org/birger.haarbrandt/32/24_2.png) [@birger.haarbrandt](https://discourse.openehr.org/u/birger.haarbrandt)\
**Post date:** [8 February 2020 15:37 UTC](https://discourse.openehr.org/t/aql-to-query-the-same-node-across-multiple-versions-of-any-archetype/325/4 "2020-02-08T15:37:41Z")

</div>

I’m quite sure it works in Better (not exactly as done in the example but wildcards should be supported like body\_weight.\*)

…I might try later to confirm😎

---

<div class="post-metadata">

**Author:** ![birger.haarbrandt](https://discourse.openehr.org/user_avatar/discourse.openehr.org/birger.haarbrandt/32/24_2.png) [@birger.haarbrandt](https://discourse.openehr.org/u/birger.haarbrandt)\
**Post date:** [8 February 2020 19:08 UTC](https://discourse.openehr.org/t/aql-to-query-the-same-node-across-multiple-versions-of-any-archetype/325/5 "2020-02-08T19:08:48Z")

</div>

I can confirm that this actually works in Better:

```
select a_a
from EHR e
contains COMPOSITION a
contains OBSERVATION a_a[openEHR-EHR-OBSERVATION.body_temperature*]
```

---

<div class="post-metadata">

**Author:** ![Dileep\_V\_S](https://discourse.openehr.org/user_avatar/discourse.openehr.org/dileep_v_s/32/664_2.png) [@Dileep\_V\_S](https://discourse.openehr.org/u/Dileep_V_S)\
**Post date:** [9 February 2020 19:11 UTC](https://discourse.openehr.org/t/aql-to-query-the-same-node-across-multiple-versions-of-any-archetype/325/6 "2020-02-09T19:11:29Z")

</div>

I feel this would be a good feature in AQL as archetypes are expected to evolve for some time into the future. Over the life of a person, EHR systems would end up using multiple template versions. As I have understood, the biggest strength of OpenEHR is the ability to support evolving clinical data sets.

Since Ian has touched upon the topic of archetype specializations, What are the reason why we should specialize as opposed to creating new ones? Does it bring any advantages in querying?

regards

---

<div class="post-metadata">

**Author:** ![ian.mcnicoll](https://discourse.openehr.org/user_avatar/discourse.openehr.org/ian.mcnicoll/32/4430_2.png) [@ian.mcnicoll](https://discourse.openehr.org/u/ian.mcnicoll)\
**Post date:** [10 February 2020 07:40 UTC](https://discourse.openehr.org/t/aql-to-query-the-same-node-across-multiple-versions-of-any-archetype/325/7 "2020-02-10T07:40:20Z")

</div>

@dileep good question about specialisation. Perhaps it would be better to ask in a separate thread in the clini csl channel so we get a wider response?

---

<div class="post-metadata">

**Author:** ![Dileep\_V\_S](https://discourse.openehr.org/user_avatar/discourse.openehr.org/dileep_v_s/32/664_2.png) [@Dileep\_V\_S](https://discourse.openehr.org/u/Dileep_V_S)\
**Post date:** [10 February 2020 08:07 UTC](https://discourse.openehr.org/t/aql-to-query-the-same-node-across-multiple-versions-of-any-archetype/325/8 "2020-02-10T08:07:57Z")

</div>

Thanks Ian. Will do that  
Regards

---

<div class="post-metadata">

**Author:** ![bna](https://discourse.openehr.org/user_avatar/discourse.openehr.org/bna/32/1994_2.png) [@bna](https://discourse.openehr.org/u/bna)\
**Post date:** [10 February 2020 11:35 UTC](https://discourse.openehr.org/t/aql-to-query-the-same-node-across-multiple-versions-of-any-archetype/325/9 "2020-02-10T11:35:43Z")

</div>

> [@birger.haarbrandt](#):
>
> select a\_a from EHR e contains COMPOSITION a contains OBSERVATION a\_a[openEHR-EHR-OBSERVATION.body\_temperature\*]

I can confirm it also works in DIPS EHR Store. And if you want to be explicit about the versions you might want to something like this:

> select a\_a/archetype\_details/archetype\_id/value as archetype  
> from EHR e  
> contains COMPOSITION a  
> contains OBSERVATION a\_a  
> WHERE a\_a/archetype\_details/archetype\_id/value MATCHES {‘openEHR-EHR-OBSERVATION.body\_temperature.v1’, ‘openEHR-EHR-OBSERVATION.body\_temperature.v2’}

---

<div class="post-metadata">

**Author:** ![matijap](https://discourse.openehr.org/user_avatar/discourse.openehr.org/matijap/32/233_2.png) [@matijap](https://discourse.openehr.org/u/matijap)\
**Post date:** [11 February 2020 11:41 UTC](https://discourse.openehr.org/t/aql-to-query-the-same-node-across-multiple-versions-of-any-archetype/325/10 "2020-02-11T11:41:01Z")

</div>

In Better Platform, most types need to have an archetype specified, so the last example from @bna would need to be amended with [openEHR-EHR-OBSERVATION.body\_temperature\*] after observation alias. The WHERE condition would then work as intended.

---

<div class="post-metadata">

**Author:** ![pablo](https://discourse.openehr.org/user_avatar/discourse.openehr.org/pablo/32/3505_2.png) [@pablo](https://discourse.openehr.org/u/pablo)\
**Post date:** [14 February 2020 03:06 UTC](https://discourse.openehr.org/t/aql-to-query-the-same-node-across-multiple-versions-of-any-archetype/325/11 "2020-02-14T03:06:14Z")

</div>

This is off topic but related: though is not AQL, in the EHRServer, the Query Builder has a qualifier in the archetype selector “Allow any archetype version”.

 ![Query builder 1](https://discourse.openehr.org/uploads/default/original/1X/dc27f490355068b305005d04ffce3b76a40e0d93.jpeg)

That generates a wildcard in the condition/filter, in the archetype ID:

 ![Query builder 2](https://discourse.openehr.org/uploads/default/original/1X/cc6fb800f36498b47fa0fb98cf0188cadf86566c.jpeg)

---

<div class="post-metadata">

**Author:** ![andrazk](https://discourse.openehr.org/user_avatar/discourse.openehr.org/andrazk/32/3344_2.png) [@andrazk](https://discourse.openehr.org/u/andrazk)\
**Post date:** [18 March 2020 14:40 UTC](https://discourse.openehr.org/t/aql-to-query-the-same-node-across-multiple-versions-of-any-archetype/325/12 "2020-03-18T14:40:58Z")

</div>

Another option within Better Platform would be to use UNION ALL keyword, that merges result-sets of two separate queries.

```
SELECT f/data[at0002]/events[at0003]/data[at0001]/items[at0004]/value,
c/context/start_time/value
FROM EHR e
CONTAINS COMPOSITION c
CONTAINS OBSERVATION f[openEHR-EHR-OBSERVATION.body_temperature.v1] 

UNION ALL

SELECT f/data[at0002]/events[at0003]/data[at0001]/items[at0004]/value,
c/context/start_time/value
FROM EHR e
CONTAINS COMPOSITION c
CONTAINS OBSERVATION f[openEHR-EHR-OBSERVATION.body_temperature.v2]
ORDER BY 2 DESC
```

---

<div class="post-metadata">

**Author:** ![ian.mcnicoll](https://discourse.openehr.org/user_avatar/discourse.openehr.org/ian.mcnicoll/32/4430_2.png) [@ian.mcnicoll](https://discourse.openehr.org/u/ian.mcnicoll)\
**Post date:** [18 March 2020 17:05 UTC](https://discourse.openehr.org/t/aql-to-query-the-same-node-across-multiple-versions-of-any-archetype/325/13 "2020-03-18T17:05:55Z")

</div>

Thanks @andraz. For others, UNION is not currently part of the official AQL spec but perhaps worth thinking about. I presume you have to carefully align the SELECT statements ?

Can you show any other good examples of where UNION can solve tricky problem?

---

<div class="post-metadata">

**Author:** ![birger.haarbrandt](https://discourse.openehr.org/user_avatar/discourse.openehr.org/birger.haarbrandt/32/24_2.png) [@birger.haarbrandt](https://discourse.openehr.org/u/birger.haarbrandt)\
**Post date:** [18 March 2020 17:08 UTC](https://discourse.openehr.org/t/aql-to-query-the-same-node-across-multiple-versions-of-any-archetype/325/14 "2020-03-18T17:08:02Z")

</div>

Hi Ian,

UNION, MINUS and INTERSECT will be needed for any sensible cohort identification query. Hence, we should add these to the standard sooner than later.

---

<div class="post-metadata">

**Author:** ![ian.mcnicoll](https://discourse.openehr.org/user_avatar/discourse.openehr.org/ian.mcnicoll/32/4430_2.png) [@ian.mcnicoll](https://discourse.openehr.org/u/ian.mcnicoll)\
**Post date:** [18 March 2020 17:10 UTC](https://discourse.openehr.org/t/aql-to-query-the-same-node-across-multiple-versions-of-any-archetype/325/15 "2020-03-18T17:10:31Z")

</div>

Can you give an example @birger.haarbrandt (for my education!!)

---

<div class="post-metadata">

**Author:** ![birger.haarbrandt](https://discourse.openehr.org/user_avatar/discourse.openehr.org/birger.haarbrandt/32/24_2.png) [@birger.haarbrandt](https://discourse.openehr.org/u/birger.haarbrandt)\
**Post date:** [18 March 2020 17:21 UTC](https://discourse.openehr.org/t/aql-to-query-the-same-node-across-multiple-versions-of-any-archetype/325/16 "2020-03-18T17:21:12Z")

</div>

If you want to identify patients eligible for a clinical study. Here is a quick example:

 ![image](https://discourse.openehr.org/uploads/default/original/1X/161a21915eaba7ffb87ebd783d26b552e7dc88e3.png)

With MINUS, we can exclude all patients (EHR IDs) from the result set (those that have a diagnosis of sleep apnea) that was built based on the inclusion criteria inside one query.

Alternatively, we could do these operations outside AQL but then we potentially loose the optimizations provided by the database management systems.

---

<div class="post-metadata">

**Author:** ![pablo](https://discourse.openehr.org/user_avatar/discourse.openehr.org/pablo/32/3505_2.png) [@pablo](https://discourse.openehr.org/u/pablo)\
**Post date:** [18 March 2020 17:31 UTC](https://discourse.openehr.org/t/aql-to-query-the-same-node-across-multiple-versions-of-any-archetype/325/17 "2020-03-18T17:31:16Z")

</div>

In the EHRServer we do something like that, it’s called Combined Queries. The idea is, you can have one query to get compositions that match patient\_age \>= 40, another query for compositions that have diagnosis IN a SNOMED CT Expression (could be multiple queries for multiple comorbilities and allergies), another query for compositions containing some kind of medication, etc.

Then the combined query can mix all of them, and if there is at least one result for each query, the EHR containing those compositions is selected, that means, you have identified patients matching all the queries.

This could be used to identify cohorts for a special prevention program, or for a clinical trial, or just for doing epidemiology and knowing your population. Also is something we use to support CDS rules, since this mixed information is crucial for some rules to send alerts and reminders, also useful to calculate population indicators.

Though, this is not AQL, also the combined queries concept is not in openEHR, is something I designed with this purpose of supporting those things mentioned above.

Here there is an old demo, but works to show the key concepts [https://youtu.be/pOMhqc1TZ7A?t=987](https://youtu.be/pOMhqc1TZ7A?t=987)

---

<div class="post-metadata">

**Author:** ![andrazk](https://discourse.openehr.org/user_avatar/discourse.openehr.org/andrazk/32/3344_2.png) [@andrazk](https://discourse.openehr.org/u/andrazk)\
**Post date:** [19 March 2020 13:03 UTC](https://discourse.openehr.org/t/aql-to-query-the-same-node-across-multiple-versions-of-any-archetype/325/18 "2020-03-19T13:03:57Z")

</div>

As you say, this is Better Platform extension. Better (not a company 🙂) sooner than later the AQL spec should be revisited and extended with the most common functions used, to align them between providers.

Anyway, some specifics:  
1/ if you use aliases in both queries, those from the first query are used,  
2/ you can use ORDER BY in second query and it will be applied to the whole result-set  
3/ both queries should SELECT the same number AND same TYPES of data

```
SELECT y/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value as systolic,
       y/archetype_details/archetype_id/value as archId,
       c/context/start_time/value as time
FROM COMPOSITION c
CONTAINS OBSERVATION y[openEHR-EHR-OBSERVATION.blood_pressure.v1]

UNION ALL

SELECT y/data[at0001]/events[at0006]/data[at0003]/items[at0005]/value as diastolic,
       y/archetype_details/archetype_id/value as archId,
       c/context/start_time/value as time
FROM COMPOSITION c
CONTAINS OBSERVATION y[openEHR-EHR-OBSERVATION.blood_pressure.v2]

ORDER BY time DESC

```

This query merges blood pressure from two different archetype versions and sorts them out in a single stream of data, ordered by time of creation. But beware, if you observe the second query, it SELECTs diastolic - different path, but same type, so no issues there - but content wise you should be careful.  
With latest Better Platform we do support the [openEHR-EHR-OBSERVATION.blood\_pressure.v\*] notation for this purpose.

UNION ALL comes handy when you would like to have a result-set of a key-value format, for charts containing data from different observations, which fall under separate constraints, different templates used etc. EAsier to manage and establish complex criteria - each query for it’s own content.

Ever tried UNION ALL over three or more queries? 😉

```
SELECT y/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value as data,
       "systolic" as type,
       y/archetype_details/archetype_id/value as archId,
       c/context/start_time/value as time
FROM COMPOSITION c
CONTAINS OBSERVATION y[openEHR-EHR-OBSERVATION.blood_pressure.v1]

UNION ALL

SELECT y/data[at0001]/events[at0006]/data[at0003]/items[at0005]/value as diastolic,
       "diastolic" as type,
       y/archetype_details/archetype_id/value as archId,
       c/context/start_time/value as time
FROM COMPOSITION c
CONTAINS OBSERVATION y[openEHR-EHR-OBSERVATION.blood_pressure.v2]

UNION ALL

SELECT y/data[at0002]/events[at0003]/data[at0001]/items[at0004]/value as temperature,
        "temperature" as type,
       y/archetype_details/archetype_id/value as archId,
       c/context/start_time/value as time
FROM COMPOSITION c
CONTAINS OBSERVATION y[openEHR-EHR-OBSERVATION.body_temperature.v1]

ORDER BY time DESC
```