SQL on FHIR
3.0.0-ballot - STU 3 Ballot International flag

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

Example ViewDefinition: Questionnaire Response Items

This view acts on the QuestionnaireResponse resource to produce a table named "questionnaire_response_items".

The table contains the following columns:

NameFhir TypeSQL TypeCollection
idstringString
questionnairestringString
patient_idstringString
authoreddateTimeDateTime
item_link_idstringString
item_textstringString
answer_value_stringstringString
answer_value_integerintegerInteger
answer_value_booleanbooleanBoolean
answer_value_datedateTimeDateTime

Selection Rules:

ItemCollTypeDescription
questionnaire_response_itemsQuestionnaireResponse
questionnaire_response_items / Select
questionnaire_response_items / Select / idgetResourceKey()
Unique questionnaire response identifier
questionnaire_response_items / Select / questionnairequestionnaire
Canonical URL of the questionnaire
questionnaire_response_items / Select / patient_idsubject.getReferenceKey(Patient)
Patient identifier
questionnaire_response_items / Select / authoredauthored
Date and time the response was authored
questionnaire_response_items / Select
questionnaire_response_items / Select / item_link_idlinkId
Unique identifier for this item within the questionnaire
questionnaire_response_items / Select / item_texttext
Question text
questionnaire_response_items / Select / Selectfor each answer, or null
questionnaire_response_items / Select / Select / answer_value_stringvalue.ofType(string)
String answer value
questionnaire_response_items / Select / Select / answer_value_integervalue.ofType(integer)
Integer answer value
questionnaire_response_items / Select / Select / answer_value_booleanvalue.ofType(boolean)
Boolean answer value
questionnaire_response_items / Select / Select / answer_value_datevalue.ofType(date)
Date answer value

Notes:

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

idquestionnairepatient_idauthoreditem_link_iditem_textanswer_value_stringanswer_value_integeranswer_value_booleananswer_value_date
32http://example.org/q/phq9282024-03-15T10:30:00+10:00q1Little interest or pleasure in doing things2
32http://example.org/q/phq9282024-03-15T10:30:00+10:00q2Feeling down, depressed, or hopeless1
33http://example.org/q/health-history292024-03-16T14:20:00+10:00demographicsDemographics
33http://example.org/q/health-history292024-03-16T14:20:00+10:00nameFull nameJohn Smith
33http://example.org/q/health-history292024-03-16T14:20:00+10:00dobDate of birth1980-05-22
33http://example.org/q/health-history292024-03-16T14:20:00+10:00conditionsMedical conditions
33http://example.org/q/health-history292024-03-16T14:20:00+10:00diabetesDiabetes1
33http://example.org/q/health-history292024-03-16T14:20:00+10:00diabetes-typeDiabetes typeType 2
33http://example.org/q/health-history292024-03-16T14:20:00+10:00hypertensionHypertension0

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. The "diabetes-type" item is nested under the answer to "diabetes" rather than under an item, and is reached through the answer.item path in repeat.