AQL query using function SUBSTRING

Specifications AQL
,
1

My current situation:
Based on the specification for the Archetype Query Languange for SUBSTRING I am testing a possibility how to perform a query by using the SUBSTRING of the attribute “name” of the template and comparing it with a defined String.

My goal:
Every score in our electronic health record may have a different templateID. For this purpose, I want to analyze the performance of AQL queries based on a naming convention, where all these Scores might start with “Score” in the attribute name of the template. Consequently I am trying to apply the following query

{
  "q": "SELECT c FROM COMPOSITION c WHERE SUBSTRING(c/name/value,0,5)='Score'",
  "offset": 0
}

My questions:

  1. Do Better’s openEHR database or Vitagroup’s EHRBase support the substring function?
  2. How do we correctly apply the substring function to successfully perform the example query above based on the REST API endpoint for /query/aql?
1 Like
2

both support LIKE. I’m not sure about SUBSTRING

Better EhrServer

Partial matching with LIKE operator

Allows matching with wildcards * and ?:

  • * stands for any string,
  • ? stands for any character.
SELECT DISTINCT c/archetype_details/template_id/value as templateName
FROM EHR e
CONTAINS COMPOSITION c
WHERE templateName LIKE "Vital*"

EhrBase (2)

LIKE​

The LIKE binary operator is used to compare a value of type string (or dates and times) against a simple pattern. The left-hand operand is an AQL identified path to a data element that is either a String or has a String representation, while the right-hand operand is a String value, representing the pattern to be matched. It returns true if the value matches the supplied pattern.

Below is an example using a simple pattern matching:

SELECT   e/ehr_id/value, c/context/start_timeFROM   EHR e      CONTAINS COMPOSITION c[openEHR-EHR-COMPOSITION.administrative_encounter.v1]         CONTAINS ADMIN_ENTRY admission[openEHR-EHR-ADMIN_ENTRY.admission.v1]WHERE   c/context/start_time LIKE '2019-0?-*'
3 Likes
3

The more precise template name is in the archetype_details.template_id.value.

Edit: Ian and I posted at the same time. Just follow the Ian’s answer.

2 Likes
4

Thank you both so much for your prompt answer. Now I understand, why I was unable to perform this request as intended. I tried to use “%” and “_” as wildcards as in SQL.

Concerning the SUBSTRING function in AQL queries, is it implemented? And if so, how do we correctly apply it?

5

SUBSTRING() is not documented as being available in either CDR but your interpretation of the openEHR docs looks correct to me.

1 Like
6

Thank you, Ian, for your feedback and clarification on the function. But fortunately, you provided a better solution than the substring, such that I was able to test it.

1 Like