Behaviour of logical operators(OR, AND, NOT) in FROM Clause

I am working on aql query to get different composition for an ehr.
Query 1 : "SELECT c1, c2 FROM EHR e[ehr_id/value=‘3fb5117e-dba9-40d1-b472-d575f0a341ae’] CONTAINS (COMPOSITION c1[openEHR-EHR-COMPOSITION.encounter.v0] AND COMPOSITION c2[openEHR-EHR-COMPOSITION.encounter.v1]) "
I want to know how this above query will execute and in place of AND operator if it is OR then what would be result.
Query 2 : “SELECT c FROM EHR e[ehr_id/value=‘3fb5117e-dba9-40d1-b472-d575f0a341ae’] CONTAINS COMPOSITION c[openEHR-EHR-COMPOSITION.encounter.v1] CONTAINS OBSERVATION o[openEHR-EHR-OBSERVATION.rough.v1] WHERE o/data[at0001]/events[at0002]/data[at0003]/items[at0004]/value/value=‘10’”
Query 3 : “SELECT c FROM EHR e[ehr_id/value=‘3fb5117e-dba9-40d1-b472-d575f0a341ae’] CONTAINS COMPOSITION c[openEHR-EHR-COMPOSITION.encounter.v0] CONTAINS OBSERVATION o[openEHR-EHR-OBSERVATION.address.v0] WHERE o/data[at0001]/events[at0002]/data[at0003]/items[at0004]/value/value = ‘xyz’”
Query 4 : "SELECT c1, c2 FROM EHR e[ehr_id/value=‘3fb5117e-dba9-40d1-b472-d575f0a341ae’] CONTAINS (COMPOSITION c1[openEHR-EHR-COMPOSITION.encounter.v0] CONTAINS OBSERVATION o1[openEHR-EHR-OBSERVATION.address.v0]) AND (COMPOSITION c2[openEHR-EHR-COMPOSITION.encounter.v1] CONTAINS OBSERVATION o2[openEHR-EHR-OBSERVATION.rough.v1]) "

In Query 2 and Query 3, I am getting 1 as list size of RECORD1 in both the queries but getting zero as list size of RECORD2 in Query 4 and in Query 1 getting 2 as list size How? Want to know how exactly AND, OR, () works in CONTAINS

Query 5 : "SELECT c1, c2 FROM EHR e[ehr_id/value=‘3fb5117e-dba9-40d1-b472-d575f0a341ae’] CONTAINS COMPOSITION c1[openEHR-EHR-COMPOSITION.encounter.v0] NOT CONTAINS COMPOSITION c2[openEHR-EHR-COMPOSITION.encounter.v1] "

Query 5 throws : Got: 400. Error message: {“error”:“Could not process query, reason:org.antlr.v4.runtime.misc.ParseCancellationException: AQL Parse exception: line 1: char 140 mismatched input ‘NOT’ expecting {, WHERE, ORDERBY, OFFSET, LIMIT}”,“status”:“Bad Request”}

I can only comment about Query 5: my assumption is that the AQL engine you are using did not implemented yet the newly AQL Release-1.1.0, which introduced support for NOT CONTAINS ( SPECQUERY-7 ).

Hi Roshan,

Could you perhaps post a link to the template you are using and a some sample compositions?

Also what CDR are you using? As @sebastian.iancu says the NOT CONTAINS is fairly new and may not be supported by all CDRs yet.

Looks like EHRbase, I will check with @christian about the status of this one. My guess is that we don’t support it at the moment.

Thank you for your response and i appreciate the attention you are giving to this issue.

Thanks for your response. I want to know what are the other alternatives we can use for the same NOT CONTAINS.
for this query "SELECT c1, c2 FROM EHR e[ehr_id/value=‘3fb5117e-dba9-40d1-b472-d575f0a341ae’] CONTAINS COMPOSITION c1[openEHR-EHR-COMPOSITION.encounter.v0] NOT CONTAINS COMPOSITION c2[openEHR-EHR-COMPOSITION.encounter.v1] "

