AQL to retrieving data for an element with cardinality 0..*

Specifications AQL
1

Hi All,
I am having issue in AQl while retrieving data for an element with cardinality 0…*

I am using Symptom/sign screening questionnaire archetype with Specific symptom/sign as 0…* within the encounter composition.
I have inserted 3 elements with Symptom or sign name and Presence?

0th element has
Symptom or sign name = Depression
Presence = No

1st element has
Symptom or sign name = Anxiety
Presence = No

2nd element has
Symptom or sign name = Feeding/eating
Presence = Yes

Below is the AQl to retrieve all records with Symptom or sign name = Depression and Presence = Yes.
But the AQl is returning the above record as well as there is a match for Depression and Yes although it is in different indexes.

{
“q” : “SELECT e/ehr_id/value as ehrid, c/uid/value as compid, FROM ehr e CONTAINS COMPOSITION c[openEHR-EHR-COMPOSITION.encounter.v1] CONTAINS (observation o[openEHR-EHR-OBSERVATION.symptom_sign_screening.v0] ) WHERE (o/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0004]/value/value = ‘Depression’ and o/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0005]/value/value = ‘Yes’”)
}

Can somebody please correct the aql query.

2

This is not querying the ELEMENT.name but the ELEMENT.value, see the value/value at the end. If …[at0004] points to ELEMENT, then …[at0004]/name is the DV_TEXT for the name and …[at0004]/name/value is the text for the name. Though I don’t know why the “name” would be in at0004 and the value on at0005, both could be in the same element as /name and /value.

Another point is: CONTAINS (observation o[openEHR-EHR-OBSERVATION.symptom_sign_screening.v0] )

Shouldn’t that be CONTAINS OBSERVATION o[…] without the parentheses? Not sure if that changes anything.

3

Thanks for the reply
Yes, I need to compare the value of …[at0004] in combination with value of …[at0005]
Not clear why it is giving the result for an ‘or’ condition.
Also will this query check across all the indexes of the CLUSTER?
I am using EHRBase and the iterationScanDepth is set to 20

Note I tried with …[at0004]/name/value. It does not return any record.

4

Well, that’s the correct answer, right? You don’t have a “sign name = Depression and Presence = Yes” in your example.

5

About the name or value discussion, the Screening Questionnaire family of archetypes is designed to be able to code the name of the concept (in this case symptom or sign) in at0004/value, and the presence of the named concept in at0005/value.

Regarding the original question, I’m on my phone so I haven’t been able to test this, but I suspect the issue is that the AQL returns any composition which contains the OBSERVATION.symptom_sign_screening archetype, where at least one of the “Specific symptom” internal clusters contain the symptom name “Depression” and the presence “Yes”.

The Screening Questionnaire archetypes are designed to be extremely flexible for specifying predetermined questions, and this also places some restrictions on how they should be queried. The archetype Use section clearly states this. The query should probably use a much more specific path, from the relevant template.

If a more flexible at-runtime recording of the positive presence of non-predetermined symptoms is required, the CLUSTER symptom_sign archetype should be used.

1 Like
6

@siljelb yes the intention is to
get at least one of the “Specific symptom” internal clusters contain the symptom name “Depression” and the presence “Yes”.

But the problem is the composition contains three symptom names and corresponding presence as the cardinality is 0…*
eq:
index0:
symptom name : s1
presence: p1
index1:
symptom name: s2
presence: p2
index2:
symptom name : s3
presence: p3

If the aql is querying for s1 and p2, it is returning his composition. This is wrong as the composition contains s1 and p1 and not p2.

Also on:
the query should probably use a much more specific path, from the relevant template.
We have not renamed any data. So the path is same for all questions and answers. This seams to be a more generic issue while writing aql for a cluster with cardinality 0…* Is the where clause correct? Should we be using node predicate? If so how?

7

THis query does look correct to me but it would be helpful to get a copy of the template and a sample composition, if that was possible.

8

@ian.mcnicoll Sure, please find attached the template and two compositions. The first has only one Specific sign and symptom, second has three. The AQl was supposed to return only the first, But it returns both.

