This page includes frequently asked questions related to the use of sql in implementation guides (IGs).

An SQL query can be performed and the result set displayed as a table in any narrative page. The SQL query is embedded in the page like this:

{% sql select Name, Value from Metadata %}

This syntax will generate this table in the built IG:

Name	Value
path	http://hl7.org/fhir/R5/
​canonical	http://hl7.org/fhir/uv/howto
igId	​Guidance for FHIR IG Creation
igName	HowToCreateIGs
packageId	​Guidance for FHIR IG Creation
igVer	0.1.0
errorCount	1
version	5.0.0
releaseLabel	CI Build
revision	2aecd53
versionFull	5.0.0-2aecd53
toolingVersion	5.0.0
toolingRevision	3
toolingVersionFull	5.0.0 (3)
totalFiles	2
processedFiles	28
genDate	Fri, Oct 25, 2024 03:18+0000
genDay	25/10/2024
gitstatus	master

The SQL engine that evaluates the queries is SQLite3. The database the SQL is evaluated against is built as part of building the IG, and is available for authors to peruse in the file package.db when the IG is built.

Notes for Authors:

• Authors wishing to use SQL should use an SQLite3 tool to develop their queries against package.db
• The tables in the package.db are a moving target. See below for documentation
• If authors wish to affect the output, they can instead use the json format for the query, which looks like this:

{% sql {
  "query" : "select name as Name, Description, web from Resources",
  "class" : "lines",
  "columns" : [
    { "name" : "Name", "type" : "link", "source" : "Name", "target" : "web"},
    { "name" : "Description", "type" : "markdown", "source" : "Description"}
  ]
} %}

Which produces this table:

Name	Description
​HowToCreateIGs	Provides guidance on the process to define implementation guides using the HL7 IG Publisher and HL7-managed IG templates. It also provides guidance on 'best practices' around IG development and information about how to develop and maintain IG templates.
​Example patient	Example used to show the use of instance fragments

JSON Control format

The JSON format has the following properties:

• query: the sql to execute
• class: the css class name for the table. Default = grid
• titles: A boolean for whether to include column titles. Default = true
• columns: A JSON array with a list of column objects. If this is empty, or missing, the columns will be auto-populated with a type if 'auto'

Each column has one of more of the following properties:

• source: The SQL result field from which the column value is taken. The column will be blank if there is no value. Madatory
• title: The name to give to the column. Defaults to the source if not provided
• type: The type of the data in the column - see below
• target: The result field that contains the target - see in the type table
• system: A fixed URL or the result field that contains the code system URL - see in the type table
• display: The result field that contains the code display - see in the type table
• version: The result field that contains the code version - see in the type table

This table summarises the types of column:

Type	Description
auto	The type of the content will be automatically interprted as best as it can be from the value of the cell e.g. text, markdown, or canonical or URL
link	The column is rendered as a link where the source column is the text, and the target column contains the URL
text	The column is rendered as plain text
markdown	The column is rendered as markdown
url	The column is rendered as a URL using the URL as the target
canonical	The column is rendered as a link to the canonical resource identified in the source result fiel
resource	The column is rendered as a link to an IG authored resource, using the best name known. The result field is a getResourceKey() or getReferenceKey() value
coding	The column is rendered as a coded value using the source as the code, and the system, version and display as provided, if available

Embedding SQL Results as JSON Data

In addition to displaying SQL query results directly on the page, you can also execute a query and save the results as a JSON data file that can be used in Jekyll/Liquid templates. This allows you to query the IG database, generate JSON data, and then use that data to dynamically build content.

To use this feature, use the {​% sqlToData %} tag with the following syntax:

{% sqlToData myFileName SELECT ... %}

Where:

• myFileName is the name you want to give the _data JSON file (without ".json" extension). The same content will also be assigned to a liquid variable that you use to access the data in Jekyll.
• SELECT ... is your SQL query

The query results will be saved to _data/myFileName.json and can be accessed in Jekyll using site.data.myFileName. The same content will be assigned to the myFileName variable in the current context. The result is an array of rows where reach rows is a JSON object whoe keys are column names and values are column values.

For example:

{% sqlToData itemQuery
  SELECT count(*) as n from metadata
%}

Number of Metadata Items: {​{ itemQuery[0].n }} 

