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: QuestionnaireResponseItems

{
  "resourceType" : "https://sql-on-fhir.org/ig/StructureDefinition/ViewDefinition",
  "id" : "QuestionnaireResponseItems",
  "name" : "questionnaire_response_items",
  "status" : "draft",
  "resource" : "QuestionnaireResponse",
  "select" : [
    {
      "column" : [
        {
          "path" : "getResourceKey()",
          "name" : "id",
          "description" : "Unique questionnaire response identifier"
        },
        {
          "path" : "questionnaire",
          "name" : "questionnaire",
          "description" : "Canonical URL of the questionnaire"
        },
        {
          "path" : "subject.getReferenceKey(Patient)",
          "name" : "patient_id",
          "description" : "Patient identifier"
        },
        {
          "path" : "authored",
          "name" : "authored",
          "description" : "Date and time the response was authored"
        }
      ]
    },
    {
      "column" : [
        {
          "path" : "linkId",
          "name" : "item_link_id",
          "description" : "Unique identifier for this item within the questionnaire"
        },
        {
          "path" : "text",
          "name" : "item_text",
          "description" : "Question text"
        },
        {
          "path" : "answer.value.ofType(string)",
          "name" : "answer_value_string",
          "description" : "String answer value"
        },
        {
          "path" : "answer.value.ofType(integer)",
          "name" : "answer_value_integer",
          "description" : "Integer answer value"
        },
        {
          "path" : "answer.value.ofType(boolean)",
          "name" : "answer_value_boolean",
          "description" : "Boolean answer value"
        },
        {
          "path" : "answer.value.ofType(date)",
          "name" : "answer_value_date",
          "description" : "Date answer value"
        }
      ],
      "repeat" : [
        "item"
      ]
    }
  ]
}

Notes:

This will result in a “questionnaire_response_items” table that looks like this:

id questionnaire patient_id authored item_link_id item_text answer_value_string answer_value_integer answer_value_boolean answer_value_date
1 http://example.org/q/phq9 101 2024-03-15T10:30:00+10:00 q1 Little interest… null 2 null null
1 http://example.org/q/phq9 101 2024-03-15T10:30:00+10:00 q2 Feeling down… null 1 null null
2 http://example.org/q/health-history 102 2024-03-16T14:20:00+10:00 demographics Demographics null null null null
2 http://example.org/q/health-history 102 2024-03-16T14:20:00+10:00 name Full name John Smith null null null
2 http://example.org/q/health-history 102 2024-03-16T14:20:00+10:00 dob Date of birth null null null 1980-05-22
2 http://example.org/q/health-history 102 2024-03-16T14:20:00+10:00 conditions Medical conditions null null null null
2 http://example.org/q/health-history 102 2024-03-16T14:20:00+10:00 diabetes Diabetes null null true null
2 http://example.org/q/health-history 102 2024-03-16T14:20:00+10:00 hypertension Hypertension null null false null

Note how all items are flattened into a single table regardless of their nesting depth. The “demographics” and “conditions” items are group items (with no answer values), while items like “name”, “dob”, “diabetes”, and “hypertension” are nested within those groups but appear as separate rows in the output.