sample_questionnaire.en.v0.opt (42.4 KB)
sample_questionnaire-aql.json (501 Bytes)
sample_questionnaire-composition-1.json (7.0 KB)
sample_questionnaire-composition-2.json (11.9 KB)

2 Likes
9

Hi again!

The issue is that the archetype isn’t intended to be used in this way. It’s intended to be used “to create a framework for recording answers to pre-defined screening questions about symptoms or signs” (my emphasis) and “The semantics of this archetype are intentionally loose, and querying this archetype would normally only be useful or safe within the context of each specific template. In a template, each data element would usually be renamed to the specific question asked”.

See this example template.

The query here would look something like this:

SELECT
    e/ehr_id/value AS ehrid,
    c/uid/value AS compid
FROM
    EHR e
        CONTAINS COMPOSITION c[openEHR-EHR-COMPOSITION.encounter.v1]
            CONTAINS OBSERVATION o[openEHR-EHR-OBSERVATION.symptom_sign_screening.v1]
WHERE
	c/archetype_details/template_id/value = 'test_symptom_screening' AND
    o/data[at0001]/events[at0002]/data[at0003]/items[at0022 and name/value='Specific symptom/sign (1)']/items[at0005 and name/value='Does the patient have depression?']/value/value = 'Yes'
1 Like
10

Hi @siljelb , Thanks for the reply.
What you are saying is that there is no way to query for two attributes within the cluster with cardinality more than one with the same path.
What you are suggesting is put the value of at0004 as named value in at0005 so that the path to be queried is unique

Is this the limitation of the specification or implementation?

This will be the case with other archetypes also which has cardinality more than one and we want to query for specific combination of values. eg: Laboratory analyte result, get record where Analyte Name is Haemoglobin and Analyte result <10, Assuming the Laboratory analyte result has more than one cardinality for other Analyte names?

2 Likes
11

Please someone correct me if my understanding of AQL is wrong here, but:

I think the main difference here is that Laboratory analyte result is a separate CLUSTER archetype, so you can say something like this:

CONTAINS CLUSTER a[openEHR-EHR-CLUSTER.laboratory_test_analyte.v1]
WHERE a/some_path/value = ‘Haemoglobin’ AND a/some_path/value/magnitude < 10

12

TBH I am not sure myself, but this is an excellent point. Intuitively I would expect the Where clause would resolve inside the single cluster but …???

I’m just loading up the artefacts from @chaya and will report back.

1 Like
13

@siljelb
Please check this sample template
https://tools.openehr.org/designer/#/viewer/shared/Pz9zaGFyZWRJZD0xJGUzYTU3NWUwMmY0MTRkYzc5MWEyOWMxOWJlZWVkZDUy

Created a sample composition with three tests
analyte_name : analyte_result quantity
Blood : 10g/dl,
LDL cholesterol: 200mg/dl and
HDL cholesterol : 150mg/dl

for the below aql it is returning LDL as quantity is >170 while i am querying for HDL.

{
“q” : “SELECT c/uid/value as compid, a/items[at0001]/value/magnitude as magnitude FROM ehr e CONTAINS COMPOSITION c[openEHR-EHR-COMPOSITION.encounter.v1] CONTAINS (CLUSTER a[openEHR-EHR-CLUSTER.laboratory_test_analyte.v1]) WHERE a/items[at0024]/value/value = ‘HDL cholesterol’ AND a/items[at0001]/value/magnitude > 170”
}

14

Hi,

Running in the Better CDR, here is your AQL without the Where clause

