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

**Category:** [Platform](https://discourse.openehr.org/c/platform-implem/7)
**Created:** 2022-03-24 11:21 UTC
**Views:** 1087
**Replies:** 12
**URL:** https://discourse.openehr.org/t/aql-query-for-compositions-that-have-been-created-updated-within-a-time-period/2458

---

## Post #1 by @Peter_Mullarkey

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

---

## Post #2 by @ian.mcnicoll

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

```
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
```

---

## Post #3 by @Peter_Mullarkey

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

---

## Post #4 by @birger.haarbrandt

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.

---

## Post #5 by @jgarciaroca

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 
```
SELECT e/compositions 
FROM EHR e[ehr_id/value='XXX']
```
This returns the information about the 4 compositions

```
SELECT e/compositions/time_created/value 
FROM EHR e[ehr_id/value='XXX']
```
Also returns 4 rows
```
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:
```
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
```
SELECT e/compositions/id/value
FROM EHR e[ehr_id/value='XXX']
WHERE e/compositions/time_created/value > '2022-03-30'
```
Error:
```
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:
```
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 
```
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

---

## Post #6 by @LauraMoral

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:

```
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

---

## Post #7 by @ian.mcnicoll

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 

```
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

---

## Post #8 by @LauraMoral

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 https://specifications.openehr.org/releases/RM/latest/common.html#_change_control_package:

![image|690x459](upload://tvs0pL0d4T9XEVFdkCtu52VlvFt.png)

Thank you in advance!

---

## Post #9 by @birger.haarbrandt

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

---

## Post #10 by @ian.mcnicoll

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.

---

## Post #11 by @Lucas

@birger.haarbrandt  

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

---

## Post #12 by @birger.haarbrandt

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

```
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
```

---

## Post #13 by @Lucas

Thanks a lot for your prompt answer!

---


**Canonical:** https://discourse.openehr.org/t/aql-query-for-compositions-that-have-been-created-updated-within-a-time-period/2458
**Original content:** https://discourse.openehr.org/t/aql-query-for-compositions-that-have-been-created-updated-within-a-time-period/2458