AQL queries and one-many relationships

Technical (archive)
1

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

2

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

3

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

4

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 :slight_smile:

5

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

6

The contains statement is like a join. It is the association of containment
which is very useful in longitudinal records.
Cheers, Sam

7

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

8

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.

  1. 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.

  1. 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