openEHR archetypes may contain structures that may be repeated at almost any level. The problem is how to interpret AQL and what the resultset should be.
The problem is :
-
how to handle permutations and just return an endles combination of all combinations
-
how to make the user define exactly what kind of resultset he wants
One simple example may be openEHR-EHR-OBSERVATION.body_weight.v1 which have an optional and repeating 'any_event'. Below is a straigt forward AQL on this archetype. We are asking for the origin from the HISTORY attribute, and then for each repeating event we want the weight magnitude and unit.
As you see from the AQL there is an additional WHERE clausul telling that we only want weight with magnitude less than 45.
The question is: What do you expect as result from this query?
select
o/data[at0002]/origin/value as time,
o/data[at0002]/events[at0003]/data[at0001]/items[at0004]/value/magnitude as Weight,
o/data[at0002]/events[at0003]/data[at0001]/items[at0004]/value/units as Unit
from
composition c
contains
observation o[openEHR-EHR-OBSERVATION.body_weight.v1]
where
o/data[at0002]/events[at0003]/data[at0001]/items[at0004]/value/magnitude < 45
order by
o/data[at0002]/origin/value descThe question above seems simple. But is not. To illustrate this case we have created an OBSERVATION archetype openEHR-EHR-OBSERVATION.multiple_events_cluster.v0. This archetype has repeating structures in almost any leve. The structure of this archetype is as follows:
Header
Concept: Multiple events and clusters
Definition
OBSERVATION
DATA = {
Structure = TREE
Items
The repeating cluster (0..*)
- A CLUSTER that may be repeated unbounded.
Items
The repeating measurement (0..*)
The repeating measurement
DataType = Quantity
Constraint: Physical property = Time;
Units = yr;
Units = hr;
Units = min;
Units = microsec;
Units = sec;
Units = wk;
EventSeries = {
Any event (0..*)
} -- end EventSeries
} -- end Data
PROTOCOL = {
Structure = TREE
Items
Some protocol value (0..*)
Some protocol value
DataType = Text
Constraint: Text;
} -- end ProtocolThe case contains one Composition (example_real) in both json and xml representation.
Below is a "pseudo_composition" to describe the problem.
{
"observation": {
"origin": "t1",
"anyEvent": [{
"time": "t2",
"cluster": [{
"the measurement": {
"value": 1,
"units": "a"
}
},
{
"the measurement": {
"value": 2,
"units": "b"
}
}
]
},
{
"time": "t3",
"cluster": [{
"the measurement": {
"value": 3,
"units": "c"
}
}]
}
],
"protocol": {
"the_repeatable_element": [
"X", "Y"
]
}
}
}The following diagram give another view on the structure:
In the following there are several candidate AQL’s with their expected result_set based on the Composition above.
'AQL 1' is a normal pattern to query this kind of data. One select statement for each attribute.
select
o/data[at0001]/events[at0002]/data[at0003]/items[at0004]/items[at0005]/value/magnitude as Magn,
o/data[at0001]/events[at0002]/data[at0003]/items[at0004]/items[at0005]/value/units as Units,
o/protocol[at0007]/items[at0008]/value as Protocol
from
COMPOSITION c
contains
OBSERVATION o[openEHR-EHR-OBSERVATION.multiple_events_cluster.v0]This result set should be the complete permutation of all variables defined in the AQL. Given the total set of varibales to be:
-
The measurement value: 1,2,3
-
The measument units: a,b,c
-
The protocol value: x,y
Then the result set should be all permutation of the above parameter set.
link:resultset_aql1.json[role=include]The following is a small adjustment on the previous AQL. Instead of querying magnitude and units separately we will query the DataValue (here DV_QUANTITY).
This example alings better with the the great feedback from Seref Arikan. https://github.com/serefarikan/aql-discussion.
SELECT
o/protocol[at0007]/items[at0008]/value as Protocol,
o/data[at0001]/events[at0002]/data[at0003]/items[at0004]/items[at0005]/value as Element
FROM
COMPOSITION c
CONTAINS
OBSERVATION o
[openEHR-EHR-OBSERVATION.multiple_events_cluster.v0]This AQL will return the following resultset. Which is found as resultset_aql1_1.(json|xml).
| Protocol | Element |
|---|---|
X |
(1,a) |
Y |
(1,a) |
X |
(2,h) |
Y |
(2,h) |
X |
(3,min) |
Y |
(3,min) |
'AQL 2' add one more CONTAINS clause to the AQL, and we have removed the protocol
select
e/data[at0003]/items[at0004]/items[at0005]/value/magnitude as Magn,
e/data[at0003]/items[at0004]/items[at0005]/value/units as Units
-- (1)
from
COMPOSITION c
CONTAINS OBSERVATION o[openEHR-EHR-OBSERVATION.multiple_events_cluster.v0]
CONTAINS POINT_EVENT e --(2)
where c/uid/value = 'f6c51223-7700-4d5b-9c6a-a976d54fe13d::91215053-854b-45b8-bb2a-3b0d255858d1::1'-
Removed the protocol
-
Added "contains POINT_EVENT e"
The following table lists the expected result.
| Magn | Units |
|---|---|
1 |
a |
1 |
b |
2 |
a |
2 |
b |
3 |
c |
link:resultset_aql2.json[role=include]'AQL 3' is based on 'AQL 2' with the addition of protocol in select.
select
e/data[at0003]/items[at0004]/items[at0005]/value/magnitude as Magn,
e/data[at0003]/items[at0004]/items[at0005]/value/units as Units,
o/protocol[at0007]/items[at0008]/value as Protocol -- (1)
from
COMPOSITION c
CONTAINS OBSERVATION o[openEHR-EHR-OBSERVATION.multiple_events_cluster.v0]
CONTAINS POINT_EVENT e
where c/uid/value = 'f6c51223-7700-4d5b-9c6a-a976d54fe13d::91215053-854b-45b8-bb2a-3b0d255858d1::1'-
Protocol added from 'AQL 2'
The resultset from this AQL is based on the result from 'AQL 2' but you will get a permutation with all values from the protocol.
The table below shows the inital values of this result set.
| Magn | Units | Protocol |
|---|---|---|
1 |
a |
x |
1 |
a |
y |
1 |
b |
x |
1 |
b |
y |
. |
. |
. |
link:resultset_aql3.json[role=include]