SQL on FHIR
2.1.0-pre - release International flag

SQL on FHIR, published by SQL on FHIR Working Group. This guide is not an authorized publication; it is the continuous build for version 2.1.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

{
  "resourceType" : "https://sql-on-fhir.org/ig/StructureDefinition/ViewDefinition",
  "id" : "UsCoreBloodPressures",
  "name" : "us_core_blood_pressures",
  "status" : "draft",
  "resource" : "Observation",
  "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" : [
        {
          "path" : "value.ofType(Quantity).system",
          "name" : "sbp_quantity_system"
        },
        {
          "path" : "value.ofType(Quantity).code",
          "name" : "sbp_quantity_code"
        },
        {
          "path" : "value.ofType(Quantity).unit",
          "name" : "sbp_quantity_unit"
        },
        {
          "path" : "value.ofType(Quantity).value",
          "name" : "sbp_quantity_value"
        }
      ],
      "forEach" : "component.where(code.coding.exists(system='http://loinc.org' and code=%systolic_bp)).first()"
    },
    {
      "column" : [
        {
          "path" : "value.ofType(Quantity).system",
          "name" : "dbp_quantity_system"
        },
        {
          "path" : "value.ofType(Quantity).code",
          "name" : "dbp_quantity_code"
        },
        {
          "path" : "value.ofType(Quantity).unit",
          "name" : "dbp_quantity_unit"
        },
        {
          "path" : "value.ofType(Quantity).value",
          "name" : "dbp_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)"
    }
  ]
}

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