AQL Optional CONTAINMENT question

Implementation Platform
1

Hi all,

I have a case where I need to query something like this:

SELECT a/path1, a/path2, b/path3
FROM EHR e CONTAINS COMPOSITION c CONTAINS ACTION a CONTAINS CLUSTER b
WHERE …

But the “CLUSTER b” could not occur in the data, on that case I still need the a/path1 and a/path2 and expect b/path3 to be NULL, but I think the CONTAINS CLUSTER b will actually filter that result because there is on CLUSTER instance in the data.

How can that query be expressed in AQL to work properly?

Thanks.

2 Likes
2

Update:

This was my original query that returns an empty result because the CLUSTER checked by the CONTAINS doesn’t exists:

{
“q”: “SELECT c/uid/value, a/description[at0001]/items[at0002]/value, a/description[at0001]/items[at0049]/value, a/time, al/items[at0001]/value FROM EHR e CONTAINS COMPOSITION c CONTAINS ACTION a[openEHR-EHR-ACTION.procedure.v1] CONTAINS CLUSTER al[openEHR-EHR-CLUSTER.anatomical_location.v1] WHERE c/uid/value = ‘9009fd78-8651-404c-91fc-cc0487878eab::local.ehrbase.org::1’”
}

Removing the CONTAINS CLUSTER the query returns results, but I lose the info from the CLUSTER when there is data there:

{
“q”: “SELECT c/uid/value, a/description[at0001]/items[at0002]/value, a/description[at0001]/items[at0049]/value, a/time FROM EHR e CONTAINS COMPOSITION c CONTAINS ACTION a[openEHR-EHR-ACTION.procedure.v1] WHERE c/uid/value = ‘9009fd78-8651-404c-91fc-cc0487878eab::local.ehrbase.org::1’”
}

Then I tried to use the path from the ACTION to the CLUSTER node as a projection instead of using the CONTAINS, but still getting empty results in EHRBASE:

{
“q”: “SELECT c/uid/value, a/description[at0001]/items[at0002]/value, a/description[at0001]/items[at0049]/value, a/time, a/description[at0001]/items[openEHR-EHR-CLUSTER.anatomical_location.v1]/items[at0001]/value FROM EHR e CONTAINS COMPOSITION c CONTAINS ACTION a[openEHR-EHR-ACTION.procedure.v1] WHERE c/uid/value = ‘9009fd78-8651-404c-91fc-cc0487878eab::local.ehrbase.org::1’”
}

Not sure if this is an EHRBASE issue or if this case doesn’t work well with AQL.

3

This should work - I am pretty sure it does on ThinkEhr. Could you post the template and sample compositions somewhere and I’ll test it.

SELECT c/uid/value, a/description[at0001]/items[at0002]/value, a/description[at0001]/items[at0049]/value,
a/time, al/items[at0001]/value
FROM EHR e
CONTAINS COMPOSITION c
CONTAINS ACTION a[openEHR-EHR-ACTION.procedure.v1] OR
CONTAINS (ACTION a[openEHR-EHR-ACTION.procedure.v1] CONTAINS CLUSTER al[openEHR-EHR-CLUSTER.anatomical_location.v1]) WHERE c/uid/value = ‘9009fd78-8651-404c-91fc-cc0487878eab::local.ehrbase.org::100:

might work ?
4

Hi Ian, I’m working on the FHIR-openEHR mappings, here is the OPT in the project repo https://github.com/ehrbase/fhir-bridge/blob/develop/src/main/resources/opt/Prozedur.opt

Let me get some sample COMPOs from the network traffic.

5

This works on Better…

SELECT c/uid/value, a/description[at0001]/items[at0002]/value,
a/description[at0001]/items[at0049]/value, 
a/time, 
a/description[at0001]/items[openEHR-EHR-CLUSTER.anatomical_location.v1]/items[at0001]/value 
FROM EHR e CONTAINS COMPOSITION c CONTAINS ACTION a[openEHR-EHR-ACTION.procedure.v1] WHERE c/uid/value = '9755310a-094a-4249-afa6-d3d6cf6cfad0::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1'

THis also works

SELECT c/uid/value, a/description[at0001]/items[at0002]/value, 
a/description[at0001]/items[at0049]/value, 
a/time, 
f/items[at0001]/value 
FROM EHR e 
CONTAINS COMPOSITION c 
CONTAINS (ACTION a[openEHR-EHR-ACTION.procedure.v1] or CLUSTER f[openEHR-EHR-CLUSTER.anatomical_location.v1])
WHERE c/uid/value = '9755310a-094a-4249-afa6-d3d6cf6cfad0::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1'

but not

SELECT c/uid/value, a/description[at0001]/items[at0002]/value,
a/description[at0001]/items[at0049]/value,
a/time,
f/items[at0001]/value
FROM EHR e
CONTAINS COMPOSITION c
CONTAINS (ACTION a[openEHR-EHR-ACTION.procedure.v1]
OR (ACTION a[openEHR-EHR-ACTION.procedure.v1] and CLUSTER f[openEHR-EHR-CLUSTER.anatomical_location.v1]))
WHERE c/uid/value = ‘9755310a-094a-4249-afa6-d3d6cf6cfad0::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1’


"Query is not valid - Invalid containment, alias 'a' is used more than once! (Error code: QRY-4135)"
1 Like