# AQL queries and one-many relationships **Category:** [Technical (archive)](https://discourse.openehr.org/c/technical-archive/156) **Created:** 2009-02-24 06:23 UTC **Views:** 6 **Replies:** 7 **URL:** https://discourse.openehr.org/t/aql-queries-and-one-many-relationships/14877 --- ## Post #1 by @john.ryan-brown Hello\. I am currently investigating AQL and would like to know how the following queries would be answered by a conformant AQL query engine\. I understand that the structure of AQL query results are not standardized yet in the AQL specifications, so if the spec cannot give a definitive answer what would Ocean's AQL query engine implementation do? 1\. SELECT       o/data\[at0001\]/events\[at0031\]/data\[at0003\]/items\[at0004\]/value AS PosturalChangeSystolic,       o/data\[at0001\]/events\[at1004\]/data\[at0003\]/items\[at0004\]/value AS ParadoxSystolic     FROM       EHR       CONTAINS COMPOSITION c \[openEHR\-EHR\-COMPOSITION\.encounter\.v1\]       CONTAINS OBSERVATION o \[openEHR\-EHR\-OBSERVATION\.blood\_pressure\.v1\]     WHERE       o/data\[at0001\]/events\[at0031\]/data\[at0003\]/items\[at0004\]/value/value >= 140 OR       o/data\[at0001\]/events\[at1004\]/data\[at0003\]/items\[at0004\]/value/value >= 140 This query attempts to find all Systolic readings for Paradox and Postural Change blood pressure events where the Systolic reading for either is >= 140\. As there is a one\-to\-many relationship between a blood pressure observation and both Paradox and Postural Change events, how should the query be processed? If an observation has three Postural Change events where Systolic >= 140, and four such Paradox events, would the query return twelve rows \(using my relational database thinking\)\. Or would one row be returned, having two "lists" \(with three and four members respectively\) of Systolic readings? 2\. SELECT o     FROM       EHR       CONTAINS COMPOSITION c \[openEHR\-EHR\-COMPOSITION\.encounter\.v1\]       CONTAINS OBSERVATION o \[openEHR\-EHR\-OBSERVATION\.blood\_pressure\.v1\]     WHERE       o/data\[at0001\]/events\[at0031\]/data\[at0003\]/items\[at0004\]/value/value >= 140 OR       o/data\[at0001\]/events\[at1004\]/data\[at0003\]/items\[at0004\]/value/value >= 140 This query is the same as the previous one, except that it returns the whole observation\. It seems to me that all readings should be returned, regardless of their systolic values? 3\. SELECT o     FROM       EHR       CONTAINS COMPOSITION c \[openEHR\-EHR\-COMPOSITION\.encounter\.v1\]       CONTAINS OBSERVATION o \[openEHR\-EHR\-OBSERVATION\.blood\_pressure\.v1\]     WHERE       o/data\[at0001\]/events\[at0031\]/data\[at0003\]/items\[at0004\]/value/value >= 140 AND       o/data\[at0001\]/events\[at1004\]/data\[at0003\]/items\[at0004\]/value/value >= 140 This query is the same as the previous one, except that the OR has been changed to an AND\. It could be argued that only the Paradox and Postural Change events with a Systolic reading >= 140 should be returned, but it could also be argued that all readings should be returned, as the whole observation has been selected\. The problem I have is how to treat queries which have in the WHERE clause a path expression that traverses through a 1:n relationship\. In trying to think through the semantics of such queries, I come up with ambiguities\. In a relational query \(i\.e\. SQL\) the "equivalent" path expression would have to be expressed as a join between tables in the FROM clause, thus removing the ambiguities\. Am I missing something or are my concerns relevant? If so, how does the spec/Ocean implementation address them? Thanks, John Ryan\-Brown The Australian e\-Health Research Centre CSIRO ICT Centre Brisbane --- ## Post #2 by @Greg_Caulton My assumption was that the 'contains openEHR\-EHR\-COMPOSITION\.encounter\.v1' will restrict the result set to be in this case blood pressure observations that were recorded on an encounter note \(or any document identified as such\)\. If I make up an archetype called assessment you could have the following to get blood pressure results from two different document types \- not sure if they intention was for all assessments to be designated as encounter notes though \- perhaps\. SELECT o FROM EHR CONTAINS COMPOSITION c \[openEHR\-EHR\-COMPOSITION\.encounter\.v1\]      CONTAINS OBSERVATION o \[openEHR\-EHR\-OBSERVATION\.blood\_pressure\.v1\] AND CONTAINS COMPOSITION a \[openEHR\-EHR\-COMPOSITION\.assessment\.v1\]      CONTAINS OBSERVATION o2 \[openEHR\-EHR\-OBSERVATION\.blood\_pressure\.v1\] Greg http://www.patientos.org --- ## Post #3 by @john.ryan-brown Thanks for your respose Greg\. I'm not really concerned about the details of specific archetypes \- I just used the ubiquitous blood pressure one because that's the one used in a lot of the example documentation\. My question is more about the how AQL should handle querying data that conforms to archetypes that contain one or more one\-to\-many relationships\. John --- ## Post #4 by @Greg_Caulton Sorry, I was trying to use an example to explain that in SQL one would have a cartesian join if you have select    t1\.\* from t1, t2 but in AQL the examples I have seen suggest that select    o from c1, o1 would be an implict join I'll leave the AQL discussions to someone more versed with it :\-\) --- ## Post #5 by @system Hi John I am not sure that this has gone further\.\.\.\. > Hello\. I am currently investigating AQL and would like to know how the > following queries would be answered by a conformant AQL query engine\. I > understand that the structure of AQL query results are not standardized > yet in the AQL specifications, so if the spec cannot give a definitive > answer what would Ocean's AQL query engine implementation do? > > 1\. SELECT >       o/data\[at0001\]/events\[at0031\]/data\[at0003\]/items\[at0004\]/value AS > PosturalChangeSystolic, >       o/data\[at0001\]/events\[at1004\]/data\[at0003\]/items\[at0004\]/value AS > ParadoxSystolic >     FROM >       EHR >       CONTAINS COMPOSITION c \[openEHR\-EHR\-COMPOSITION\.encounter\.v1\] >       CONTAINS OBSERVATION o \[openEHR\-EHR\- > OBSERVATION\.blood\_pressure\.v1\] >     WHERE > > o/data\[at0001\]/events\[at0031\]/data\[at0003\]/items\[at0004\]/value/value >= > 140 OR > > o/data\[at0001\]/events\[at1004\]/data\[at0003\]/items\[at0004\]/value/value >= > 140 > > This query attempts to find all Systolic readings for Paradox and > Postural Change blood pressure events where the Systolic reading for > either is >= 140\. \[Sam Heard\] These would usually be 0\-30 or so but\.\.\.\. As there is a one\-to\-many relationship between a > blood pressure observation and both Paradox and Postural Change events, > how should the query be processed? If an observation has three Postural > Change events where Systolic >= 140, and four such Paradox events, > would the query return twelve rows \(using my relational database > thinking\)\. Or would one row be returned, having two "lists" \(with three > and four members respectively\) of Systolic readings? \[Sam Heard\] In the Ocean environment we flatten the response \(ie one row\)\. > 2\. SELECT o >     FROM >       EHR >       CONTAINS COMPOSITION c \[openEHR\-EHR\-COMPOSITION\.encounter\.v1\] >       CONTAINS OBSERVATION o \[openEHR\-EHR\- > OBSERVATION\.blood\_pressure\.v1\] >     WHERE > > o/data\[at0001\]/events\[at0031\]/data\[at0003\]/items\[at0004\]/value/value >= > 140 OR > > o/data\[at0001\]/events\[at1004\]/data\[at0003\]/items\[at0004\]/value/value >= > 140 > > This query is the same as the previous one, except that it returns the > whole observation\. It seems to me that all readings should be returned, > regardless of their systolic values? \[Sam Heard\] Yes, you are selecting all observations that include a paradox or postural change of > 140 \(very rare\!\) > 3\. SELECT o >     FROM >       EHR >       CONTAINS COMPOSITION c \[openEHR\-EHR\-COMPOSITION\.encounter\.v1\] >       CONTAINS OBSERVATION o \[openEHR\-EHR\- > OBSERVATION\.blood\_pressure\.v1\] >     WHERE > > o/data\[at0001\]/events\[at0031\]/data\[at0003\]/items\[at0004\]/value/value >= > 140 AND > > o/data\[at0001\]/events\[at1004\]/data\[at0003\]/items\[at0004\]/value/value >= > 140 > > This query is the same as the previous one, except that the OR has been > changed to an AND\. It could be argued that only the Paradox and > Postural Change events with a Systolic reading >= 140 should be > returned, but it could also be argued that all readings should be > returned, as the whole observation has been selected\. > \[Sam Heard\] Again \- the whole observation that meets both criteria should be returned\. > The problem I have is how to treat queries which have in the WHERE > clause a path expression that traverses through a 1:n relationship\. In > trying to think through the semantics of such queries, I come up with > ambiguities\. In a relational query \(i\.e\. SQL\) the "equivalent" path > expression would have to be expressed as a join between tables in the > FROM clause, thus removing the ambiguities\. > > Am I missing something or are my concerns relevant? If so, how does the > spec/Ocean implementation address them? \[Sam Heard\] Hope that helps\. Sam --- ## Post #6 by @system The contains statement is like a join\. It is the association of containment which is very useful in longitudinal records\. Cheers, Sam --- ## Post #7 by @system Hi John, I am very pleased that you kick off the discussions about AQL\. See my comments inline below\.\. Cheers\. Chunlan > From: Sam Heard \[mailto:sam.heard@oceaninformatics.com] > Sent: Monday, 16 March 2009 5:36 PM > To: 'For openEHR technical discussions' > Cc: 'Chunlan Ma' > Subject: RE: AQL queries and one\-many relationships > > Hi John > > I am not sure that this has gone further\.\.\.\. > > Hello\. I am currently investigating AQL and would like to know how > the > > following queries would be answered by a conformant AQL query engine\. > I > > understand that the structure of AQL query results are not > standardized > > yet in the AQL specifications, so if the spec cannot give a > definitive > > answer what would Ocean's AQL query engine implementation do? > > > > 1\. SELECT > > o/data\[at0001\]/events\[at0031\]/data\[at0003\]/items\[at0004\]/value > AS > > PosturalChangeSystolic, > > o/data\[at0001\]/events\[at1004\]/data\[at0003\]/items\[at0004\]/value > AS > > ParadoxSystolic > > FROM > > EHR > > CONTAINS COMPOSITION c \[openEHR\-EHR\-COMPOSITION\.encounter\.v1\] > > CONTAINS OBSERVATION o \[openEHR\-EHR\- > > OBSERVATION\.blood\_pressure\.v1\] > > WHERE > > > > > o/data\[at0001\]/events\[at0031\]/data\[at0003\]/items\[at0004\]/value/value >= > > 140 OR > > > > > o/data\[at0001\]/events\[at1004\]/data\[at0003\]/items\[at0004\]/value/value >= > > 140 > > > > This query attempts to find all Systolic readings for Paradox and > > Postural Change blood pressure events where the Systolic reading for > > either is >= 140\. > > \[Sam Heard\] These would usually be 0\-30 or so but\.\.\.\. > > As there is a one\-to\-many relationship between a > > blood pressure observation and both Paradox and Postural Change > events, > > how should the query be processed? If an observation has three > Postural > > Change events where Systolic >= 140, and four such Paradox events, > > would the query return twelve rows \(using my relational database > > thinking\)\. Or would one row be returned, having two "lists" \(with > three > > and four members respectively\) of Systolic readings? > > \[Sam Heard\] In the Ocean environment we flatten the response \(ie one > row\)\. > \[Chunlan Ma\] Archetyped data is in hierarchical structure, rather than relational structure\. However, people normally like to represent all query results in a table, i\.e\. using relational table to represent hierarchical data\. We did a project and we experienced exactly the same issue\. That's why Sam mentioned that we flatten the results to one table at the end\. A flattened table is the final results \(just for meeting user's requirements\), but our internal structure is a result set with embedded result sets\. For your particular example, if you want to represent all systolic readings for Paradox and Postural change bp in a single table, then the table should be 12 rows\. This works just like SQL join\. You may argue that this result is not correct, but it is because the chosen structure is not correct\. Paradox and postural change doesn't have any relationships\. Your second option, a row with a list of Paradox systolic and a list of Postural change systolic, this structure represents the genuine data, but may work for this particular scenario, but not others, and you may normally need further process as well\. Ideally, we do need a very generic result model that is able to represent the AQL query results without any confusion and side effects\. > > > > > > 2\. SELECT o > > FROM > > EHR > > CONTAINS COMPOSITION c \[openEHR\-EHR\-COMPOSITION\.encounter\.v1\] > > CONTAINS OBSERVATION o \[openEHR\-EHR\- > > OBSERVATION\.blood\_pressure\.v1\] > > WHERE > > > > > o/data\[at0001\]/events\[at0031\]/data\[at0003\]/items\[at0004\]/value/value >= > > 140 OR > > > > > o/data\[at0001\]/events\[at1004\]/data\[at0003\]/items\[at0004\]/value/value >= > > 140 > > > > This query is the same as the previous one, except that it returns > the > > whole observation\. It seems to me that all readings should be > returned, > > regardless of their systolic values? > > \[Sam Heard\] Yes, you are selecting all observations that include a > paradox > or postural change of > 140 \(very rare\!\) \[Chunlan Ma\] Agree with Sam\. From the AQL definition, what you want to return is an observation which has at least a systolic value >=140 for either Paradox or Postural change\. Certainly, the other readings within this observation are also included, they cannot be filtered\. If you only want the events with systolic >=140, then you need to change your select clause pointing to events, then you'll face to the same issue in your first example\. > > > > > > 3\. SELECT o > > FROM > > EHR > > CONTAINS COMPOSITION c \[openEHR\-EHR\-COMPOSITION\.encounter\.v1\] > > CONTAINS OBSERVATION o \[openEHR\-EHR\- > > OBSERVATION\.blood\_pressure\.v1\] > > WHERE > > > > > o/data\[at0001\]/events\[at0031\]/data\[at0003\]/items\[at0004\]/value/value >= > > 140 AND > > > > > o/data\[at0001\]/events\[at1004\]/data\[at0003\]/items\[at0004\]/value/value >= > > 140 > > > > This query is the same as the previous one, except that the OR has > been > > changed to an AND\. It could be argued that only the Paradox and > > Postural Change events with a Systolic reading >= 140 should be > > returned, but it could also be argued that all readings should be > > returned, as the whole observation has been selected\. > > > \[Sam Heard\] Again \- the whole observation that meets both criteria > should be > returned\. \[Chunlan Ma\] This AQL returns the entire observation that contains systolic value >=140 for both Paradox and Postural change\. > > > > The problem I have is how to treat queries which have in the WHERE > > clause a path expression that traverses through a 1:n relationship\. > In > > trying to think through the semantics of such queries, I come up with > > ambiguities\. In a relational query \(i\.e\. SQL\) the "equivalent" path > > expression would have to be expressed as a join between tables in the > > FROM clause, thus removing the ambiguities\. > > > > Am I missing something or are my concerns relevant? If so, how does > the > > spec/Ocean implementation address them? > \[Sam Heard\] Hope that helps\. \[Chunlan Ma\] I completely understand where your questions come from\. I had exactly the same questions before\. Thomas and our team had some discussions about standardizing AQL result model\. We are in the process at the moment\. Hopefully, a draft will be published in near future\. Chunlan --- ## Post #8 by @system Hi John, I am very pleased that you kick off the discussions about AQL. See my comments inline below.. Cheers. Chunlan > From: Sam Heard [[mailto:sam.heard@oceaninformatics.com](mailto:sam.heard@oceaninformatics.com)] > Sent: Monday, 16 March 2009 5:36 PM > To: 'For openEHR technical discussions' > Cc: 'Chunlan Ma' > Subject: RE: AQL queries and one-many relationships > > Hi John > > I am not sure that this has gone further.... > > Hello. I am currently investigating AQL and would like to know how > the > > following queries would be answered by a conformant AQL query engine. > I > > understand that the structure of AQL query results are not > standardized > > yet in the AQL specifications, so if the spec cannot give a > definitive > > answer what would Ocean's AQL query engine implementation do? > > > > 1. SELECT > > o/data[at0001]/events[at0031]/data[at0003]/items[at0004]/value > AS > > PosturalChangeSystolic, > > o/data[at0001]/events[at1004]/data[at0003]/items[at0004]/value > AS > > ParadoxSystolic > > FROM > > EHR > > CONTAINS COMPOSITION c [openEHR-EHR-COMPOSITION.encounter.v1] > > CONTAINS OBSERVATION o [openEHR-EHR- > > OBSERVATION.blood_pressure.v1] > > WHERE > > > > > o/data[at0001]/events[at0031]/data[at0003]/items[at0004]/value/value > >= > > 140 OR > > > > > o/data[at0001]/events[at1004]/data[at0003]/items[at0004]/value/value > >= > > 140 > > > > This query attempts to find all Systolic readings for Paradox and > > Postural Change blood pressure events where the Systolic reading for > > either is >= 140. > > [Sam Heard] These would usually be 0-30 or so but.... > > As there is a one-to-many relationship between a > > blood pressure observation and both Paradox and Postural Change > events, > > how should the query be processed? If an observation has three > Postural > > Change events where Systolic >= 140, and four such Paradox events, > > would the query return twelve rows (using my relational database > > thinking). Or would one row be returned, having two "lists" (with > three > > and four members respectively) of Systolic readings? > > [Sam Heard] In the Ocean environment we flatten the response (ie one > row). > [Chunlan Ma] Archetyped data is in hierarchical structure, rather than relational structure. However, people normally like to represent all query results in a table, i.e. using relational table to represent hierarchical data. We did a project and we experienced exactly the same issue. That's why Sam mentioned that we flatten the results to one table at the end. A flattened table is the final results (just for meeting user's requirements), but our internal structure is a result set with embedded result sets. For your particular example, if you want to represent all systolic readings for Paradox and Postural change bp in a single table, then the table should be 12 rows. This works just like SQL join. You may argue that this result is not correct, but it is because the chosen structure is not correct. Paradox and postural change doesn't have any relationships. Your second option, a row with a list of Paradox systolic and a list of Postural change systolic, this structure represents the genuine data, but may work for this particular scenario, but not others, and you may normally need further process as well. Ideally, we do need a very generic result model that is able to represent the AQL query results without any confusion and side effects. > > > > > > 2. SELECT o > > FROM > > EHR > > CONTAINS COMPOSITION c [openEHR-EHR-COMPOSITION.encounter.v1] > > CONTAINS OBSERVATION o [openEHR-EHR- > > OBSERVATION.blood_pressure.v1] > > WHERE > > > > > o/data[at0001]/events[at0031]/data[at0003]/items[at0004]/value/value > >= > > 140 OR > > > > > o/data[at0001]/events[at1004]/data[at0003]/items[at0004]/value/value > >= > > 140 > > > > This query is the same as the previous one, except that it returns > the > > whole observation. It seems to me that all readings should be > returned, > > regardless of their systolic values? > > [Sam Heard] Yes, you are selecting all observations that include a > paradox or postural change of > 140 (very rare!) [Chunlan Ma] Agree with Sam. From the AQL definition, what you want to return is an observation which has at least a systolic value >=140 for either Paradox or Postural change. Certainly, the other readings within this observation are also included, they cannot be filtered. If you only want the events with systolic >=140, then you need to change your select clause pointing to events, then you'll face to the same issue in your first example. > > > > > > > 3. SELECT o > > FROM > > EHR > > CONTAINS COMPOSITION c [openEHR-EHR-COMPOSITION.encounter.v1] > > CONTAINS OBSERVATION o [openEHR-EHR- > > OBSERVATION.blood_pressure.v1] > > WHERE > > > > > o/data[at0001]/events[at0031]/data[at0003]/items[at0004]/value/value > >= > > 140 AND > > > > > o/data[at0001]/events[at1004]/data[at0003]/items[at0004]/value/value > >= > > 140 > > > > This query is the same as the previous one, except that the OR has > been > > changed to an AND. It could be argued that only the Paradox and > > Postural Change events with a Systolic reading >= 140 should be > > returned, but it could also be argued that all readings should be > > returned, as the whole observation has been selected. > > > [Sam Heard] Again - the whole observation that meets both criteria > should be returned. [Chunlan Ma] This AQL returns the entire observation that contains systolic value >=140 for both Paradox and Postural change. > > > > > The problem I have is how to treat queries which have in the WHERE > > clause a path expression that traverses through a 1:n relationship. > In > > trying to think through the semantics of such queries, I come up > > with ambiguities. In a relational query (i.e. SQL) the "equivalent" > > path expression would have to be expressed as a join between tables > > in the FROM clause, thus removing the ambiguities. > > > > Am I missing something or are my concerns relevant? If so, how does > the > > spec/Ocean implementation address them? > [Sam Heard] Hope that helps. [Chunlan Ma] I completely understand where your questions come from. I had exactly the same questions before. Thomas and our team had some discussions about standardizing AQL result model. We are in the process at the moment. Hopefully, a draft will be published in near future. Chunlan --- **Canonical:** https://discourse.openehr.org/t/aql-queries-and-one-many-relationships/14877 **Original content:** https://discourse.openehr.org/t/aql-queries-and-one-many-relationships/14877