Querying openEHR data

Since ArcEHR maps openEHR Reference Model (RM) to the graph database, we can use graph traversal to query the openEHR data. The data stored in the graph database is much more efficient than the relational database. No joins are needed to retrieve the data. We use out('openEHR-EHR-OBSERVATION.blood_pressure.v2') to get all the blood pressure records for a given COMPOSITION or EHR.

Simple SELECT with one archetype

Let's say we want to get the blood pressure data for a given encounter.

We can use the out() function to get all the blood pressure records for a given encounter. The out() function takes an edge type name as an argument. The out() function returns a list of all the records that have the specified edge type. The out() function can be used to traverse the graph database to get the data.

SELECT $blood_pressure.$event AS blood_pressure
FROM   EHR
LET    $blood_pressure = out(`openEHR-EHR-OBSERVATION.blood_pressure.v2`)
WHERE  $ehr = "ehr-1"

Result:

{
  "blood_pressure": [[[
    {"_type": "ELEMENT", "archetype_node_id": "at0004",
      "name": {"_type": "DV_TEXT", "value": "Systolic"},
      "value": {"_type": "DV_QUANTITY",
        "magnitude": 140.5,
        "units": "mm[Hg]"
      }
    },
    {"_type": "ELEMENT", "archetype_node_id": "at0005",
      "name": {"_type": "DV_TEXT", "value": "Diastolic"},
      "value": {"_type": "DV_QUANTITY",
        "magnitude": 80.1,
        "units": "mm[Hg]"
      }
    }
  ]]]
}

SELECT with multiple archetypes

Let's say we want to get the blood pressure and pulse data for a given encounter. We can use the out() function to get all the blood pressure and pulse records for a given encounter. We can use the CONTAINS operator to filter the records. The CONTAINS operator takes a sub-query as an argument. The CONTAINS operator returns a list of all the records that satisfy the sub-query.

SELECT $blood_pressure.$event AS blood_pressure, $pulse.$event AS pulse
FROM   EHR
LET    $blood_pressure = out(`openEHR-EHR-OBSERVATION.blood_pressure.v2`),
       $pulse = out(`openEHR-EHR-OBSERVATION.pulse.v2`)
WHERE  $ehr = "ehr-1"
  AND  $blood_pressure.$event CONTAINS (
          archetype_node_id = "at0004" AND  -- Systolic
          value.magnitude > 140 AND value.units = "mm[Hg]")))
  AND  $pulse.$event CONTAINS (
          archetype_node_id = "at0004" AND  -- Rate
          value.magnitude > 80 AND value.units = "/min")))

Result:

{
  "blood_pressure": [[[
    {"_type": "ELEMENT", "archetype_node_id": "at0004",
      "name": {"_type": "DV_TEXT", "value": "Systolic"},
      "value": {"_type": "DV_QUANTITY",
        "magnitude": 140.5,
        "units": "mm[Hg]"
      }
    },
    {"_type": "ELEMENT", "archetype_node_id": "at0005",
      "name": {"_type": "DV_TEXT", "value": "Diastolic"},
      "value": {"_type": "DV_QUANTITY",
        "magnitude": 80.1,
        "units": "mm[Hg]"
      }
    }
  ]]],
  "pulse": [[[
    {"_type": "ELEMENT", "archetype_node_id": "at0004",
      "name": {"_type": "DV_TEXT", "value": "Rate"},
      "value": {"_type": "DV_QUANTITY",
        "magnitude": 83.7,
        "units": "/min"
      }
    }
  ]]]
}