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

Implementation Platform
1

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

2

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
2 Likes
3

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

1 Like
4

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.

1 Like
5

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

6

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

7

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

8

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

image
image999×665 114 KB

Thank you in advance!

9

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

3 Likes
10

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.

1 Like
11

@birger.haarbrandt

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

12

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
4 Likes
13

Thanks a lot for your prompt answer!