This queries the Metadata table to count items, saves the results to _data/itemQuery.json, and then generate an HTML output including the count.

Which produces this output:

Number of Metadata Items: 19

View Definitions

You can use ViewDefinitions to generate custom SQL tables that are also added to the package.db file. These can be used when pages are generated (as above), but might also be useful when the packages are used at run-time etc. The outputs are also saved to a json file with the same name as the name of the view definition.

ViewDefinitions are registered with the IG publisher using one or more viewDefinition parameters in the ImplementationGuide Resource that refer to the ViewDefinition to execute by the path to the local JSON file that contains the ViewDefinition. Note that templates are able to register ViewDefinitions using the same mechanism.

Each ViewDefinition generates a table in the output package.db. Table names must be unique (including the list below). It's the responsibility of the IG author to ensure that the names (taken from ViewDefinition.name) are unique.

Note that ViewDefinitions are experimental, and the definition or the table created may change without warning. (for now)

Scope

By default, the view is executed against the resources defined in the Implementation Guide itself. If desired, additional resources can be brought into scope, but this might have a considerable performance penalty. To specify additional package to bring into scope, use the extension http://hl7.org/fhir/tools/StructureDefinition/view-definition-scope.

{
  "name" : "TableName",
  "resource" : "StructureDefinition",
  "extension" : [{
    "url" : "http://hl7.org/fhir/tools/StructureDefinition/view-definition-scope",
    "valueCode" : "mask"
  }]
}

Where the mask is a package id that might include a wild card, e.g. hl7.fhir.us.core, or hl7.fhir.*, or just *. In any of these cases, the scope of the package specifier is limited to the set of packages that are in scope for the IG by it's listed dependencies and the recursive dependencies of any of those packages.

Base Tables

The following tables are always created by the IG publisher:

