Skip to main content

Querying openEHR data with SQL in TypeScript

Select blood pressure​

const select = `SELECT $blood_pressure.$event AS blood_pressure
                FROM   EHR
                LET    $blood_pressure = out("openEHR-EHR-OBSERVATION.blood_pressure.v2")
                WHERE  $ehr = $1`
await client.query(select, ['ehr-1'])

Result:

{
  blood_pressure: [[[
        {"@type":"ELEMENT","_type":"ELEMENT","archetype_node_id":"at0004","name":{"@type":"DV_TEXT","_type":"DV_TEXT","value":"Systolic"},"value":{"@type":"DV_QUANTITY","_type":"DV_QUANTITY","magnitude":746.5,"units":"mm[Hg]"}},
        {"@type":"ELEMENT","_type":"ELEMENT","archetype_node_id":"at0005","name":{"@type":"DV_TEXT","_type":"DV_TEXT","value":"Diastolic"},"value":{"@type":"DV_QUANTITY","_type":"DV_QUANTITY","magnitude":526.1,"units":"mm[Hg]"}}
    ]],[[
        {"@type":"ELEMENT","_type":"ELEMENT","archetype_node_id":"at0004","name":{"@type":"DV_TEXT","_type":"DV_TEXT","value":"Systolic"},"value":{"@type":"DV_QUANTITY","_type":"DV_QUANTITY","magnitude":746.5,"units":"mm[Hg]"}},
        {"@type":"ELEMENT","_type":"ELEMENT","archetype_node_id":"at0005","name":{"@type":"DV_TEXT","_type":"DV_TEXT","value":"Diastolic"},"value":{"@type":"DV_QUANTITY","_type":"DV_QUANTITY","magnitude":526.1,"units":"mm[Hg]"}}
  ]]]
}

Select blood pressure and pulse​

const select = `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 = $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")`
await client.query(select, ['ehr-1'])

Result:

{
  blood_pressure: [[[
        {"@type":"ELEMENT","_type":"ELEMENT","archetype_node_id":"at0004","name":{"@type":"DV_TEXT","_type":"DV_TEXT","value":"Systolic"},"value":{"@type":"DV_QUANTITY","_type":"DV_QUANTITY","magnitude":746.5,"units":"mm[Hg]"}},
        {"@type":"ELEMENT","_type":"ELEMENT","archetype_node_id":"at0005","name":{"@type":"DV_TEXT","_type":"DV_TEXT","value":"Diastolic"},"value":{"@type":"DV_QUANTITY","_type":"DV_QUANTITY","magnitude":526.1,"units":"mm[Hg]"}}
    ]],[[
        {"@type":"ELEMENT","_type":"ELEMENT","archetype_node_id":"at0004","name":{"@type":"DV_TEXT","_type":"DV_TEXT","value":"Systolic"},"value":{"@type":"DV_QUANTITY","_type":"DV_QUANTITY","magnitude":746.5,"units":"mm[Hg]"}},
        {"@type":"ELEMENT","_type":"ELEMENT","archetype_node_id":"at0005","name":{"@type":"DV_TEXT","_type":"DV_TEXT","value":"Diastolic"},"value":{"@type":"DV_QUANTITY","_type":"DV_QUANTITY","magnitude":526.1,"units":"mm[Hg]"}}
  ]]],
  pulse: [[[
        {"@type":"ELEMENT","_type":"ELEMENT","archetype_node_id":"at0004","name":{"@type":"DV_TEXT","_type":"DV_TEXT","value":"Rate"},"value":{"@type":"DV_QUANTITY","_type":"DV_QUANTITY","magnitude":636.7,"units":"/min"}}
    ]],[[
        {"@type":"ELEMENT","_type":"ELEMENT","archetype_node_id":"at0004","name":{"@type":"DV_TEXT","_type":"DV_TEXT","value":"Rate"},"value":{"@type":"DV_QUANTITY","_type":"DV_QUANTITY","magnitude":636.7,"units":"/min"}}
  ]]]
}