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

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

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

Selection Rules:

ItemCollTypeDescription
.. code_system_hierarchy CodeSystem
... Select
.... id getResourceKey()
CodeSystem identifier
... Select
.... parent_code code
Code of the parent concept in the hierarchy
.... Select for each concept
..... code code
Code of the child concept

Notes:

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

id parent_code code
1 vehicle car
1 vehicle truck
1 vehicle motorbike
1 car sedan
1 car suv
1 car hatchback
1 truck pickup
1 truck semi

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 analyse the structure of a terminology.