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

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

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

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

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.

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