Skip to main content

Querying openEHR data with SQL via HTTP API

We use the fetch function in TypeScript to execute commands via HTTP API.

query function (used for executing SQL queries like SELECT):

const auth = Buffer.from('root:arcadedb').toString('base64')

async function query<T = unknown>(command: string): Promise<T[]> {
  const response = await fetch('http://localhost:2480/api/v1/query/arcehr', {
    method: 'POST',
    headers: {'Content-Type': 'application/json', 'Authorization': `Basic ${auth}`},
    body: JSON.stringify({language: 'sql', command}),
  })
  if (!response.ok) {
    throw new Error(`Query failed with ${response.status} ${response.statusText}: ${await response.text()}`)
  }
  const data = await response.json() as QueryResponse<T>
  return data.result ?? data.records ?? []
}

Select blood pressure​

// Select blood pressure
const select1 = `SELECT $blood_pressure.$event AS blood_pressure
                 FROM   EHR
                 LET    $blood_pressure = out("openEHR-EHR-OBSERVATION.blood_pressure.v2")
                 WHERE  $ehr = 'ehr-1'`
await query(select1)

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​

// Select blood pressure and pulse
const select2 = `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")`
await query(select2)

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"}}
  ]]]
}