{
    "meta": {
        "_type": "RESULTSET",
        "_created": "2022-11-09T15:36:01.875Z",
        "_executed_aql": " SELECT e/ehr_id/value as ehrid, c/uid/value as compid,\no/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0004]/value/value as Condition,\no/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0005]/value/value as Result\n\n  FROM ehr e \n  CONTAINS COMPOSITION c[openEHR-EHR-COMPOSITION.encounter.v1] \n  CONTAINS (observation o[openEHR-EHR-OBSERVATION.symptom_sign_screening.v0] )\n  \n\n  "
    },
    "q": " SELECT e/ehr_id/value as ehrid, c/uid/value as compid,\no/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0004]/value/value as Condition,\no/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0005]/value/value as Result\n\n  FROM ehr e \n  CONTAINS COMPOSITION c[openEHR-EHR-COMPOSITION.encounter.v1] \n  CONTAINS (observation o[openEHR-EHR-OBSERVATION.symptom_sign_screening.v0] )\n  \n\n  ",
    "columns": [
        {
            "name": "ehrid",
            "path": "/ehr_id/value"
        },
        {
            "name": "compid",
            "path": "/uid/value"
        },
        {
            "name": "Condition",
            "path": "/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0004]/value/value"
        },
        {
            "name": "Result",
            "path": "/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0005]/value/value"
        }
    ],
    "rows": [
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "a18341ee-f6a6-4098-988c-eec328d71cc7::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Depression",
            "Yes"
        ],
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "5a2be35f-6b36-402e-b332-e14e4e94162f::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Depression",
            "No"
        ],
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "5a2be35f-6b36-402e-b332-e14e4e94162f::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Anxiety",
            "No"
        ],
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "5a2be35f-6b36-402e-b332-e14e4e94162f::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Feeding/eating",
            "Yes"
        ],
        [
            "a61459fe-c977-4720-9ffd-7f401e61dc7b",
            "76ff5c2d-8710-408a-a583-24e73cc7c90c::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Altered consciousness",
            null
        ],
        [
            "a61459fe-c977-4720-9ffd-7f401e61dc7b",
            "724b89e9-5a2b-424f-ad33-44d8e9935604::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Altered consciousness",
            null
        ],
        [
            "a61459fe-c977-4720-9ffd-7f401e61dc7b",
            "cbf974c7-f5a6-467f-a720-ef840aca8b67::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Altered consciousness",
            null
        ],
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "33664b0c-6c81-43fb-b72a-6abdb1b6524c::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Anosmia (loss of taste/smell)",
            "Present"
        ],
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "33664b0c-6c81-43fb-b72a-6abdb1b6524c::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Shortness of breath",
            "Present"
        ],
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "33664b0c-6c81-43fb-b72a-6abdb1b6524c::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "D.91 description",
            "Present"
        ],
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "33664b0c-6c81-43fb-b72a-6abdb1b6524c::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "F.85 description",
            "Present"
        ],
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "33664b0c-6c81-43fb-b72a-6abdb1b6524c::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Sore throat",
            "Present"
        ],
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "58e35672-e17a-44e4-90c1-682637cd8e23::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Fever",
            null
        ],
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "58e35672-e17a-44e4-90c1-682637cd8e23::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Diarrhea",
            null
        ],
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "9109e25f-6284-4d53-8ef9-f70b16496aa7::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Fever",
            null
        ],
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "9109e25f-6284-4d53-8ef9-f70b16496aa7::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Diarrhea",
            null
        ],
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "94c8955a-0f63-4d56-9da2-ef9fc1cd9566::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Fever",
            null
        ],
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "94c8955a-0f63-4d56-9da2-ef9fc1cd9566::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Diarrhea",
            null
        ],
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "4ea3fcdc-0dfe-45e8-b153-da3f7ebb1be5::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Fever",
            null
        ],
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "4ea3fcdc-0dfe-45e8-b153-da3f7ebb1be5::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Diarrhea",
            null
        ],
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "30ce80a7-51c8-4a91-b51a-13ac6e1c5b2d::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Altered consciousness",
            null
        ],
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "2e4ee84c-1da8-4c58-b735-8b89b459b060::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Altered consciousness",
            null
        ],
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "44d07bf6-4172-4fa2-93aa-790023a36a5f::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Altered consciousness",
            null
        ],
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "a6f9c861-3483-41b3-b759-50d2f6461b35::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Altered consciousness",
            null
        ],
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "0ce89e9d-5ee0-496e-a828-c406cde2a5d2::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Altered consciousness",
            null
        ],
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "f2c1ebbf-c585-4514-94b2-df4c1ee57548::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Altered consciousness",
            null
        ]
    ]
}
  1. With WHERE o/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0004]/value/value = 'Depression' 
  "rows": [
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "a18341ee-f6a6-4098-988c-eec328d71cc7::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Depression",
            "Yes"
        ],
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "5a2be35f-6b36-402e-b332-e14e4e94162f::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Depression",
            "No"
        ]
    ]
  1. With WHERE o/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0004]/value/value = 'Depression' and o/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0005]/value/value = 'Yes' 
 "rows": [
        [
            "3e674739-950c-4b8a-976b-5aef21c618c5",
            "a18341ee-f6a6-4098-988c-eec328d71cc7::a81f47c6-a757-4e34-b644-3ccc62b4a01c::1",
            "Depression",
            "Yes"
        ]
    ]

