Use standard SQL on FHIR ViewDefinitions. Open source, US Core 7.0 compatible, ready to deploy.
New: ViewDefinitions now aligned with SQL on FHIR IG v2.0 specification. Learn about our CQL-to-SQL engine →
Showing 6 of 6 ViewDefinitions
SQL on FHIR IG →Flattened patient demographics including name, gender, birthDate, and identifiers
Columns:
patient_idfamily_namegiven_namegenderbirth_datemrn{
"resourceType": "ViewDefinition",
"name": "us_core_patient",
"resource": "Patient",
"meta": {
"profile": [
"http://hl7.org/fhir/uv/sql-on-fhir/StructureDefinition/ViewDefinition"
]
},
"select": [
{
"column": [
{
"name": "patient_id",
"path": "id"
}
]
},
{
"column": [
{
"name": "family_name",
"path": "name.where(use='official').family.first()"
}
]
},
{
"column": [
{
"name": "given_name",
"path": "name.where(use='official').given.first()"
}
]
},
{
"column": [
{
"name": "gender",
"path": "gender"
}
]
},
{
"column": [
{
"name": "birth_date",
"path": "birthDate"
}
]
},
{
"column": [
{
"name": "mrn",
"path": "identifier.where(type.coding.code='MR').value.first()"
}
]
}
]
}SELECT id AS patient_id, JSON_EXTRACT_SCALAR(name, '$[0].family') AS family_name, JSON_EXTRACT_SCALAR(name, '$[0].given[0]') AS given_name, gender, birthDate AS birth_date, JSON_EXTRACT_SCALAR(identifier, '$[?(@.type.coding[0].code=="MR")].value') AS mrn FROM fhir.patient
Vital signs observations with patient reference, code, value, and effective date
Columns:
observation_idpatient_idloinc_codedisplayvalueuniteffective_date{
"resourceType": "ViewDefinition",
"name": "us_core_vital_signs",
"resource": "Observation",
"meta": {
"profile": [
"http://hl7.org/fhir/uv/sql-on-fhir/StructureDefinition/ViewDefinition"
]
},
"where": [
{
"path": "category.coding.where(system='http://terminology.hl7.org/CodeSystem/observation-category' and code='vital-signs').exists()"
}
],
"select": [
{
"column": [
{
"name": "observation_id",
"path": "id"
}
]
},
{
"column": [
{
"name": "patient_id",
"path": "subject.reference"
}
]
},
{
"column": [
{
"name": "loinc_code",
"path": "code.coding.where(system='http://loinc.org').code.first()"
}
]
},
{
"column": [
{
"name": "display",
"path": "code.text"
}
]
},
{
"column": [
{
"name": "value",
"path": "valueQuantity.value"
}
]
},
{
"column": [
{
"name": "unit",
"path": "valueQuantity.unit"
}
]
},
{
"column": [
{
"name": "effective_date",
"path": "effectiveDateTime"
}
]
}
]
}SELECT id AS observation_id, JSON_EXTRACT_SCALAR(subject, '$.reference') AS patient_id, JSON_EXTRACT_SCALAR(code, '$.coding[0].code') AS loinc_code, JSON_EXTRACT_SCALAR(code, '$.text') AS display, CAST(JSON_EXTRACT_SCALAR(valueQuantity, '$.value') AS FLOAT64) AS value, JSON_EXTRACT_SCALAR(valueQuantity, '$.unit') AS unit, effectiveDateTime AS effective_date FROM fhir.observation WHERE JSON_EXTRACT_SCALAR(category, '$[0].coding[0].code') = 'vital-signs'
Active conditions with patient reference, diagnosis codes, and clinical status
Columns:
condition_idpatient_idicd10_codesnomed_codedisplayclinical_statusonset_date{
"resourceType": "ViewDefinition",
"name": "us_core_condition",
"resource": "Condition",
"meta": {
"profile": [
"http://hl7.org/fhir/uv/sql-on-fhir/StructureDefinition/ViewDefinition"
]
},
"select": [
{
"column": [
{
"name": "condition_id",
"path": "id"
}
]
},
{
"column": [
{
"name": "patient_id",
"path": "subject.reference"
}
]
},
{
"column": [
{
"name": "icd10_code",
"path": "code.coding.where(system='http://hl7.org/fhir/sid/icd-10-cm').code.first()"
}
]
},
{
"column": [
{
"name": "snomed_code",
"path": "code.coding.where(system='http://snomed.info/sct').code.first()"
}
]
},
{
"column": [
{
"name": "display",
"path": "code.text"
}
]
},
{
"column": [
{
"name": "clinical_status",
"path": "clinicalStatus.coding.code.first()"
}
]
},
{
"column": [
{
"name": "onset_date",
"path": "onsetDateTime"
}
]
}
]
}SELECT id AS condition_id, JSON_EXTRACT_SCALAR(subject, '$.reference') AS patient_id, (SELECT code FROM UNNEST(JSON_EXTRACT_ARRAY(code, '$.coding')) WHERE JSON_EXTRACT_SCALAR(code, '$.system') = 'http://hl7.org/fhir/sid/icd-10-cm' LIMIT 1) AS icd10_code, (SELECT code FROM UNNEST(JSON_EXTRACT_ARRAY(code, '$.coding')) WHERE JSON_EXTRACT_SCALAR(code, '$.system') = 'http://snomed.info/sct' LIMIT 1) AS snomed_code, JSON_EXTRACT_SCALAR(code, '$.text') AS display, JSON_EXTRACT_SCALAR(clinicalStatus, '$.coding[0].code') AS clinical_status, onsetDateTime AS onset_date FROM fhir.condition
Medication orders with patient reference, RxNorm codes, and prescriber info
Columns:
request_idpatient_idrxnorm_codemedication_displaystatusauthored_on{
"resourceType": "ViewDefinition",
"name": "us_core_medication_request",
"resource": "MedicationRequest",
"meta": {
"profile": [
"http://hl7.org/fhir/uv/sql-on-fhir/StructureDefinition/ViewDefinition"
]
},
"select": [
{
"column": [
{
"name": "request_id",
"path": "id"
}
]
},
{
"column": [
{
"name": "patient_id",
"path": "subject.reference"
}
]
},
{
"column": [
{
"name": "rxnorm_code",
"path": "medicationCodeableConcept.coding.where(system='http://www.nlm.nih.gov/research/umls/rxnorm').code.first()"
}
]
},
{
"column": [
{
"name": "medication_display",
"path": "medicationCodeableConcept.text"
}
]
},
{
"column": [
{
"name": "status",
"path": "status"
}
]
},
{
"column": [
{
"name": "authored_on",
"path": "authoredOn"
}
]
}
]
}SELECT id AS request_id, JSON_EXTRACT_SCALAR(subject, '$.reference') AS patient_id, JSON_EXTRACT_SCALAR(medicationCodeableConcept, '$.coding[0].code') AS rxnorm_code, JSON_EXTRACT_SCALAR(medicationCodeableConcept, '$.text') AS medication_display, status, authoredOn AS authored_on FROM fhir.medication_request
Healthcare encounters with patient, provider, location, and service type
Columns:
encounter_idpatient_idencounter_classtype_codestatusperiod_startperiod_end{
"resourceType": "ViewDefinition",
"name": "us_core_encounter",
"resource": "Encounter",
"meta": {
"profile": [
"http://hl7.org/fhir/uv/sql-on-fhir/StructureDefinition/ViewDefinition"
]
},
"select": [
{
"column": [
{
"name": "encounter_id",
"path": "id"
}
]
},
{
"column": [
{
"name": "patient_id",
"path": "subject.reference"
}
]
},
{
"column": [
{
"name": "encounter_class",
"path": "class.code"
}
]
},
{
"column": [
{
"name": "type_code",
"path": "type.coding.code.first()"
}
]
},
{
"column": [
{
"name": "status",
"path": "status"
}
]
},
{
"column": [
{
"name": "period_start",
"path": "period.start"
}
]
},
{
"column": [
{
"name": "period_end",
"path": "period.end"
}
]
}
]
}SELECT id AS encounter_id, JSON_EXTRACT_SCALAR(subject, '$.reference') AS patient_id, JSON_EXTRACT_SCALAR(class, '$.code') AS encounter_class, JSON_EXTRACT_SCALAR(type, '$[0].coding[0].code') AS type_code, status, JSON_EXTRACT_SCALAR(period, '$.start') AS period_start, JSON_EXTRACT_SCALAR(period, '$.end') AS period_end FROM fhir.encounter
Clinical procedures with CPT/SNOMED codes, status, and performed date
Columns:
procedure_idpatient_idcpt_codesnomed_codedisplaystatusperformed_date{
"resourceType": "ViewDefinition",
"name": "us_core_procedure",
"resource": "Procedure",
"meta": {
"profile": [
"http://hl7.org/fhir/uv/sql-on-fhir/StructureDefinition/ViewDefinition"
]
},
"select": [
{
"column": [
{
"name": "procedure_id",
"path": "id"
}
]
},
{
"column": [
{
"name": "patient_id",
"path": "subject.reference"
}
]
},
{
"column": [
{
"name": "cpt_code",
"path": "code.coding.where(system='http://www.ama-assn.org/go/cpt').code.first()"
}
]
},
{
"column": [
{
"name": "snomed_code",
"path": "code.coding.where(system='http://snomed.info/sct').code.first()"
}
]
},
{
"column": [
{
"name": "display",
"path": "code.text"
}
]
},
{
"column": [
{
"name": "status",
"path": "status"
}
]
},
{
"column": [
{
"name": "performed_date",
"path": "performedDateTime"
}
]
}
]
}SELECT id AS procedure_id, JSON_EXTRACT_SCALAR(subject, '$.reference') AS patient_id, (SELECT code FROM UNNEST(JSON_EXTRACT_ARRAY(code, '$.coding')) WHERE JSON_EXTRACT_SCALAR(code, '$.system') = 'http://www.ama-assn.org/go/cpt' LIMIT 1) AS cpt_code, (SELECT code FROM UNNEST(JSON_EXTRACT_ARRAY(code, '$.coding')) WHERE JSON_EXTRACT_SCALAR(code, '$.system') = 'http://snomed.info/sct' LIMIT 1) AS snomed_code, JSON_EXTRACT_SCALAR(code, '$.text') AS display, status, performedDateTime AS performed_date FROM fhir.procedure
We can help you create ViewDefinitions tailored to your implementation guide or custom profiles.