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/HL7/sql-on-fhir/ and changes regularly. See the Directory of published versions

Example Binary: QuestionnaireResponseItems

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

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

Selection Rules:

ItemCollTypeDescription
.. questionnaire_response_items QuestionnaireResponse
... Select
.... id getResourceKey()
Unique questionnaire response identifier
.... questionnaire questionnaire
Canonical URL of the questionnaire
.... patient_id subject.getReferenceKey(Patient)
Patient identifier
.... authored authored
Date and time the response was authored
... Select
.... item_link_id linkId
Unique identifier for this item within the questionnaire
.... item_text text
Question text
.... answer_value_string answer.value.ofType(string)
String answer value
.... answer_value_integer answer.value.ofType(integer)
Integer answer value
.... answer_value_boolean answer.value.ofType(boolean)
Boolean answer value
.... answer_value_date answer.value.ofType(date)
Date answer value

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.