Non archetype AQL predicates

Specifications AQL
1

Hi I have a mappings list as such attached to a DV_TEXT
“mappings”: [
{
“_type”: “TERM_MAPPING”,
“match”: “=”,
“target”: {
“terminology_id”: {
“value”: “SNOMED-CT”
},
“code_string”: “345356676789”
}
},
{
“_type”: “TERM_MAPPING”,
“match”: “=”,
“target”: {
“terminology_id”: {
“value”: “UMLS”
},
“code_string”: “1234”
}
]

Is it possible to use AQL predicates over this list to get the code_string for a particular terminology. The below AQL gives the error ‘Could not perform SQL query:org.postgresql.util.PSQLException: ERROR: syntax error’

SELECT o/data[at0001]/events[at0002]/data[at0003]/items[at0077]/value/mappings[target/terminology_id/value = ‘SNOMED-CT’]
from EHR e
contains composition c [openEHR-EHR-COMPOSITION.report.v1]
contains observation o [openEHR-EHR-OBSERVATION.laboratory_test_result.v1]

2

I believe the implementation you use doesn’t support that, is not an issue of the AQL specification but of that specific implementation.

3

Hi @sangeeta.nbose,

Pablo is right, this is likely not (yet) supported in EHRbase. Feel free to open an issue in GitHub, though it might take a bit until we can address this.

4

ok… will raise an issue… thanks.

Also then any suggestions on how best to write my query ?

1 Like
5

I suspect “Best” would be subjective in this case, like most things related to code are :wink:

However, you may consider rephrasing your query as a potential workaround for this specific case. If the query you provided is not a simplified one, then all you’re interested in is accessing mappings with a particular terminology id.

The dive-into-attributes-in-predicate approach you used is syntactically and semantically valid, but as Pablo and Birger pointed out, it may not have support in all implementations.
The WHERE clause on the other hand, is more widely supported. I’d suggest giving something like this a try : (written from the top of my head, check against the aql specs please)

SELECT o/data[at0001]/events[at0002]/data[at0003]/items[at0077]/value/mappings
from EHR e
contains composition c [openEHR-EHR-COMPOSITION.report.v1]
contains observation o [openEHR-EHR-OBSERVATION.laboratory_test_result.v1]
WHERE o/data[at0001]/events[at0002]/data[at0003]/items[at0077]/value/mappings/target/terminology_id/value = ‘SNOMED-CT’

There may be some use for matches etc but in general the idea is to try to shift the criteria to WHERE clause instead of using the predicate, then using SELECT to access the value that fits the criteria by doing something like the above.

6

thanks @Seref

However , since the mappings is a list, this does not work…It works if there is only one mapping.

1 Like
7

Ah, I see. Sorry, my memory of matches was fuzzy. I thought it’d help you deal with cardinality of mappings, but it does not.

Interesting point here, at least from my p.o.v. How should the path in WHEREclause work to support your requirement? Thanks for raising it :slight_smile:

8

HI @birger.haarbrandt since I am using Ehrbase right now, checking if there any suggestions on how to write this query, with a list of mappings.

9

Not sure if I understand the missing part. Would post-processing of the returned list be an option if filtering on predicates does not work?

Instead of “=”, you should be able to use the “IN” operator to filter on arrays.

10

Hi Birger. Is that an alternative to matches ? I mentioned matches for IN semantics actually. Did I miss IN in Aql specs or is it an EhrBase specific feature/keyword?

11

HI … My goal is to save multiple terminology codes for a DV_TEXT. I was able to do that, using a list of term mappings (thanks to your sample code). Now I am trying to use AQL to access the individual mapping based on the terminology (SNOMED or UMLS). However the individual items in the mappings list do not have an RM path. (Please correct me if I am wrong in my understanding). I was hoping to use the archetype predicate, but as you clarified, that is not possible.

I will use post processing of the returned list. Thanks

12

I think this is accurate that you cannot use the predicate at the moment. We will surely add the capability in the future!

13

@Seref I think the current AQL processor, when it finds an IN, it translates that to SQL directly, that is why @birger.haarbrandt mentioned that. This is not part of the spec or anything, is just how EHRBASE was implemented, I believe it’s a momentary hack.

14

Thanks Pablo,
I appreciate the clarification