Hi John, I am very pleased that you kick off the discussions about AQL.
See my comments inline below.. Cheers. Chunlan > -----Original Message----- > From: Sam Heard [mailto:sam.heard at 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 > > Sam > > > > > Thanks, > > > > John Ryan-Brown > > The Australian e-Health Research Centre CSIRO ICT Centre Brisbane > > _______________________________________________ > > openEHR-technical mailing list > > openEHR-technical at openehr.org > > http://lists.chime.ucl.ac.uk/mailman/listinfo/openehr-technical ---------------------------------------------------------- Dr Chunlan Ma (Med) PhD (Health Informatics) Software Architect, Clinical Interoperability t: +61 (0)8 7127 5574 | m: 0405 139 586 f: +61 (0)8 8212 9466 | skype: chunlan_ma Ocean Informatics Pty Ltd Level 4, 25 King William street Adelaide SA 5000 http://www.oceaninformatics.com -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://lists.openehr.org/mailman/private/openehr-technical_lists.openehr.org/attachments/20090317/77d7f8c3/attachment.html>
