AQL support for demographic queries

Queries using SYSTEM

As demographic data does not reside inside EHRs, there should be an upper structure which would be usable in those queries that combines EHRs with demographic data. This is especially needed for those settings facilitating querying across multi-tenant’s data, or when EHRs are clustered on the server side.

The SYSTEM type is already described on another page. A typical query using system may look like:

SELECT s/uid, s/name, e/ehr_id/value, c/context/start_time
FROM SYSTEM s
  CONTAINS EHR e CONTAINS COMPOSITION c

When SYSTEM is omitted, the engine will query for data in all selected EHRs will use all available systems, like it is already supposed to work in standard AQL:

SELECT e/ehr_id/value, c/context/start_time
FROM EHR e CONTAINS COMPOSITION c

And if the query addresses Demographic it would be query for data in all available systems:

SELECT p/uid/value, pid/item/value
FROM PERSON p [openEHR-DEMOGRAPHIC-PERSON.person.v2] 
  CONTAINS PARTY_IDENTITY pid [openEHR-DEMOGRAPHIC-PARTY_IDENTITY.person_name.v2]

One aspect to consider is that SYSTEM does not actually “contains” EHRs, but it is the EHR that referrers to the owning system. For this matter, if we would like to consider a new keyword (an AQL syntax change), for instance WRAPS, we could have:
FROM SYSTEM s WRAPS (EHR e CONTAINS COMPOSITION c)

Introducing SYSTEM, allows the use of EHR data next to Demographic data inside same system:

SELECT s/uid, s/name, e/ehr_id/value, c/context/start_time, p/uid/value, p/identities
FROM SYSTEM s
  (CONTAINS EHR e CONTAINS COMPOSITION c)
  AND CONTAINS PERSON p
WHERE p/uid/value = e/ehr_status/subject/external_ref/id/value

or, with more complexity, using an fictive ENCRYPT() function that matches EHR with ACTOR in a environment that supports Master Patient Indexes:

SELECT s/uid, s/name, e/ehr_id/value, c/context/start_time, p/uid/value, pid/items[at0010]/value
FROM SYSTEM s[uid/value=$system_uid]
  (CONTAINS EHR e CONTAINS COMPOSITION c [openEHR-EHR-COMPOSITION.encounter.v2]
      CONTAINS OBSERVATION obs [openEHR-EHR-OBSERVATION.blood_pressure.v2])
  AND 
  (CONTAINS PERSON p [openEHR-DEMOGRAPHIC-PERSON.person.v2] 
      CONTAINS PARTY_IDENTITY pid [openEHR-DEMOGRAPHIC-PARTY_IDENTITY.person_name.v2])
WHERE e/ehr_status/subject/external_ref/id/value = ENCRYPT(p/uid/value)

Querying for demographic data

The following will retrieve only demographic data, showing example on various CONTAINS use cases:

SELECT 
  p/uid/value, 
  pid/items[at0010]/value AS full_name, 
  d/items[at0001]/value AS bday, 
  d/items[at0031]/value AS gender
FROM PERSON p [openEHR-DEMOGRAPHIC-PERSON.person.v2] 
  CONTAINS PARTY_IDENTITY pid [openEHR-DEMOGRAPHIC-PARTY_IDENTITY.person_name.v2]
  AND CONTAINS ITEM_TREE d [openEHR-DEMOGRAPHIC-ITEM_TREE.person_details.v2]
  AND CONTAINS ADDRESS a [openEHR-DEMOGRAPHIC-ADDRESS.address.v2]
WHERE 
  a/details/items[at0006, "Postal Code"]/value/value = "GX11 1AA"

Querying using relations

Another important use-case is when querying demographic data of actors that matches certain relationship condition. En example would be “Retrieve all Patient’s data (uid, name, age, gender) that are belonging to a specific Care Group X (relation type = 'patientOf').

SELECT 
  p/uid/value, 
  pid/items[at0010]/value AS full_name, 
  d/items[at0001]/value AS bday, 
  d/items[at0031]/value AS gender
FROM 
  (PERSON p [openEHR-DEMOGRAPHIC-PERSON.person.v2] 
      CONTAINS PARTY_IDENTITY pid [openEHR-DEMOGRAPHIC-PARTY_IDENTITY.person_name.v2]
      AND CONTAINS ITEM_TREE d [openEHR-DEMOGRAPHIC-ITEM_TREE.person_details.v2])
  AND 
  (CONTAINS GROUP g [openEHR-DEMOGRAPHIC-GROUP.group.v2]
      CONTAINS PARTY_IDENTITY gid [openEHR-DEMOGRAPHIC-PARTY_IDENTITY.group_name.v2])
  AND
  (CONTAINS ROLE r [openEHR-DEMOGRAPHIC-ROLE.role.v2])
WHERE 
  gid/items[at0010]/value = "Care Group X"
  AND IS_RELATED(p, g, 'patientOf')
  AND p/roles[1]/id = r/uid AND r/name/value = 'Patient'