So that looks correct to me?

1 Like
15

Just of clarity, in this archetype we have an explcit Element [at0004 | Symptom / sign name| which carries the ‘Depression’ , 'Anxiety etc (or coded equivalents) ,as @Silje said, there should be no need to use name/value in the query or data.

So @chaya - from what I have seen you have constructed the compositions and the AQL correctly. :clap:

THis feels like it may be an issue with ehrBase. I’ll have a play with that now.

It would be good if the true AQL gurus could pitch in @bna @Seref @matijap @birger.haarbrandt ??

16

@ian.mcnicoll Thanks so much.
So looks like an EHRBase issue?

I cross checked with the sql created by EHRBase for this aql.
It is creating two lateral joins, one for each where clause, hence the behavior

join lateral (
 select (ehr.xjsonb_array_elements(("ehr"."entry"."entry"#>>'{/composition[openEHR-EHR-COMPOSITION.encounter.v1],/content[openEHR-EHR-OBSERVATION.symptom_sign_screening.v0],0,/data[at0001],/events,/events[at0002],0,/data[at0003],/items[at0022]}')::jsonb)#>>'{/items[at0004],0,/value,value}')
AS var_1844082057_1546) as "array_1844082057_1545"
on true
  join lateral (
 select (ehr.xjsonb_array_elements(("ehr"."entry"."entry"#>>'{/composition[openEHR-EHR-COMPOSITION.encounter.v1],/content[openEHR-EHR-OBSERVATION.symptom_sign_screening.v0],0,/data[at0001],/events,/events[at0002],0,/data[at0003],/items[at0022]}')::jsonb)#>>'{/items[at0005],0,/value,value}')
AS var_599317576_1548) as "array_599317576_1547"
on true

When I change this to a single join lateral it gives the expected result

join lateral (
select (ehr.xjsonb_array_elements(("ehr"."entry"."entry"#>>'{/composition[openEHR-EHR-COMPOSITION.encounter.v1],/content[openEHR-EHR-OBSERVATION.symptom_sign_screening.v0],0,/data[at0001],/events,/events[at0002],0,/data[at0003],/items[at0022]}')::jsonb)#>>'{/items[at0004],0,/value,value}')
AS var_1844082057_1546,(ehr.xjsonb_array_elements(("ehr"."entry"."entry"#>>'{/composition[openEHR-EHR-COMPOSITION.encounter.v1],/content[openEHR-EHR-OBSERVATION.symptom_sign_screening.v0],0,/data[at0001],/events,/events[at0002],0,/data[at0003],/items[at0022]}')::jsonb)#>>'{/items[at0005],0,/value,value}')
AS var_599317576_1548 ) as array_599317576_1547
on true

Is it possible to resolve this?

1 Like
17

Nice!! It would be good to get confirmation from the AQL experts of the expected behaviour but if the Better approach is correct this neesd a PR to ehrBase.

As a workaround, it might be possible to do as Silje suggests and a local name override in your template, and then adjust the AQL to pick that up.

e.g.

WHERE o/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0004, ‘Depression’]/value/value = ‘Depression’
and o/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0005]/value/value = ‘Yes’

but I suspect the same issue will arise. I can’t do more tinkering today but will try some options tomorrow

18

Please post a complete composition (data) and the OPT you are using.

19

If it’s an internal issue of EHRBASE, you should report it to their GitHub with the compo and opt you are using, so they can confirm the issue and fix it.

2 Likes
20

The artefacts are here, @pablo. See above for my tests against Better CDD and ehrBase, which confirms the problem.

1 Like