# AQL to retrieving data for an element with cardinality 0..* **Category:** [AQL](https://discourse.openehr.org/c/aql/43) **Created:** 2022-11-09 04:32 UTC **Views:** 1353 **Replies:** 50 **URL:** https://discourse.openehr.org/t/aql-to-retrieving-data-for-an-element-with-cardinality-0/3153 --- ## Post #1 by @chaya 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. --- ## Post #2 by @pablo [quote="chaya, post:1, topic:3153"] o/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items[at0004]/value/value = ‘Depression [/quote] 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. --- ## Post #3 by @chaya [quote="pablo, post:2, topic:3153"] …[at0004]/name/value is the text for the name. [/quote] 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. --- ## Post #4 by @damoca [quote="chaya, post:3, topic:3153"] Note I tried with …[at0004]/name/value. It does not return any record. [/quote] Well, that's the correct answer, right? You don't have a "sign name = Depression and Presence = Yes" in your example. --- ## Post #5 by @siljelb 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. --- ## Post #6 by @chaya @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? --- ## Post #7 by @ian.mcnicoll 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. --- ## Post #8 by @chaya @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|attachment](upload://fxg4VRkj6TJRHHTxdIJzcf9biV1.opt) (42.4 KB) [sample_questionnaire-aql.json|attachment](upload://ldnHilADNhIG7g0u2EVIgH0mQAg.json) (501 Bytes) [sample_questionnaire-composition-1.json|attachment](upload://8lpqtLWxcCX831MSTlYJ6q5f1ab.json) (7.0 KB) [sample_questionnaire-composition-2.json|attachment](upload://26oMWMEgLvL1GuCXQULbw4qDxB3.json) (11.9 KB) --- ## Post #9 by @siljelb 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](https://tools.openehr.org/designer/#/viewer/shared/Pz9zaGFyZWRJZD0xJDRmMTE4MDNjYjI5MjRiNjZiOTcwMTM4OGNiNjQzNzY0). 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' ``` --- ## Post #10 by @chaya 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? --- ## Post #11 by @siljelb [quote="chaya, post:10, topic:3153"] : Laboratory analyte result, get record where Analyte Name is Haemoglobin and Analyte result <10 [/quote] 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 --- ## Post #12 by @ian.mcnicoll 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. --- ## Post #13 by @chaya @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" } --- ## Post #14 by @ian.mcnicoll 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 ] ] } ``` 2. 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" ] ] ``` 3. 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? --- ## Post #15 by @ian.mcnicoll 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 ?? --- ## Post #16 by @chaya [quote="ian.mcnicoll, post:14, topic:3153"] here is your AQL without the Where clause [/quote] @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? --- ## Post #17 by @ian.mcnicoll 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 --- ## Post #18 by @pablo [quote="chaya, post:3, topic:3153"] Note I tried with …[at0004]/name/value. It does not return any record. [/quote] Please post a complete composition (data) and the OPT you are using. --- ## Post #19 by @pablo 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. --- ## Post #20 by @ian.mcnicoll https://discourse.openehr.org/t/aql-to-retrieving-data-for-an-element-with-cardinality-0/3153/8?u=ian.mcnicoll The artefacts are here, @pablo. See above for my tests against Better CDD and ehrBase, which confirms the problem. --- ## Post #21 by @siljelb Setting aside the fact that the specific archetype in question in this topic isn't designed to be templated in this way: Does AQL have a mechanism to specify that two (or more) parts of the `WHERE` clause should operate on the same instance of an internal cluster of an archetype? Otherwise, I would think it would return any instance of the immediate parent archetype where any instance of the cluster corresponds to the first part of the `WHERE` clause, and any, potentially other, instance of the cluster corresponds to the secord part of the `WHERE` clause? --- ## Post #22 by @chaya @pablo yes, raised a bug in EHRBase GitHub --- ## Post #23 by @Seref [quote="siljelb, post:21, topic:3153"] Does AQL have a mechanism to specify that two (or more) parts of the `WHERE` clause should operate on the same instance of an internal cluster of an archetype? [/quote] My expectation from an AQL implementation would be that this is done by using a path from/including the cluster. As in ``` ... CONTAINS CLUSTER cls[...] ... ... WHERE cls/..../foo0 = 'bar0' and cls/.../foo1 = 'bar1' ``` even if there are multiple `CLUSTER`s in a `COMPOSITION` the implicit meaning of the above is get me the whatever (`SELECT`) only if the `COMPOSITION` containing it has a `CLUSTER` such that it satisfies these 2 conditions (expressed above in `WHERE` using `and`). Most query languages work on this semantics, that the alias `cls` refers to the same instance of data everywhere in the query and if you provide `n` constraints then it must satisfy them all. Now how a vendor interprets the semantics of the syntax I used above is up to them, so I qualified my explanation with "My expectation" --- ## Post #24 by @damoca I think that the current AQL specifications do not mention how to deal with paths including objects with multiple occurrences in the middle. As you say, we could have two different interpretations: 1. Each match of the full path is processed as a single valid result, and it is combined with any other matched results. 2. The match of the path with existing instances breaks when an object with multiple occurrences is found. For example, in the Symptom example, the path ``` o/data[at0001]/events[at0002]/data[at0003]/items[at0022]/items ``` includes an object with multiple occurrences (the at0022 CLUSTER). How do we combine the results? I suspect we can make explicit this behavior using the current AQL syntax, using the containment definition in the FROM clause. In theory, it is not needed to have it defined as an independent archetype. So, this shoulb be a valid AQL: ``` SELECT 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] CONTAINS CLUSTER c WHERE c/items[at0004]/value/value='Depression' AND c/items[at0005]/value/value='Yes' ``` Observe that here we will have a problem if the OBSERVATION archetype contains different internal CLUSTERs. We should be able to differentiate the specific CLUSTER we want to work with. It could be done with the following expression: ``` -- According to the specifications, this is a perfectly valid predicate -- although I have never seen it in use CONTAINS CLUSTER c[at0022] ``` or it could be done at the WHERE clause: ``` -- I think I've never seen this kind of filter but, again, it seems syntactically valid WHERE c[at0022] AND c/items[at0004]/value/value='Depression' AND c/items[at0005]/value/value='Yes' ``` I don't know if any of those possibilities is currently supported by implementations. --- ## Post #25 by @siljelb [quote="damoca, post:24, topic:3153"] Observe that here we will have a problem if the OBSERVATION archetype contains different internal CLUSTERs. We should be able to differentiate the specific CLUSTER we want to work with. It could be done with the following expression: ``` -- According to the specifications, this is a perfectly valid predicate -- although I have never seen it in use CONTAINS CLUSTER c[at0022] ``` or it could be done at the WHERE clause: ``` -- I think I've never seen this kind of filter but, again, it seems syntactically valid WHERE c[at0022] AND c/items[at0004]/value/value='Depression' AND c/items[at0005]/value/value='Yes' ``` I don’t know if any of those possibilities is currently supported by implementations. [/quote] Yep, this is what I was wondering about. Wouldn't this be needed to be able to do what @chaya wanted? --- ## Post #26 by @ian.mcnicoll @Seref - the issue here is that the Cluster concerned is 'internal', and not an independent CLUSTER archetype. I'm not aware of ant current implementations allowing CONTAINS on non-root nodes e.g. CONTAINS CLUSTER cls [at002] as per yours and @siljelb suggestions. I'll try to test the behaviour when the CONTAINS is applied at CLUSTER archetype level (lab analytes being a good example). Does the Ocean CDR support this, and if not, what would be your expectation of the behaviour of the query that lacks an explicit 'contains cluster' statement. Right now it looks as if Better CDR 'assumes' that a default contains exists i.e that if a cluster is referred to in the where clause, there conditions apply to each cluster instance, not across every instance That feels to be a reasonable assumption for clinical data but appreciate that might not align with a technical querying perspective. OTOH ehrBase seems to assume the absence of an explicit CONTAINS (support for which may not exist, at least on non root nodes). That may be technically more correct but I suspect is unhelpful in the majority of clinical querying. This is quite an important topic so we should definitely discuss ASAP in SEC to provide clear guidance. --- ## Post #27 by @chaya @damoca Thanks. True, I had tried node predicate with name/value criterion. But looks like it is not supported by EHRBase. --- ## Post #28 by @chaya Thanks @ian.mcnicoll This is a major issue for us and a quick resolution would help. --- ## Post #29 by @ian.mcnicoll Did you use the approach that @Sijle suggested? i.e apply the rename to the presence node? - and can you share your original template as a template fileset as this makes it much easier to play around with? --- ## Post #30 by @ian.mcnicoll I would say at least according to @Seref 's advice, that is definitely wrong. There is an explicit CONTAINS CLUSTER statement that, at least in Seref's view, should cause the where clause to applied internally within that cluster instance only. I'm less clear what should happen if there is no explicit CONTAINS --- ## Post #31 by @ian.mcnicoll > Otherwise, I would think it would return any instance of the immediate parent archetype where any instance of the cluster corresponds to the first part of the `WHERE` clause, and any, potentially other, instance of the cluster corresponds to the secord part of the `WHERE` clause? I think that is the key question and while I understand that might be technically correct, I think it is pretty counter-intuitive and I'm struggling to think of examples where it should apply. If we removed CONTAINS clauses and performed full path queries, would we expect cross-instance container queries at let's say OBSERVATION level on systolic and diastolic elements? --- ## Post #32 by @Seref [quote="ian.mcnicoll, post:26, topic:3153"] the issue here is that the Cluster concerned is ‘internal’, and not an independent CLUSTER archetype. [/quote] That's irrelevant at the data level. The `CLUSTER` is a data item sitting in a `COMPOSITION` It's semantics at the modelling level do not exist at the data level. You can only stick a cluster archetype where it's allowed in the RM, so the personal history of the CLUSTER makes no difference (at least to me) :) Happy to hear if you disagree (or maybe not...) [quote="ian.mcnicoll, post:26, topic:3153"] I’m not aware of ant current implementations allowing CONTAINS on non-root nodes e.g. CONTAINS CLUSTER [/quote] Once again we hit another vendor choice. Fair enough, in that case, you may be in trouble because you cannot assign an alias to a path segment (as per syntax) and you lose the ability to imply it's the same data instance that should conform to criteria. As in: ``` ... CONTAINS INSTRUCTION i[bla] ... ... WHERE i/path/segments/all_/the/way/to/cluster/and_/its/element/value = 'foo1' and i/path/segments/all_/the/way/to/cluster/and_/its/element/value = 'foo2' ``` The problem above is this composition can have > 1 `CLUSTER`s and there's nothing that says "a cluster should satisfy both conditions" instead, this query says "fetch me the composition that contains a cluster with criteria 1 (foo1) and also contains cluster with criteria2 (foo2)" If a composition has two clusters, each satisfying one criteria, you'll end up selecting it, which is not what you want. (I used the underscores in the paths above to disable syntax colouring of discourse, ignore them) Unless you introduce some mechanism that's not in the commonly implemented subset of AQL, your best bet is to fetch these and filter the query results at the application layer. Implementers can do different things by interpreting the same syntax in a different way, but I'm always conservative when it comes to language semantics, so I cannot see an aql level solution unless the implementation supports `CLUSTER` in the `FROM` clause --- ## Post #33 by @siljelb [quote="ian.mcnicoll, post:31, topic:3153"] I think that is the key question and while I understand that might be technically correct, I think it is pretty counter-intuitive and I’m struggling to think of examples where it should apply. [/quote] I'm not sure I agree it's counter-intuitive. IMO the best solution for this generic problem would be if the FROM clause supported internal clusters, both with and without a path or at-code. --- ## Post #34 by @Seref [quote="siljelb, post:33, topic:3153"] IMO the best solution for this generic problem would be if the FROM clause supported internal clusters, both with and without a path or at-code. [/quote] I agree. Sorry, I should have given more focus to your earlier comment, I only responded to its first sentence, then posted something that agrees with the remaining part of it. You're right (at least at the same camp with me :) ) in your interpretation (so we're right). Re your suggested solution: vendors keep the supported types in FROM to a limited subset because for most technologies used to implement AQL, there is a significant cost (processing, memory, disk space) to supporting more types, or at-code-free queries. It is also hard to see where this'd stop, i.e. other types being promoted to `FROM` clause. --- ## Post #35 by @chaya [quote="ian.mcnicoll, post:29, topic:3153, full:true"] Did you use the approach that @Sijle suggested? i.e apply the rename to the presence node? - and can you share your original template as a template fileset as this makes it much easier to play around with? [/quote] No not yet, Please find attached the template native format. [sample_questionnaire.en.v0.t.json|attachment](upload://b9vt33JbQOBU1RakgDWioQp7oP1.json) (117.0 KB) The archetype used are openEHR-EHR-COMPOSITION.encounter.v1 and openEHR-EHR-OBSERVATION.symptom_sign_screening.v0 --- ## Post #36 by @bna A long thread. I will explore this on our CDR. Do we have some example composition to validate against? --- ## Post #37 by @chaya [quote="chaya, post:8, topic:3153, full:true"] [sample_questionnaire.en.v0.opt|attachment](/uploads/short-url/fxg4VRkj6TJRHHTxdIJzcf9biV1.opt) (42.4 KB) [sample_questionnaire-aql.json|attachment](/uploads/short-url/ldnHilADNhIG7g0u2EVIgH0mQAg.json) (501 Bytes) [sample_questionnaire-composition-1.json|attachment](/uploads/short-url/8lpqtLWxcCX831MSTlYJ6q5f1ab.json) (7.0 KB) [sample_questionnaire-composition-2.json|attachment](/uploads/short-url/26oMWMEgLvL1GuCXQULbw4qDxB3.json) (11.9 KB) [/quote] Hope this helps. --- ## Post #38 by @ian.mcnicoll @bna - I confirmed the behaviour on ehrBase and that Better CDRs handles this as if there was CONTAINS[at0022] clause. @siljelb - I don't really agree that this AQL is not using the archetype 'as -designed' - the query should work without needing to add the name/value as you suggested. I would probably have tried that s a workaround but it should not be necessary. --- ## Post #39 by @bna I added the two compositions and runned the AQL as provided by @chaya above ``` SELECT 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' and c/archetype_details/template_id/value = 'sample_questionnaire.en.v0' ORDER BY c/context/start_time/value DESC ``` `Yes` will return one row and `No` will return one row. As expected. I am not sure if I understand what the problem is. This seems like a trivial use-case for a CDR. --- ## Post #40 by @bna [quote="ian.mcnicoll, post:26, topic:3153"] I’m not aware of ant current implementations allowing CONTAINS on non-root nodes e.g. CONTAINS CLUSTER cls [at002] as per yours and @siljelb suggestions. [/quote] Seems like we have this feature... ![image|690x443](upload://PGYHK4MaSc1afsRDvVGs32GLcl.png) --- ## Post #41 by @damoca Thats great! As I said before, that cl[at0005] is a valid predicate according to the AQL specifications, so it should be acceptable not only in the WHERE clause, but also in the FROM clause. Following the discussion between @siljelb and @ian.mcnicoll, my opinion is that if we use a path in the WHERE clause, it should be evaluated as a whole, matching with every instance it finds, and without trying to break it by its middle multi-valued objects to group them. That should be explicitly expressed in the FROM clause. --- ## Post #42 by @chaya [quote="bna, post:39, topic:3153"] `Yes` will return one row and `No` will return one row. As expected. [/quote] @bna please note that this has to return rows where the match is found for the two where clause in the same instance of the cluster [at0022] --- ## Post #43 by @bna [quote="chaya, post:42, topic:3153"] please note that this has to return rows where the match is found for the two where clause in the same instance of the cluster [at0022] [/quote] Absolutely. That was the point 👍 --- ## Post #44 by @Seref [quote="bna, post:43, topic:3153"] Absolutely. That was the point [/quote] This is where I suggested that the syntax and consequently the spec is ambiguous. [quote="Seref, post:32, topic:3153"] As in: ``` ... CONTAINS INSTRUCTION i[bla] ... ... WHERE i/path/segments/all_/the/way/to/cluster/and_/its/element/value = 'foo1' and i/path/segments/all_/the/way/to/cluster/and_/its/element/value = 'foo2' ``` The problem above is this composition can have > 1 `CLUSTER`s and there’s nothing that says “a cluster should satisfy both conditions” instead, this query says “fetch me the composition that contains a cluster with criteria 1 (foo1) and also contains cluster with criteria2 (foo2)” If a composition has two clusters, each satisfying one criteria, you’ll end up selecting it, which is not what you want. (I used the underscores in the paths above to disable syntax colouring of discourse, ignore them) Unless you introduce some mechanism that’s not in the commonly implemented subset of AQL, your best bet is to fetch these and filter the query results at the application layer. Implementers can do different things by interpreting the same syntax in a different way, but I’m always conservative when it comes to language semantics, so I cannot see an aql level solution unless the implementation supports `CLUSTER` in the `FROM` clause [/quote] your implementation may be enforcing the criteria/logic for grouping data instances based on their archetype node ids, but I don't think the syntax is actually expressing that here. --- ## Post #45 by @ian.mcnicoll Hmmm ... one step forward and one step back!! At least in terms of helping fix the issue. I have picked up 2 competing themes/suggestions. 1. The 'correct' way to do this is enforce the grouping on a cluster by supporting CONTAINS on non-root archetype nodes. DIPS appear to do this. 2. Better and DIPS simply **assume** that the correct behaviour is to group data instances (as Seref just said) in the absence of such a CONTAINS clause It would be good to hear from other CDR implementers. This is quite a critical issue as a number of archetypes now use this pattern. --- ## Post #46 by @Seref [quote="ian.mcnicoll, post:45, topic:3153"] on non-root archetype nodes [/quote] Please consider not using this term :) It really is vague. --- ## Post #47 by @thomas.beale it is? Doesn't it just mean nodes that are root points of the archetypes that created them, and are marked with the archetype id rather than an internal archetype node code. Interested to understand the ambiguity, relating to querying. --- ## Post #48 by @Seref [quote="thomas.beale, post:47, topic:3153"] Doesn’t it just mean nodes that are root points of the archetypes that created them, and are marked with the archetype id rather than an internal archetype node code [/quote] To me, that's referring to a bunch of RM types which may be different according to interpretation. There's been a historical difference between what can be archetyped and what the tools support (maybe that's gone now), and what the clinicians prefer to archetype, as part of modelling. Agreeing upon a clear set of RM types as allowed types in the FROM clause is clearer to me. Highly personal view of course, but that's where my confusion comes from. ps: completely off topic but I'm being told that in recent years my perception has kinda "narrowed". I seem to be getting stuck at ambiguities which do not exist to the other side of the conversation. No idea why but if this is another case, then sorry about it. --- ## Post #49 by @stefanspiska As of the latest ehrbase Release, it will show the same behavior as Better and DIPS. --- ## Post #50 by @chaya Thank you. --- ## Post #51 by @chaya Hi All, Unfortunately the latest EHRBase release has still not addressed this issue completely. I have raised an issue in EHRBase git [issue](https://github.com/ehrbase/ehrbase/issues/1013). Thanks --- **Canonical:** https://discourse.openehr.org/t/aql-to-retrieving-data-for-an-element-with-cardinality-0/3153 **Original content:** https://discourse.openehr.org/t/aql-to-retrieving-data-for-an-element-with-cardinality-0/3153