Facing issue in AQL when queried across different templates across different archetypes

Implementation Platform
,
1

Facing issue while executing AQL, where it has two templates with different archetypes. Used same ehr_id for composition insertions.

AQL:



SELECT 
		e/ehr_id/value 
FROM ehr e CONTAINS 
	COMPOSITION c[openEHR-EHR-COMPOSITION.encounter.v1]
	CONTAINS  
            (OBSERVATION o[openEHR-EHR-OBSERVATION.problem_screening.v1] 
			OR 
            OBSERVATION o1[openEHR-EHR-OBSERVATION.symptom_sign_screening.v0])
WHERE
	(o/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0004]/value/value=‘TestProblemScreening-1’ 
	AND 
	o/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0005]/value/value=‘Yes’)
	OR
	(o1/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0004]/value/value=‘Test-signsymptom-2’ 
	AND 
	o1/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0005]/value/value =‘Yes’)"

Expected was ehr_id, but we are facing below error:

{
“error”: “Bad Request”,
“message”: “Could not perform SQL query:org.postgresql.util.PSQLException: ERROR: column alias_117828109.1 does not exist\n Position: 38, AQL expression:selecte/ehr_id/valuefromehrecontainscompositionc[openEHR-EHR-COMPOSITION.encounter.v1]contains(observationo[openEHR-EHR-OBSERVATION.problem_screening.v1]orobservationo1[openEHR-EHR-OBSERVATION.symptom_sign_screening.v0])where(o/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0004]/value/value='TestProblemScreening-1’ando/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0005]/value/value=‘Yes’)or(o1/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0004]/value/value='Test-signsymptom-2’ando1/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0005]/value/value=‘Yes’), Translated SQL:select distinct on ("/ehr_id/value") "alias_117828109"."1" from (select 1) as "alias_117828109"”
}

Please Note CDR used is ehrbase
Any help would be highly appreciated.
Thanks in Advance.

2

As usual, it is really helpful for any AQL-related question, if you can clearly state the CDR product )I know it is tagged as Ehrbase), and include an operational template plus some example compositions to help test/reproduce the issue.

3

Please format your aql content and put it into a code block. It makes it easier for others to help you.

4

AQL.json (668 Bytes)
Flat-template-2.json (1.6 KB)
Flat-template-1.json (1.9 KB)
sample_template-2.en.v0.t.json (10.2 KB)
sample_template-3.en.v0.t.json (9.8 KB)

1 Like
5

Not sure if this is discourse’s formatting but it seems it’s messing the quotes. Can you check you are using plain double or single quotes in your AQL, because those oblique quotes might be causing issues on the AQL parser.

‘Yes’ should be "Yes" or 'Yes'

6

Another thing, shouldn’t class names be in uppercase?

See Archetype Query Language (AQL)

7

Thanks but it is the .opt exports that we need, not the native JSON. The native json only contains the unique template level constraints and not he underlying archetypes. The .opt essentially ‘compiles’ these together as a run-time artefact.

If you do want to send the full design-time package of native json plus individual archetypes, then use the Template Fileset .zip format. This will work but requires the recipient to set up a new repo and generate the .opt for upload to their CDR so a bit more cumbersome.

8

sample_template-3.en.v0.opt (53.6 KB)
sample_template-2.en.v0.opt (60.1 KB)

1 Like
9

Thanks, looks like editing issue, we are using single quotes in AQL.

10

I have tested the AQL in both EhrBase and Better CDR.

I get the same error in Ehrbase and the AQL looksvalid to me.

Better CDR gives

{
    "meta": {
        "_type": "RESULTSET",
        "_created": "2023-01-31T15:28:03.616Z",
        "_executed_aql": "SELECT \n\t\te/ehr_id/value \nFROM ehr e CONTAINS \n\tCOMPOSITION c[openEHR-EHR-COMPOSITION.encounter.v1]\n\tCONTAINS  \n            (OBSERVATION o[openEHR-EHR-OBSERVATION.problem_screening.v1] \n\t\t\tOR \n            OBSERVATION o1[openEHR-EHR-OBSERVATION.symptom_sign_screening.v0])\n            WHERE\n\t(o/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0004]/value/value='TestProblemScreening-1' \n\tAND \n\to/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0005]/value/value='Yes')\n\tOR\n\t(o1/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0004]/value/value='Test-signsymptom-2' \n\tAND \n\to1/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0005]/value/value ='Yes')\n\n    "
    },
    "q": "SELECT \n\t\te/ehr_id/value \nFROM ehr e CONTAINS \n\tCOMPOSITION c[openEHR-EHR-COMPOSITION.encounter.v1]\n\tCONTAINS  \n            (OBSERVATION o[openEHR-EHR-OBSERVATION.problem_screening.v1] \n\t\t\tOR \n            OBSERVATION o1[openEHR-EHR-OBSERVATION.symptom_sign_screening.v0])\n            WHERE\n\t(o/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0004]/value/value='TestProblemScreening-1' \n\tAND \n\to/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0005]/value/value='Yes')\n\tOR\n\t(o1/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0004]/value/value='Test-signsymptom-2' \n\tAND \n\to1/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0005]/value/value ='Yes')\n\n    ",
    "columns": [
        {
            "name": "#0",
            "path": "/ehr_id/value"
        }
    ],
    "rows": [
        [
            "66943603-ace9-40e9-81b5-d99911a8e6c6"
        ],
        [
            "66943603-ace9-40e9-81b5-d99911a8e6c6"
        ]
    ]
}

which I think is correct. I’ll let others explain why 2 rows appear!!

So I would consider this to be a bug in Ehrbase and you should report it on their GH site.

11

bug in Ehrbase?

1 Like
12

Thank you @ [ian.mcnicoll]

1 Like