[
{
"composition": {
"content": {
"archetype_details": {
"archetype_id": {
"class": "ARCHETYPE_ID",
"value": "openEHR-EHR-COMPOSITION.encounter.v1"
},
"class": "ARCHETYPED",
"rm_version": "1.0.1",
"template_id": {
"class": "TEMPLATE_ID",
"value": "Vital Signs"
}
},
"archetype_node_id": "openEHR-EHR-COMPOSITION.encounter.v1",
"category": {
"class": "DV_CODED_TEXT",
"defining_code": {
"class": "CODE_PHRASE",
"code_string": "433",
"terminology_id": {
"class": "TERMINOLOGY_ID",
"value": "openehr"
}
},
"value": "event"
},
"class": "COMPOSITION",
"composer": {
"class": "PARTY_IDENTIFIED",
"name": "ehrscape"
},
"content": [
{
"archetype_details": {
"archetype_id": {
"class": "ARCHETYPE_ID",
"value": "openEHR-EHR-OBSERVATION.body_temperature.v1"
},
"class": "ARCHETYPED",
"rm_version": "1.0.1"
},
"archetype_node_id": "openEHR-EHR-OBSERVATION.body_temperature.v1",
"class": "OBSERVATION",
"data": {
"archetype_node_id": "at0002",
"class": "HISTORY",
"events": [
{
"archetype_node_id": "at0003",
"class": "POINT_EVENT",
"data": {
"archetype_node_id": "at0001",
"class": "ITEM_TREE",
"items": [
{
"archetype_node_id": "at0004",
"class": "ELEMENT",
"name": {
"class": "DV_TEXT",
"value": "Temperature"
},
"value": {
"class": "DV_QUANTITY",
"magnitude": 39.7,
"precision": 1,
"units": "°C"
}
}
],
"name": {
"class": "DV_TEXT",
"value": "Single"
}
},
"name": {
"class": "DV_TEXT",
"value": "Any event"
},
"state": {
"archetype_node_id": "at0029",
"class": "ITEM_TREE",
"items": [
{
"archetype_node_id": "at0030",
"class": "ELEMENT",
"name": {
"class": "DV_TEXT",
"value": "Body exposure"
},
"value": {
"class": "DV_CODED_TEXT",
"defining_code": {
"class": "CODE_PHRASE",
"code_string": "at0031",
"terminology_id": {
"class": "TERMINOLOGY_ID",
"value": "local"
}
},
"value": "Naked"
}
}
],
"name": {
"class": "DV_TEXT",
"value": "State"
}
},
"time": {
"class": "DV_DATE_TIME",
"value": "2005-12-20T17:49:46.000+01:00"
}
}
],
"name": {
"class": "DV_TEXT",
"value": "History"
},
"origin": {
"class": "DV_DATE_TIME",
"value": "2005-12-20T17:49:46.000+01:00"
}
},
"encoding": {
"class": "CODE_PHRASE",
"code_string": "UTF-8",
"terminology_id": {
"class": "TERMINOLOGY_ID",
"value": "IANA_character-sets"
}
},
"language": {
"class": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"class": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"name": {
"class": "DV_TEXT",
"value": "Body temperature"
},
"subject": {
"class": "PARTY_SELF"
}
},
{
"archetype_details": {
"archetype_id": {
"class": "ARCHETYPE_ID",
"value": "openEHR-EHR-OBSERVATION.blood_pressure.v1"
},
"class": "ARCHETYPED",
"rm_version": "1.0.1"
},
"archetype_node_id": "openEHR-EHR-OBSERVATION.blood_pressure.v1",
"class": "OBSERVATION",
"data": {
"archetype_node_id": "at0001",
"class": "HISTORY",
"events": [
{
"archetype_node_id": "at0006",
"class": "POINT_EVENT",
"data": {
"archetype_node_id": "at0003",
"class": "ITEM_TREE",
"items": [
{
"archetype_node_id": "at0004",
"class": "ELEMENT",
"name": {
"class": "DV_TEXT",
"value": "Systolic"
},
"value": {
"class": "DV_QUANTITY",
"magnitude": 105,
"precision": 0,
"units": "mm[Hg]"
}
},
{
"archetype_node_id": "at0005",
"class": "ELEMENT",
"name": {
"class": "DV_TEXT",
"value": "Diastolic"
},
"value": {
"class": "DV_QUANTITY",
"magnitude": 84,
"precision": 0,
"units": "mm[Hg]"
}
}
],
"name": {
"class": "DV_TEXT",
"value": "blood pressure"
}
},
"name": {
"class": "DV_TEXT",
"value": "any event"
},
"time": {
"class": "DV_DATE_TIME",
"value": "2005-12-20T17:49:46.000+01:00"
}
}
],
"name": {
"class": "DV_TEXT",
"value": "history"
},
"origin": {
"class": "DV_DATE_TIME",
"value": "2005-12-20T17:49:46.000+01:00"
}
},
"encoding": {
"class": "CODE_PHRASE",
"code_string": "UTF-8",
"terminology_id": {
"class": "TERMINOLOGY_ID",
"value": "IANA_character-sets"
}
},
"language": {
"class": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"class": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"name": {
"class": "DV_TEXT",
"value": "Blood Pressure"
},
"subject": {
"class": "PARTY_SELF"
}
},
{
"archetype_details": {
"archetype_id": {
"class": "ARCHETYPE_ID",
"value": "openEHR-EHR-OBSERVATION.height.v1"
},
"class": "ARCHETYPED",
"rm_version": "1.0.1"
},
"archetype_node_id": "openEHR-EHR-OBSERVATION.height.v1",
"class": "OBSERVATION",
"data": {
"archetype_node_id": "at0001",
"class": "HISTORY",
"events": [
{
"archetype_node_id": "at0002",
"class": "POINT_EVENT",
"data": {
"archetype_node_id": "at0003",
"class": "ITEM_TREE",
"items": [
{
"archetype_node_id": "at0004",
"class": "ELEMENT",
"name": {
"class": "DV_TEXT",
"value": "Body Height/Length"
},
"value": {
"class": "DV_QUANTITY",
"magnitude": 84.60156104434435,
"units": "cm"
}
}
],
"name": {
"class": "DV_TEXT",
"value": "Simple"
}
},
"name": {
"class": "DV_TEXT",
"value": "Any event"
},
"time": {
"class": "DV_DATE_TIME",
"value": "2005-12-20T17:49:46.000+01:00"
}
}
],
"name": {
"class": "DV_TEXT",
"value": "history"
},
"origin": {
"class": "DV_DATE_TIME",
"value": "2005-12-20T17:49:46.000+01:00"
}
},
"encoding": {
"class": "CODE_PHRASE",
"code_string": "UTF-8",
"terminology_id": {
"class": "TERMINOLOGY_ID",
"value": "IANA_character-sets"
}
},
"language": {
"class": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"class": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"name": {
"class": "DV_TEXT",
"value": "Height/Length"
},
"subject": {
"class": "PARTY_SELF"
}
},
{
"archetype_details": {
"archetype_id": {
"class": "ARCHETYPE_ID",
"value": "openEHR-EHR-OBSERVATION.body_weight.v1"
},
"class": "ARCHETYPED",
"rm_version": "1.0.1"
},
"archetype_node_id": "openEHR-EHR-OBSERVATION.body_weight.v1",
"class": "OBSERVATION",
"data": {
"archetype_node_id": "at0002",
"class": "HISTORY",
"events": [
{
"archetype_node_id": "at0003",
"class": "POINT_EVENT",
"data": {
"archetype_node_id": "at0001",
"class": "ITEM_TREE",
"items": [
{
"archetype_node_id": "at0004",
"class": "ELEMENT",
"name": {
"class": "DV_TEXT",
"value": "Body weight"
},
"value": {
"class": "DV_QUANTITY",
"magnitude": 10.9,
"units": "kg"
}
}
],
"name": {
"class": "DV_TEXT",
"value": "Simple"
}
},
"name": {
"class": "DV_TEXT",
"value": "Any event"
},
"time": {
"class": "DV_DATE_TIME",
"value": "2005-12-20T17:49:46.000+01:00"
}
}
],
"name": {
"class": "DV_TEXT",
"value": "history"
},
"origin": {
"class": "DV_DATE_TIME",
"value": "2005-12-20T17:49:46.000+01:00"
}
},
"encoding": {
"class": "CODE_PHRASE",
"code_string": "UTF-8",
"terminology_id": {
"class": "TERMINOLOGY_ID",
"value": "IANA_character-sets"
}
},
"language": {
"class": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"class": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"name": {
"class": "DV_TEXT",
"value": "Body weight"
},
"subject": {
"class": "PARTY_SELF"
}
},
{
"archetype_details": {
"archetype_id": {
"class": "ARCHETYPE_ID",
"value": "openEHR-EHR-OBSERVATION.heart_rate-pulse.v1"
},
"class": "ARCHETYPED",
"rm_version": "1.0.1"
},
"archetype_node_id": "openEHR-EHR-OBSERVATION.heart_rate-pulse.v1",
"class": "OBSERVATION",
"data": {
"archetype_node_id": "at0002",
"class": "HISTORY",
"events": [
{
"archetype_node_id": "at0003",
"class": "POINT_EVENT",
"data": {
"archetype_node_id": "at0001",
"class": "ITEM_TREE",
"items": [
{
"archetype_node_id": "at0004",
"class": "ELEMENT",
"name": {
"class": "DV_TEXT",
"value": "Rate"
},
"value": {
"class": "DV_QUANTITY",
"magnitude": 85,
"precision": 0,
"units": "/min"
}
}
],
"name": {
"class": "DV_TEXT",
"value": "structure"
}
},
"name": {
"class": "DV_TEXT",
"value": "Any event"
},
"time": {
"class": "DV_DATE_TIME",
"value": "2005-12-20T17:49:46.000+01:00"
}
}
],
"name": {
"class": "DV_TEXT",
"value": "history"
},
"origin": {
"class": "DV_DATE_TIME",
"value": "2005-12-20T17:49:46.000+01:00"
}
},
"encoding": {
"class": "CODE_PHRASE",
"code_string": "UTF-8",
"terminology_id": {
"class": "TERMINOLOGY_ID",
"value": "IANA_character-sets"
}
},
"language": {
"class": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"class": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"name": {
"class": "DV_TEXT",
"value": "Pulse"
},
"subject": {
"class": "PARTY_SELF"
}
},
{
"archetype_details": {
"archetype_id": {
"class": "ARCHETYPE_ID",
"value": "openEHR-EHR-OBSERVATION.respiration.v1"
},
"class": "ARCHETYPED",
"rm_version": "1.0.1"
},
"archetype_node_id": "openEHR-EHR-OBSERVATION.respiration.v1",
"class": "OBSERVATION",
"data": {
"archetype_node_id": "at0001",
"class": "HISTORY",
"events": [
{
"archetype_node_id": "at0002",
"class": "POINT_EVENT",
"data": {
"archetype_node_id": "at0003",
"class": "ITEM_TREE",
"items": [
{
"archetype_node_id": "at0004",
"class": "ELEMENT",
"name": {
"class": "DV_TEXT",
"value": "Rate"
},
"value": {
"class": "DV_QUANTITY",
"magnitude": 15,
"precision": 0,
"units": "/min"
}
}
],
"name": {
"class": "DV_TEXT",
"value": "List"
}
},
"name": {
"class": "DV_TEXT",
"value": "Any event"
},
"time": {
"class": "DV_DATE_TIME",
"value": "2005-12-20T17:49:46.000+01:00"
}
}
],
"name": {
"class": "DV_TEXT",
"value": "history"
},
"origin": {
"class": "DV_DATE_TIME",
"value": "2005-12-20T17:49:46.000+01:00"
}
},
"encoding": {
"class": "CODE_PHRASE",
"code_string": "UTF-8",
"terminology_id": {
"class": "TERMINOLOGY_ID",
"value": "IANA_character-sets"
}
},
"language": {
"class": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"class": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"name": {
"class": "DV_TEXT",
"value": "Respirations"
},
"subject": {
"class": "PARTY_SELF"
}
},
{
"archetype_details": {
"archetype_id": {
"class": "ARCHETYPE_ID",
"value": "openEHR-EHR-OBSERVATION.indirect_oximetry.v1"
},
"class": "ARCHETYPED",
"rm_version": "1.0.1"
},
"archetype_node_id": "openEHR-EHR-OBSERVATION.indirect_oximetry.v1",
"class": "OBSERVATION",
"data": {
"archetype_node_id": "at0001",
"class": "HISTORY",
"events": [
{
"archetype_node_id": "at0002",
"class": "POINT_EVENT",
"data": {
"archetype_node_id": "at0003",
"class": "ITEM_TREE",
"items": [
{
"archetype_node_id": "at0006",
"class": "ELEMENT",
"name": {
"class": "DV_TEXT",
"value": "spO2"
},
"value": {
"class": "DV_PROPORTION",
"denominator": 100,
"numerator": 96.2,
"type": 0
}
}
],
"name": {
"class": "DV_TEXT",
"value": "Tree"
}
},
"name": {
"class": "DV_TEXT",
"value": "Any event"
},
"time": {
"class": "DV_DATE_TIME",
"value": "2005-12-20T17:49:46.000+01:00"
}
}
],
"name": {
"class": "DV_TEXT",
"value": "Event Series"
},
"origin": {
"class": "DV_DATE_TIME",
"value": "2005-12-20T17:49:46.000+01:00"
}
},
"encoding": {
"class": "CODE_PHRASE",
"code_string": "UTF-8",
"terminology_id": {
"class": "TERMINOLOGY_ID",
"value": "IANA_character-sets"
}
},
"language": {
"class": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"class": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"name": {
"class": "DV_TEXT",
"value": "Indirect oximetry"
},
"subject": {
"class": "PARTY_SELF"
}
}
],
"context": {
"class": "EVENT_CONTEXT",
"setting": {
"class": "DV_CODED_TEXT",
"defining_code": {
"class": "CODE_PHRASE",
"code_string": "238",
"terminology_id": {
"class": "TERMINOLOGY_ID",
"value": "openehr"
}
},
"value": "other care"
},
"start_time": {
"class": "DV_DATE_TIME",
"value": "2005-12-20T17:49:46.000+01:00"
}
},
"language": {
"class": "CODE_PHRASE",
"code_string": "en",
"terminology_id": {
"class": "TERMINOLOGY_ID",
"value": "ISO_639-1"
}
},
"name": {
"class": "DV_TEXT",
"value": "Vital Signs"
},
"territory": {
"class": "CODE_PHRASE",
"code_string": "SI",
"terminology_id": {
"class": "TERMINOLOGY_ID",
"value": "ISO_3166-1"
}
},
"uid": {
"class": "OBJECT_VERSION_ID",
"value": "0060656d-87a9-448f-a110-6d179022dbff::MyMedEHR::4"
}
},
"parentId": "7ee7b4c4-b25d-44c8-90b6-178c9644202b",
"type": "composition"
}
}
]
select expand(value.data.items.value.magnitude) from( select expand(value.data.events) from ( select expand(value.content) from CompositionVersions where 'openEHR-EHR-OBSERVATION.body_temperature.v1' in value.content.archetype_node_id ) where value.data.origin.value >= '2013-01-17' and value.data.archetype_node_id ='at0002' )where value.archetype_node_id ='at0003'and value.data.archetype_node_id = 'at0001'
I've got it using
select from CompositionVersions " + "where (select distinct (id) from (select id, date, events.data.items as items " + "from (select id, value.data.origin.value as date, value.data.events as events " + "from (select value.uid.value as id, value.content as value " + "from CompositionVersions unwind value) " + "where value.archetype_node_id = 'openEHR-EHR-OBSERVATION.body_temperature.v1' " + "and value.data.origin.value >= '2013-01-17' and value.data.archetype_node_id ='at0002' unwind events) " + "where events.archetype_node_id ='at0003' and events.data.archetype_node_id = 'at0001' unwind items) " + "where items.archetype_node_id = 'at0004' and items.value.magnitude <= 40 and items.value.magnitude >= 35 " + "and items.value.units !='°F' order by date ASC) " + "in value.uid.value