# How to query for compositions where fields are not null

**URL:** <https://discourse.openehr.org/t/how-to-query-for-compositions-where-fields-are-not-null/5830>\
**Category:** AQL\
**Tags:** aql\
**Created:** [21 October 2024 14:46 UTC](https://discourse.openehr.org/t/how-to-query-for-compositions-where-fields-are-not-null/5830 "2024-10-21T14:46:49Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Lucas](https://discourse.openehr.org/user_avatar/discourse.openehr.org/lucas/32/3185_2.png) [@Lucas](https://discourse.openehr.org/u/Lucas)\
**Post date:** [21 October 2024 14:46 UTC](https://discourse.openehr.org/t/how-to-query-for-compositions-where-fields-are-not-null/5830/1 "2024-10-21T14:46:49Z")

</div>

Context:  
In SQL I frequently use the “IS NOT NULL” operator, to constrain the search results of objects, where the given field is not null and thus not empty.

I am currently trying the same thing with AQL, where I want to query for compositions which have the field “end\_time” set and does not include any results, where “end\_time” is empty and thus null - or undefined depending on the implementation.

Question:  
How do we query in AQL where the WHERE clause includes the constraint, where “end\_time” is not null, such as in the following example that does not run - but not because I query for either of these two compositions 😉 :

SELECT c1, c2  
FROM  
COMPOSITION c1 [openEHR-EHR-COMPOSITION.prescription.v0]  
OR  
COMPOSITION c2 [openEHR-EHR-COMPOSITION.prescription-cistec.v0]  
WHERE  
c1/context/end\_time IS NOT NULL  
OR  
c2/context/end\_time IS NOT NULL

---

<div class="post-metadata">

**Author:** ![birger.haarbrandt](https://discourse.openehr.org/user_avatar/discourse.openehr.org/birger.haarbrandt/32/24_2.png) [@birger.haarbrandt](https://discourse.openehr.org/u/birger.haarbrandt)\
**Post date:** [21 October 2024 18:29 UTC](https://discourse.openehr.org/t/how-to-query-for-compositions-where-fields-are-not-null/5830/2 "2024-10-21T18:29:04Z")

</div>

The good news: If I understand you correctly, the EXISTS operator should be the right one:

> **[Archetype Query Language (AQL)](https://specifications.openehr.org/releases/QUERY/latest/AQL.html#_exists)**
>
> openEHR Archetype Query Language (AQL)

The bad news: it’s not yet implemented in EHRbase. But my sense is that many users are quite eager on this, so we need to assess when this can be prioritized.

---

<div class="post-metadata">

**Author:** ![Lucas](https://discourse.openehr.org/user_avatar/discourse.openehr.org/lucas/32/3185_2.png) [@Lucas](https://discourse.openehr.org/u/Lucas)\
**Post date:** [22 October 2024 11:42 UTC](https://discourse.openehr.org/t/how-to-query-for-compositions-where-fields-are-not-null/5830/3 "2024-10-22T11:42:05Z")

</div>

Thanks a lot. I have read the AQL specification and I assumed, that the EXISTS operator only applies to archeTYPES and not individual fields.

Me personally, I would appreciate, if EHRBase could support this operator because our current EHR source code deploys the “IS NOT NULL” operation in SQL quite frequently and it is a very helpful operator to constrain search results, if no additional constraints are required - because we are too lazy 😉 .

For the implementation guides at our company, I will consequently document only AQL queries, where the EXISTS operator is not used, such that it may run on ?any? platform.

Thanks to your answer, the AQL query works now and you saved me a lot of time!

The solution would be

```auto
SELECT c1, c2
FROM
    COMPOSITION c1 [openEHR-EHR-COMPOSITION.prescription.v0]
    OR
    COMPOSITION c2 [openEHR-EHR-COMPOSITION.prescription-cistec.v0]
WHERE
    EXISTS c1/context/end_time 
    OR
    EXISTS c2/context/end_time 

```

---

<div class="post-metadata">

**Author:** ![Simon](https://discourse.openehr.org/letter_avatar_proxy/v4/letter/s/f07891/32.png) [@Simon](https://discourse.openehr.org/u/Simon)\
**Post date:** [23 October 2024 13:19 UTC](https://discourse.openehr.org/t/how-to-query-for-compositions-where-fields-are-not-null/5830/4 "2024-10-23T13:19:40Z")

</div>

Really need this …Exists needs to exist if you see what I mean…

---

<div class="post-metadata">

**Author:** ![CzarMich](https://discourse.openehr.org/user_avatar/discourse.openehr.org/czarmich/32/2996_2.png) [@CzarMich](https://discourse.openehr.org/u/CzarMich)\
**Post date:** [24 October 2024 09:52 UTC](https://discourse.openehr.org/t/how-to-query-for-compositions-where-fields-are-not-null/5830/5 "2024-10-24T09:52:47Z")

</div>

> [@Lucas](#):
>
> `c2/context/end_time`

I have struggled with trying to get results with EXISTs or NOT EXIST  
In the end I resort to work around of filtering by value:  
such as  
`WHERE c1/context/end_time /value >= '1990-01-01' OR c2/context/end_time /value >= '1990-01-01'`

---

<div class="post-metadata">

**Author:** ![Lucas](https://discourse.openehr.org/user_avatar/discourse.openehr.org/lucas/32/3185_2.png) [@Lucas](https://discourse.openehr.org/u/Lucas)\
**Post date:** [25 October 2024 11:13 UTC](https://discourse.openehr.org/t/how-to-query-for-compositions-where-fields-are-not-null/5830/6 "2024-10-25T11:13:37Z")

</div>

Hi Michael

Thank you for posting the workaround. As far as I know the operator is spelled EXISTS and it does not work if the “s” at the end is left out. Furthermore, I apply this operator on Better’s CDR. From Birger’s post in this thread, we now know, that EXISTS does not exist in EHRBase and thus only applies in Better’s CDR.

If you are on Better’s CDR try to spell EXISTS with an “S” at the end. As it is not case-sensitive “EXISTs” works, too.

I will include your workaround in the implementation guide for the developers. Thanks for that!

---

<div class="post-metadata">

**Author:** ![HHeiser](https://discourse.openehr.org/user_avatar/discourse.openehr.org/hheiser/32/4249_2.png) [@HHeiser](https://discourse.openehr.org/u/HHeiser)\
**Post date:** [10 August 2026 09:27 UTC](https://discourse.openehr.org/t/how-to-query-for-compositions-where-fields-are-not-null/5830/7 "2026-08-10T09:27:22Z")

</div>

Hi Birger,

reviving this thread to ask about the progress on the EXISTS operator.  
I found that a Feature Request already exists ([EXISTS Operator is missing · Issue #1346 · ehrbase/ehrbase · GitHub](https://github.com/ehrbase/ehrbase/issues/1346)), but apparently nothing has happened in the last 2 years.

I feel like this is an important operator that would enable many common queries. Can you provide an update on the state of development, or a timeline of implementation?

Thanks,  
Hendrik

---

<div class="post-metadata">

**Author:** ![birger.haarbrandt](https://discourse.openehr.org/user_avatar/discourse.openehr.org/birger.haarbrandt/32/24_2.png) [@birger.haarbrandt](https://discourse.openehr.org/u/birger.haarbrandt)\
**Post date:** [11 August 2026 10:52 UTC](https://discourse.openehr.org/t/how-to-query-for-compositions-where-fields-are-not-null/5830/8 "2026-08-11T10:52:40Z")

</div>

Hi @HHeiser ,

Indeed, we had it multiple time de-prioritized in our backlog as always something more desirable came up in our customer projects and took away the attention. In our Health Intelligence Platform, we were also able to compensate for the lack of such operators by adjusting the underlying stored query on the SQL level (The stored query is AQL compliant, but the processing can be enhanced).

Maybe a good time to re-visit and see when it can finally be done.