Does anyone know a CDR that supports NOT CONTAINS?
@Dileep_V_S @ian.mcnicoll @birger.haarbrandt @pablo @joostholslag @jake.smolka @borut.fabjan

I’m confused @Roshan , why are you including c2 in the SELECT clause when you’re excluding it with NOT CONTAINS ? That column would be null if NOT CONTAINS were supported.

That is checking the second COMPO is not contained in the first COMPO, it’s missing and AND or OR to check both conditions about the CONTAINS against the EHR, it might be something like EHR … (CONTAINS … OR NOT CONTAINS …)

1 Like

So, I’m a little confused on what NOT CONTAINS is supposed to do. I thought it was supposed to negate the equality of the archetype predicate. Taking a simple example (from the AQL docs):

FROM EHR [ehr_id/value='1234']
   CONTAINS COMPOSITION c [openEHR-EHR-COMPOSITION.encounter.v1]
      NOT CONTAINS OBSERVATION o [openEHR-EHR-OBSERVATION.blood_pressure.v1]

thought this translates into:

FROM EHR e
   CONTAINS COMPOSITION c
      CONTAINS OBSERVATION o
WHERE
   e/ehr_id/value = '1234' AND
   c/archetype_node_id = 'openEHR-EHR-COMPOSITION.encounter.v1' AND
   o/archetype_node_id != 'openEHR-EHR-OBSERVATION.blood_pressure.v1'

However, reading more [SPECQUERY-7] - openEHR JIRA, it is only supposed to look for the absence of the parent child hierarchy relationship.

So it’s more like:

FROM EHR e
   CONTAINS COMPOSITION c
      NOT CONTAINS OBSERVATION o
WHERE
   e/ehr_id/value = '1234' AND
   c/archetype_node_id = 'openEHR-EHR-COMPOSITION.encounter.v1' AND
   o/archetype_node_id = 'openEHR-EHR-OBSERVATION.blood_pressure.v1'

So when we say
NOT CONTAINS OBSERVATION o [openEHR-EHR-OBSERVATION.blood_pressure.v1]
does this:
a) only check for absence of “blood_pressure.v1” OBSERVATION, or
b) check for absence of any OBSERVATION? (since o/archetype_node_id = ‘openEHR-EHR-OBSERVATION.blood_pressure.v1’ only makes sense when o is not null)

In case of a) if we reference “o”, it feels natural to think it’ll reference other OBSERVATION that is NOT blood_pressure.v1 (o/archetype_node_id != ‘openEHR-EHR-OBSERVATION.blood_pressure.v1’).

In case of b) there’s no point since it’ll always be null. But in case of a) it makes sense to select this since it’ll return the other nodes that don’t match the archetype predicate.

Any idea on what the correct interpretation is?

The common sense interpretation would be that the following kinds of Compositions are matched in the FROM:

  • those that contain no Observation at all (i.e. Encounters at which no Obs were done or recorded)
  • those that contain Observation(s) that are not openEHR-EHR-OBSERVATION.blood_pressure.v1 or any specialisation.

How many CDRs do this today is a question.

Relating to the original question, it appears that Query 5 is an attempt to find openEHR-EHR-COMPOSITION.encounter.v0 Compositions but not ones based on encounter.v1 within a specific EHR.

SELECT 
    c1, c2 
FROM 
    EHR e[ehr_id/value=‘3fb5117e-dba9-40d1-b472-d575f0a341ae’] 
        CONTAINS COMPOSITION c1[openEHR-EHR-COMPOSITION.encounter.v0] 
        NOT CONTAINS COMPOSITION c2[openEHR-EHR-COMPOSITION.encounter.v1] 

This might return nothing, if the EHR contains any v1 encounters, since the EHR won’t match the criteria ‘contains v0 encounters and doesn’t contain v1 encounters’, even if there are v0 encounters. If there are no v1 encounter Compositions, the EHR will match, but c2 won’t be attachable to anything, so I would expect an error. In theory, such an error is catchable at query definition time, not just at runtime.

If the idea is just to get back v0 encounter Compositions, I would just use:

SELECT 
    c1
