# AQL query for compositions that have been created/updated within a time period

**URL:** <https://discourse.openehr.org/t/aql-query-for-compositions-that-have-been-created-updated-within-a-time-period/2458>\
**Category:** Platform\
**Tags:** ehrbase\
**Created:** [24 March 2022 11:21 UTC](https://discourse.openehr.org/t/aql-query-for-compositions-that-have-been-created-updated-within-a-time-period/2458 "2022-03-24T11:21:25Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![Peter\_Mullarkey](https://discourse.openehr.org/user_avatar/discourse.openehr.org/peter_mullarkey/32/1828_2.png) [@Peter\_Mullarkey](https://discourse.openehr.org/u/Peter_Mullarkey)\
**Post date:** [24 March 2022 11:21 UTC](https://discourse.openehr.org/t/aql-query-for-compositions-that-have-been-created-updated-within-a-time-period/2458/1 "2022-03-24T11:21:25Z")

</div>

Hi all,

We are using ehrbase and are successfully adding and updating compositions using the flat format. We would like to create an AQL query that return a list of compositions that have been created or updated within a given time period. Does ehrBase support such a query?

Thanks,

Peter

---

<div class="post-metadata">

**Author:** ![ian.mcnicoll](https://discourse.openehr.org/user_avatar/discourse.openehr.org/ian.mcnicoll/32/4430_2.png) [@ian.mcnicoll](https://discourse.openehr.org/u/ian.mcnicoll)\
**Post date:** [24 March 2022 11:25 UTC](https://discourse.openehr.org/t/aql-query-for-compositions-that-have-been-created-updated-within-a-time-period/2458/2 "2022-03-24T11:25:22Z")

</div>

This works on Better, which I think was based on some original input from Ocean

```auto
SELECT v/commit_audit/time_committed as commitTime,
        c/uid as compositonId
FROM EHR e[ehr_id/value=:ehrId]
CONTAINS VERSION v
CONTAINS COMPOSITION c
WHERE c/archetype_details/template_id/value = :templateId
ORDER BY v/commit_audit/time_committed DESC

```

---

<div class="post-metadata">

**Author:** ![Peter\_Mullarkey](https://discourse.openehr.org/user_avatar/discourse.openehr.org/peter_mullarkey/32/1828_2.png) [@Peter\_Mullarkey](https://discourse.openehr.org/u/Peter_Mullarkey)\
**Post date:** [24 March 2022 11:34 UTC](https://discourse.openehr.org/t/aql-query-for-compositions-that-have-been-created-updated-within-a-time-period/2458/3 "2022-03-24T11:34:14Z")

</div>

Thanks Ian,

Unfortunately, the query fails on ehrBase with the following error;

{  
“error”: “Bad Request”,  
“message”: “Could not process query, reason:java.lang.ClassCastException: class org.ehrbase.aql.parser.AqlParser$VersionClassExprContext cannot be cast to class org.ehrbase.aql.parser.AqlParser$ArchetypedClassExprContext (org.ehrbase.aql.parser.AqlParser$VersionClassExprContext and org.ehrbase.aql.parser.AqlParser$ArchetypedClassExprContext are in unnamed module of loader org.springframework.boot.loader.LaunchedURLClassLoader @4b85612c)”  
}

---

<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:** [25 March 2022 17:54 UTC](https://discourse.openehr.org/t/aql-query-for-compositions-that-have-been-created-updated-within-a-time-period/2458/4 "2022-03-25T17:54:09Z")

</div>

Hi Peter,

AQLs on versioned data is unfortunately not yet supported in EHRbase. This will likely change later this year, but for now you are limited to data structures within compositions.

---

<div class="post-metadata">

**Author:** ![jgarciaroca](https://discourse.openehr.org/user_avatar/discourse.openehr.org/jgarciaroca/32/1663_2.png) [@jgarciaroca](https://discourse.openehr.org/u/jgarciaroca)\
**Post date:** [30 March 2022 07:39 UTC](https://discourse.openehr.org/t/aql-query-for-compositions-that-have-been-created-updated-within-a-time-period/2458/5 "2022-03-30T07:39:52Z")

</div>

I had tried that some time ago just for created compositions with no success with EHRBase as it returns more fields than expected.

I did some tests with a single EHR that has 4 different compositions: 2 created in the past and 2 created today. I wanted to retrieve the compositions created today

```auto
SELECT e/compositions 
FROM EHR e[ehr_id/value='XXX']

```

This returns the information about the 4 compositions

```auto
SELECT e/compositions/time_created/value 
FROM EHR e[ehr_id/value='XXX']

```

Also returns 4 rows

```auto
SELECT e/compositions/time_created/value, e/compositions/id/value
FROM EHR e[ehr_id/value='XXX']

```

This now returns 16 rows

So this AQL that could return created compositions:

```auto
SELECT e/compositions/time_created/value, e/compositions/id/value
FROM EHR e[ehr_id/value='XXX']
WHERE e/compositions/time_created/value > '2022-03-30'

```

Returns 8 rows (4 existing compositions and 2 that match the criteria)

If you don’t include time\_created/value EHRBase fails

```auto
SELECT e/compositions/id/value
FROM EHR e[ehr_id/value='XXX']
WHERE e/compositions/time_created/value > '2022-03-30'

```

Error:

```auto
Could not perform SQL query:org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table \"array_254765709_197\"\n Position: 412, AQL expression:SELECTe/compositions/id/valueFROMEHRe[ehr_id/value='20a5b7bf-a075-41c7-909c-b13864af727f']WHEREe/compositions/time_created/value>'2022-03-30'<EOF>, Translated SQL:select array_1146513038_195.var_1146513038_196 as \"/compositions/id/value\" from \"ehr\".\"ehr\" as \"ehr_join\" join lateral (\n select jsonb_extract_path_text(cast(ehr.xjsonb_array_elements(cast(jsonb_extract_path(cast(\"ehr\".\"js_ehr\"(\n cast(ehr_join.id as uuid), \n 'local.ehrbase.org'\n) as jsonb),'compositions') as jsonb)) as jsonb),'id','value')\n AS var_1146513038_196) as \"array_1146513038_195\" on 1 = 1 where (array_254765709_197.var_254765709_198 > '2022-03-30' and \"ehr_join\".\"id\" = 'XXX')

```

The only way to obtain the list of compositions created within a time period requires external parsing and 2 AQLs. But that just limits to created compositions, does not cover updated ones:

1st AQL query:

```auto
SELECT e/compositions/time_created/value, e/ehr_id/value
FROM EHR e
WHERE e/compositions/time_created/value > '2022-03-30',

```

Then a 2nd AQL query to obtain all the compositions from those EHRs

```auto
SELECT e/compositions
FROM EHR e
WHERE e/ehr_id/value matches {<here the list of EHRIds from the previous AQL>}

```

Then matching by time\_created

---

<div class="post-metadata">

**Author:** ![LauraMoral](https://discourse.openehr.org/user_avatar/discourse.openehr.org/lauramoral/32/5713_2.png) [@LauraMoral](https://discourse.openehr.org/u/LauraMoral)\
**Post date:** [21 August 2024 12:06 UTC](https://discourse.openehr.org/t/aql-query-for-compositions-that-have-been-created-updated-within-a-time-period/2458/6 "2024-08-21T12:06:07Z")

</div>

Hello everyone,

Regarding this conversation, we have been using this AQL in order to retrieve the information about the last updated composition version and it works fine:

```auto
SELECT 
    v/commit_audit/change_type, 
    v/uid/value, 
    v/commit_audit/time_committed 
FROM 
    EHR e[ehr_id/value='EHRid'] 
        CONTAINS VERSION v 
            CONTAINS COMPOSITION c 
WHERE 
    c/uid/value='COMPOSITIONid'

```

With this AQL, we are retrieving the information about the type of update, the version number and the time when the last change was performed. However, we were wondering if it would be possible somehow to retrieve all this information for each version of a composition, taking in account that we are using an EHRbase?

Thanks in advance

---

<div class="post-metadata">

**Author:** ![ian.mcnicoll](https://discourse.openehr.org/user_avatar/discourse.openehr.org/ian.mcnicoll/32/4430_2.png) [@ian.mcnicoll](https://discourse.openehr.org/u/ian.mcnicoll)\
**Post date:** [21 August 2024 12:27 UTC](https://discourse.openehr.org/t/aql-query-for-compositions-that-have-been-created-updated-within-a-time-period/2458/7 "2024-08-21T12:27:14Z")

</div>

HiLaura,

I think the problem here is that you are probably passing the full CompositionUID, including the version number

e.g. `dcdfa691-2827-4516-b3d9-005ff6475d07::local.ehrbase.org::1`

So that will restrict the query to just that version.

Try removing the last WHRE Clause and replace with something like

```auto
SELECT 
    v/commit_audit/change_type, 
    v/uid/value, 
    v/commit_audit/time_committed
FROM 
    EHR e[ehr_id/value=‘EHRid’]
        CONTAINS VERSION v 
            CONTAINS COMPOSITION c
WHERE 
    c/archetype_details/template_id = 'My template'

```

or however you would generally ‘identify’ the composition you are after.

It might also be possible to send the composiitonUId without the version suffix

e.g. `WHERE c/uid/value = 'dcdfa691-2827-4516-b3d9-005ff6475d07::local.ehrbase.org'` but I have not tested this

---

<div class="post-metadata">

**Author:** ![LauraMoral](https://discourse.openehr.org/user_avatar/discourse.openehr.org/lauramoral/32/5713_2.png) [@LauraMoral](https://discourse.openehr.org/u/LauraMoral)\
**Post date:** [21 August 2024 13:43 UTC](https://discourse.openehr.org/t/aql-query-for-compositions-that-have-been-created-updated-within-a-time-period/2458/8 "2024-08-21T13:43:56Z")

</div>

Hello Ian,

Thank you very much for your response.

The first solution that you propose is not the problem in my case, since I am not using the version number when running my query. For example:

> SELECT v/commit\_audit/change\_type, v/uid/value, v/commit\_audit/time\_committed  
> FROM EHR e[ehr\_id/value=‘bae96625-232b-4547-9410-372c4fd283f6’]  
> CONTAINS VERSION v CONTAINS COMPOSITION c  
> WHERE c/uid/value=‘f773ec93-9caf-42e5-bb2a-907888a98a07’ "

where I only obtained information from the latest version:

```
[
        {
            "_type": "DV_CODED_TEXT",
            "value": "modification",
            "defining_code": {
                "_type": "CODE_PHRASE",
                "terminology_id": {
                    "_type": "TERMINOLOGY_ID",
                    "value": "openehr"
                },
                "code_string": "251",
                "preferred_term": "modification"
            }
        },
        "f773ec93-9caf-42e5-bb2a-907888a98a07::ehrbase.ehrbase.org::2",
        {
            "_type": "DV_DATE_TIME",
            "value": "2024-08-20T12:55:37.056397+02:00"
        }
    ]
]

```

I tried also to filter by the template ID, but it didn’t work either, I also get only the information from the latest version of the composition.

The last option that you propose (using WHERE c/uid/value = ‘f773ec93-9caf-42e5-bb2a-907888a98a07::ehrbase.ehrbase.org’) does unfortunately not work.

I have been checking specifications, and I was wondering if it is possible, somehow, to retrieve this information, that it is described in 6.1 here [Common Information Model](https://specifications.openehr.org/releases/RM/latest/common.html#_change_control_package:)

 ![image](https://discourse.openehr.org/uploads/default/original/2X/c/cecd3440de59bd0b472baefba1b65987cfad686f.png)

Thank you in advance!

---

<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 August 2024 13:45 UTC](https://discourse.openehr.org/t/aql-query-for-compositions-that-have-been-created-updated-within-a-time-period/2458/9 "2024-08-21T13:45:55Z")

</div>

As of now, only querying on the latest version is supported in EHRbase. Queries on former versions will come eventually

---

<div class="post-metadata">

**Author:** ![ian.mcnicoll](https://discourse.openehr.org/user_avatar/discourse.openehr.org/ian.mcnicoll/32/4430_2.png) [@ian.mcnicoll](https://discourse.openehr.org/u/ian.mcnicoll)\
**Post date:** [21 August 2024 14:10 UTC](https://discourse.openehr.org/t/aql-query-for-compositions-that-have-been-created-updated-within-a-time-period/2458/10 "2024-08-21T14:10:22Z")

</div>

It is possible to access VERSIONED\_OBJECT in some CDRs AQL but not in Ehrbase(yet) AFAIK.

Since only current VERSION is supported in Ehrbase AQL for now, you will have to get multiple versions via the REST API and do some further processing.

---

<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:** [10 October 2024 18:24 UTC](https://discourse.openehr.org/t/aql-query-for-compositions-that-have-been-created-updated-within-a-time-period/2458/11 "2024-10-10T18:24:22Z")

</div>

@birger.haarbrandt

Are there any updates on how to query for commit\_audit for compositions? I am referring to your post from March 2022.

---

<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:** [10 October 2024 23:58 UTC](https://discourse.openehr.org/t/aql-query-for-compositions-that-have-been-created-updated-within-a-time-period/2458/12 "2024-10-10T23:58:32Z")

</div>

Yes, you can now do a query like the following:

```auto
SELECT
  cv/commit_audit/time_committed/value,
  cv/commit_audit/change_type/value,
  cv/commit_audit/change_type/defining_code/code_string
FROM
  VERSION cv[latest_version]
  CONTAINS COMPOSITION c
WHERE
  cv/commit_audit/time_committed > '2023-12-12'
  AND cv/commit_audit/change_type/value = 'creation'
ORDER BY
  cv/commit_audit/time_committed

```

---

<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:** [11 October 2024 12:21 UTC](https://discourse.openehr.org/t/aql-query-for-compositions-that-have-been-created-updated-within-a-time-period/2458/13 "2024-10-11T12:21:11Z")

</div>

Thanks a lot for your prompt answer!