Table / Field Name	Type	Nullable?	Description
Metadata			Metadata about the IG including things such as build times
Key	integer	False	Incrementing Primary Key
Name	string	False	Name of Metadata item
Value	string	False	Value
Resources			A list of all resources defined in this IG. Not including contained resources
Key	integer	False	Incrementing Primary Key
Type	string	False	Resource Type
Id	string	False	Resource Id
Web	string	False	URL to refer to resource
Url	string	True	CanonicalResource.url (if present)
Version	string	True	CanonicalResource.version (if present)
Status	string	True	CanonicalResource.status (if present)
Date	string	True	CanonicalResource.date (if present)
Name	string	True	CanonicalResource.name (if present)
Title	string	True	CanonicalResource.title (if present)
Experimental	string	True	CanonicalResource.experimental (if present)
Realm	string	True	derived from CanonicalResource.jurisdiction (if present)
Description	string	True	CanonicalResource.description (if present)
Purpose	string	True	CanonicalResource.purpose (if present)
Copyright	string	True	CanonicalResource.copyright (if present)
CopyrightLabel	string	True	CanonicalResource.copyrightLabel (if present)
Json	binary	False	JSON source for resource
Properties			Code System properties defined by CodeSystems in the IG
Key	integer	False	Incrementing Primary Key
ResourceKey	integer	False	Reference to Resources.Key
Code	string	False	CodeSystem.property.code
Uri	string	True	CodeSystem.property.uri
Description	string	True	CodeSystem.property.description
Type	string	True	CodeSystem.property.type
Concepts			A list of all the codes defined in the code systems in the IG
Key	integer	False	Incrementing Primary Key
ResourceKey	integer	False	Reference to Resources.Key
ParentKey	integer	True	Reference tp Concepts.Key if not a root concept
Code	string	True	CodeSystem.concept.code
Display	string	True	CodeSystem.concept.display
Definition	string	True	CodeSystem.concept.definition
ConceptProperties			Properties defined in the concepts in the Concepts table
Key	integer	False	Incrementing Primary Key
ResourceKey	integer	False	Reference to Resources.Key
ConceptKey	integer	False	Reference to Concepts.Key
PropertyKey	integer	True	Reference to Properties.Key
Code	string	True	CodeSystem.concept.property.code
Value	string	True	CodeSystem.concept.property.code
Designations			Designations defined in the concepts in the Concepts table
Key	integer	False	Incrementing Primary Key
ResourceKey	integer	False	Reference to Resources.Key
ConceptKey	integer	False	Reference to Concepts.Key
UseSystem	string	True	CodeSystem.concept.designation.use.system
UseCode	string	True	CodeSystem.concept.designation.use.code
Lang	string	True	CodeSystem.concept.designation.lang
Value	text	True	CodeSystem.concept.designation.value
ConceptMappings			Mappings Defined in ConceptMaps in the IG
Key	integer	False	Incrementing Primary Key
ResourceKey	integer	False	Reference to Resources.Key
SourceSystem	string	True	ConceptMap.group.source
SourceVersion	string	True	ConceptMap.group.sourceVersion
SourceCode	string	True	ConceptMap.group.element.code
Relationship	string	True	ConceptMap.group.element.target.relationship
TargetSystem	string	True	ConceptMap.group.target
TargetVersion	string	True	ConceptMap.group.targetVersion
TargetCode	string	True	ConceptMap.group.element.target.code
ValueSet_Codes			ValueSet Expansion Table (flat expansions only) for ValueSets defined in this IG
Key	integer	False	Incrementing Primary Key
ResourceKey	integer	False	Reference to Resources.Key
ValueSetUri	string	False	ValueSet.url
ValueSetVersion	string	False	ValueSet.version
System	string	False	ValueSet.expansion.contains.system
Version	string	True	ValueSet.expansion.contains.version
Code	string	False	ValueSet.expansion.contains.code
Display	string	True	ValueSet.expansion.contains.display
CodeSystemList			List of all code systems for the IG. ViewType = 1 somewhat overlaps with the Resources table, but is more convenient for some uses
CodeSystemListKey	integer	False	Incrementing Primary Key
ViewType	integer	False	1 = all code systems in IG. 2 = all code systems used directly by this ig. 3 = all code systems used by this ig and it's dependencies
ResourceKey	integer	True	Reference to Resources.Key (if from this IG)
Url	string	True	CodeSystem.url
Version	string	True	CodeSystem.version
Status	string	True	CodeSystem.status
Name	string	True	CodeSystem.name
Title	string	True	CodeSystem.title
Description	string	True	CodeSystem.description
CodeSystemListOIDs			OIDs for the Code Systems
CodeSystemListKey	integer	False	Reference to CodeSystemList.CodeSystemListKey
OID	string	False	OID for the code system
CodeSystemListRefs			All resources found that reference the code system
CodeSystemListKey	integer	False	Reference to CodeSystemList.CodeSystemListKey
Type	string	False	ResourceType
Id	string	False	Resource Ig
ResourceKey	integer	True	Reference to Resources.Key (if from this IG)
Title	string	True	A human description of the resource - if known
Web	string	True	URL for reference to the resource
ValueSetList			List of all value sets for the IG. ViewType = 1 somewhat overlaps with the Resources table, but is more convenient for some uses
ValueSetListKey	integer	False	Incrementing Primary Key
ViewType	integer	False	1 = all value sets in IG. 2 = all value sets used directly by this ig. 3 = all code systems used by this ig and it's dependencies
ResourceKey	integer	True	Reference to Resources.Key (if from this IG)
Url	string	True	ValueSet.url
Version	string	True	ValueSet.version
Status	string	True	ValueSet.status
Name	string	True	ValueSet.name
Title	string	True	ValueSet.title
Description	string	True	ValueSet.description
ValueSetListOIDs			OIDs for the Value Sets
ValueSetListKey	integer	False	Reference to ValueSetList.ValueSetListKey
OID	string	False	OID for the value set
ValueSetListSystems			List of Code systems used by the value set
ValueSetListKey	integer	False	Reference to ValueSetList.ValueSetListKey
URL	string	False	A code system used by the value set
ValueSetListSources			List of stated source (= Code system source category)
ValueSetListKey	integer	False	Reference to ValueSetList.ValueSetListKey
Source	string	False	stated source ( Code system source category)
ValueSetListRefs			All resources found that reference the value set
ValueSetListKey	integer	False	Reference to ValueSetList.ValueSetListKey
Type	string	False	Resource Type
Id	string	False	Resource.id
ResourceKey	integer	True	Reference to Resources.Key (if from this IG)
Title	string	True	A human description of the resource - if known
Web	string	True	URL for reference to the resource