SQL on FHIR
2.0.0-pre - ci-build International flag

SQL on FHIR, published by HL7. This guide is not an authorized publication; it is the continuous build for version 2.0.0-pre built by the FHIR (HL7® FHIR® Standard) CI Build. This version is based on the current content of https://github.com/FHIR/sql-on-fhir-v2/ and changes regularly. See the Directory of published versions

Example Binary: UsCoreBloodPressures

This content is an example of the View Definition Logical Model and is not a FHIR Resource

    
{
  "resourceType": "http://hl7.org/fhir/uv/sql-on-fhir/StructureDefinition/ViewDefinition",
  "constant": [
    {
      "name": "systolic_bp",
      "valueCode": "8480-6"
    },
    {
      "name": "diastolic_bp",
      "valueCode": "8462-4"
    },
    {
      "name": "bp_code",
      "valueCode": "85354-9"
    }
  ],
  "select": [
    {
      "column": [
        {
          "path": "getResourceKey()",
          "name": "id"
        },
        {
          "path": "subject.getReferenceKey(Patient)",
          "name": "patient_id",
          "description": "Can be used to join to patient tables created by other views."
        },
        {
          "path": "effective.ofType(dateTime)",
          "name": "effective_date_time"
        }
      ]
    },
    {
      "column": [
        {
          "name": "sbp_quantity_system",
          "path": "value.ofType(Quantity).system"
        },
        {
          "name": "sbp_quantity_code",
          "path": "value.ofType(Quantity).code"
        },
        {
          "name": "sbp_quantity_unit",
          "path": "value.ofType(Quantity).unit"
        },
        {
          "name": "sbp_quantity_value",
          "path": "value.ofType(Quantity).value"
        }
      ],
      "forEach": "component.where(code.coding.exists(system='http://loinc.org' and code=%systolic_bp)).first()"
    },
    {
      "column": [
        {
          "name": "dbp_quantity_system",
          "path": "value.ofType(Quantity).system"
        },
        {
          "name": "dbp_quantity_code",
          "path": "value.ofType(Quantity).code"
        },
        {
          "name": "dbp_quantity_unit",
          "path": "value.ofType(Quantity).unit"
        },
        {
          "name": "dbp_quantity_value",
          "path": "value.ofType(Quantity).value"
        }
      ],
      "forEach": "component.where(code.coding.exists(system='http://loinc.org' and code=%diastolic_bp)).first()"
    }
  ],
  "where": [
    {
      "path": "code.coding.exists(system='http://loinc.org' and code=%bp_code)"
    }
  ],
  "name": "us_core_blood_pressures",
  "status": "draft",
  "resource": "Observation"
}

Notes:

This will result in a "us_core_blood_pressures" table that looks like this:

id patient_id effective_date_time sbp_quantity_system sbp_quantity_code sbp_quantity_unit sbp_quantity_value dbp_quantity_system dbp_quantity_code dbp_quantity_unit dbp_quantity_value
1 1 2020-01-01T00:00:00 http://unitsofmeasure.org mmHg mm[Hg] 120 http://unitsofmeasure.org mmHg mm[Hg] 80
2 1 2020-01-02T00:00:00 http://unitsofmeasure.org mmHg mm[Hg] 130 http://unitsofmeasure.org mmHg mm[Hg] 90
3 2 2020-01-03T00:00:00 http://unitsofmeasure.org mmHg mm[Hg] 140 http://unitsofmeasure.org mmHg mm[Hg] 100
4 3 2020-01-04T00:00:00 http://unitsofmeasure.org mmHg mm[Hg] 150 http://unitsofmeasure.org mmHg mm[Hg] 110
5 3 2020-01-05T00:00:00 http://unitsofmeasure.org mmHg mm[Hg] 160 http://unitsofmeasure.org mmHg mm[Hg] 120