# AQL Optional CONTAINMENT question

**Category:** [Platform](https://discourse.openehr.org/c/platform-implem/7)
**Created:** 2020-10-02 10:57 UTC
**Views:** 930
**Replies:** 4
**URL:** https://discourse.openehr.org/t/aql-optional-containment-question/1004

---

## Post #1 by @pablo

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.

---

## Post #2 by @pablo

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.

---

## Post #3 by @ian.mcnicoll

[quote="pablo, post:2, topic:1004"]
{
“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’”
}
[/quote]

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 ?

---

## Post #4 by @pablo

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.

---

## Post #5 by @ian.mcnicoll

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)"

---


**Canonical:** https://discourse.openehr.org/t/aql-optional-containment-question/1004
**Original content:** https://discourse.openehr.org/t/aql-optional-containment-question/1004