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

{
  "resourceType" : "https://sql-on-fhir.org/ig/StructureDefinition/ViewDefinition",
  "id" : "CodeSystemHierarchy",
  "name" : "code_system_hierarchy",
  "status" : "draft",
  "resource" : "CodeSystem",
  "select" : [
    {
      "column" : [
        {
          "path" : "getResourceKey()",
          "name" : "id",
          "description" : "CodeSystem identifier"
        }
      ]
    },
    {
      "column" : [
        {
          "path" : "code",
          "name" : "parent_code",
          "description" : "Code of the parent concept in the hierarchy"
        }
      ],
      "select" : [
        {
          "column" : [
            {
              "path" : "code",
              "name" : "code",
              "description" : "Code of the child concept"
            }
          ],
          "forEach" : "concept"
        }
      ],
      "repeat" : [
        "concept"
      ]
    }
  ]
}

Notes:

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

Error processing SQL: [SQLITE_ERROR] SQL error or missing database (no such table: code_system_hierarchy)

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.