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 demographicsdemographic 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 right structure to consider in queries, that acts as a namespace for the data is the SYSTEM . This is not yet an openEHR type, although is assumed in few places in openEHR specifications: EHR.system_id, VERSIONED_OBJECT.owner_id and in OBJECT_VERSION_ID.creating_system_id.
We consider the SYSTEM type will hav following attributes:
uid (UID)
name (DV_TEXT)
location (DV_URI)
details (ITEM_STRUCTURE)
is already described on another page. A typical query using system may look like:
...
When SYSTEM is omitted, the query engine will query for data in all selected EHRs will use all available systems, like it is already supposed to work in standard AQL:
Code Block | ||
---|---|---|
| ||
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:
Code Block | ||
---|---|---|
| ||
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] |
Info |
---|
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 |
...
SELECT s/uid, s/name, e/ehr_id/value, c/context/start_time
| ||
| ||
|
Introducing SYSTEM, will allow us to also use demographic allows the use of EHR data next to Demographic data inside same system:
Code Block | ||
---|---|---|
| ||
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 encrptENCRYPT()
function that is able to match matches EHR with ACTOR in a environment using master patient indexesthat supports Master Patient Indexes:
Code Block | ||
---|---|---|
| ||
SELECT s/uid, s/name, e/ehr_id/value, c/context/start_time, p/uid/value, pid/itemitems[at0010]/value FROM SYSTEM s[uid/value=$system_uid] (CONTAINS EHR e CONTAINS COMPOSITION c [openEHR-EHR-COMPOSITION.encounter.v1v2] CONTAINS OBSERVATION obs [openEHR-EHR-OBSERVATION.blood_pressure.v1v2]) AND (CONTAINS PERSON p [openEHR-DEMOGRAPHIC-PERSON.person.v1v2] CONTAINS PARTY_IDENTITY pid [openEHR-DEMOGRAPHIC-PARTY_IDENTITY.person_name.v1v2]) WHERE e/ehr_status/subject/external_ref/id/value = encrypt(p/uid/value)ENCRYPT(p/uid/value) |
Querying for demographic data
The following will retrieve only demographic data, showing example on various CONTAINS use cases:
Code Block | ||
---|---|---|
| ||
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').
Code Block | ||
---|---|---|
| ||
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
(GROUP g [openEHR-DEMOGRAPHIC-GROUP.group.v2]
CONTAINS PARTY_IDENTITY gid [openEHR-DEMOGRAPHIC-PARTY_IDENTITY.group_name.v2])
AND
(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' |
Relations are store as PARTY_RELATIONSHIP instances, referred by ACTORS above, but they are not used explicitly in the query. The IS_RELATED() function will have to solve the query constrain by using those referred PARTY_RELATIONSHIP. Another variant would be to also add PARTY_RELATIONHIP in the FROM section:
Code Block | ||
---|---|---|
| ||
FROM
PERSON p [openEHR-DEMOGRAPHIC-PERSON.person.v2]
AND GROUP g [openEHR-DEMOGRAPHIC-GROUP.group.v2]
AND PARTY_RELATIONSHIP pr
WHERE
p/party_relationship/id = pr/uid
AND pr/source/id = p.uid
AND pr/target/id = g.uid
AND pr/name = 'patientOf' |