FROM 
    EHR e[ehr_id/value=‘3fb5117e-dba9-40d1-b472-d575f0a341ae’] 
        CONTAINS COMPOSITION c1[openEHR-EHR-COMPOSITION.encounter.v0] 

@Roshan can you please clarify your query? @pablo is right, a Composition cannot contain a composition so it’d help if you gave us some more information. People are trying to help you but this is getting into speculation now, about what you may be trying to do, so it’d be great if you could give us a bit more detail.

1 Like

@Roshan - agree with Seref - it would be really helpful if you could explain the problem that you are trying to solve as that query does not look correct. As others have said the second contains should not be nested and then

I did have a recent use case where I seemed to need a ‘NOT CONTAINS’ and it did not seen to be supported yet in the Better CDR.

My use case was to identify compositions that did not contain a specific CLUSTER archetype inside composition.other_context

so something like

SELECT 
    comp, clust
FROM 
    EHR e
        CONTAINS COMPOSITION comp[openEHR-EHR-COMPOSITION.encounter.v0] 
        NOT CONTAINS CLUSTER clust[openEHR-EHR-CLUSTER.mycluster.v1] 

but that did not work for me on the Better CDR

The solution was to query the whole path

SELECT 
    comp
FROM 
    EHR e
        CONTAINS COMPOSITION comp[openEHR-EHR-COMPOSITION.encounter.v0] 
        WHERE NOT EXISTS comp/context/other_context/items[openEHR-EHR-CLUSTER.mycluster.v1]

Not sure that is of any help to you though!!

1 Like

huh. Leaving aside this discussion turning into a speculation-fest as usual, did that work with the Better implementation? I’d say that NOT CONTAINS belongs to the WHERE clause, I don’t think it can be syntactically a sibling to a CONTAINS. Either I’m missing something, or you’re missing something, or Better’s missing something. Someone’s missing something…

Good spot! - copy /paste error!

I’ve edited the AQL in the original post

SELECT 
    comp
FROM 
    EHR e
        CONTAINS COMPOSITION comp[openEHR-EHR-COMPOSITION.encounter.v0] 
        WHERE NOT EXISTS c/context/other_context/items[openEHR-EHR-CLUSTER.mycluster.v1]
2 Likes

you mean
WHERE NOT EXISTS comp/ right? :stuck_out_tongue:

2 Likes

[moderator: query formatted for readability]

Hi, i am also trying to get ehrids from two templates, where the data points in the two templates matches some criteria. But getting empty rows if i use AND operator.

Aql used :

{
    "q" : "
SELECT 
    e/ehr_id/value as ehrIds 
FROM 
    ehr e CONTAINS 
        COMPOSITION c[openEHR-EHR-COMPOSITION.encounter.v1]
        CONTAINS (EVALUATION e1[openEHR-EHR-EVALUATION.gender.v1]) 
WHERE
    c/archetype_details/template_id/value='temp1' AND 
    c/archetype_details/template_id/value='temp2' AND 
    e1/data[at0002]/items[at0022]/value/mappings/target/code_string='snomed_code' AND 
    c/items[at0001]/value/defining_code/code_string='snomed_code' 
    ORDER BY c/context/start_time/value DESC"
}

Can anyone please help me out on how to use AND operator if we are querying on two templates.
Thanks in advance.

I guess if you need to search for two templates, then you need to use ‘OR’, not ‘AND’:

...
WHERE (c/archetype_details/template_id/value=‘patient_personal_data.en.v1’ OR c/archetype_details/template_id/value=‘screening_oral.en.v0’)
   AND ...

In theory you could also use AQL matches operator.

First: please consider formatting your aql queries when posting questions. It makes it easy for others to help you.

Second: as @sebastian.iancu says, you need to use OR, not AND . Think about it:
each composition has a single value for its ../template_id/value path. You’re asking the AQL engine to get you compositions which have value x and value y at this path. Since there is only one value set at that path , there are no matching compositions. There can never be.

1 Like

If i use OR, then i will get the ehrId’s if any one of the criteria matches. But I want the ehrId’s where it matches the both scenarios.