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: CodeSystem Hierarchy

This view acts on the CodeSystem resource to produce a table named "code_system_hierarchy".

The table contains the following columns:

NameFhir TypeSQL TypeCollection
idstringString
parent_codestringString
codestringString

Selection Rules:

ItemCollTypeDescription
code_system_hierarchyCodeSystem
code_system_hierarchy / Select
code_system_hierarchy / Select / idgetResourceKey()
CodeSystem identifier
code_system_hierarchy / Select
code_system_hierarchy / Select / parent_codecode
Code of the parent concept in the hierarchy
code_system_hierarchy / Select / Selectfor each concept
code_system_hierarchy / Select / Select / codecode
Code of the child concept

Notes:

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

idparent_codecode
6vehiclecar
6vehicletruck
6vehiclemotorbike
6carsedan
6carsuv
6carhatchback
6truckpickup
6trucksemi

Given a CodeSystem with nested concepts like:

  • vehicle
    • car
      • sedan
      • suv
      • hatchback
    • truck
      • pickup
      • semi
    • motorbike

The repeat directive walks down the concept tree, and at each level the nested forEach extracts each child concept. This produces parent-child pairs that can be used to build adjacency lists for hierarchical queries or to analyze the structure of a terminology.