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
1 Like
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.

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