SQL on FHIR
2.0.0-pre - ci-build International flag

SQL on FHIR, published by HL7. This guide is not an authorized publication; it is the continuous build for version 2.0.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

Logical Model: Shareable View Definition

Official URL: http://hl7.org/fhir/uv/sql-on-fhir/StructureDefinition/ShareableViewDefinition Version: 2.0.0-pre
Draft as of 2024-10-03 Computable Name: ShareableViewDefinition

A profile for View Definitions intended to be shared between multiple systems. This requires that the View Definition have a defined URL and name. It also requires declaration of the FHIR version that the view is intended to be executed over, and the FHIR type of each column. This ensures consistent interpretation of the view across different view runner implementations.

Usage:

  • This Logical Model is not used by any profiles in this Implementation Guide

Formal Views of Profile Content

Description of Profiles, Differentials, Snapshots and how the different presentations work.

This structure is derived from ViewDefinition

NameFlagsCard.TypeDescription & Constraintsdoco
.. ViewDefinition 0..* ViewDefinition
Elements defined in Ancestors:url, identifier, name, title, meta, status, experimental, publisher, contact, description, useContext, copyright, resource, fhirVersion, constant, select, where
View Definition
Instances of this logical model are not marked to be the target of a Reference
... url 1..1 uri Canonical identifier for this view definition, represented as a URI (globally unique)
... name 1..1 string Name of view definition (computer and database friendly)
... fhirVersion 1..* code FHIR version(s) of the resource for the ViewDefinition
.... column
..... type 1..1 uri A FHIR StructureDefinition URI for the column's type.

doco Documentation for this format
NameFlagsCard.TypeDescription & Constraintsdoco
.. ViewDefinition 0..* ViewDefinition
Elements defined in Ancestors:url, identifier, name, title, meta, status, experimental, publisher, contact, description, useContext, copyright, resource, fhirVersion, constant, select, where
View Definition
Instances of this logical model are not marked to be the target of a Reference
... url 1..1 uri Canonical identifier for this view definition, represented as a URI (globally unique)
... name C 1..1 string Name of view definition (computer and database friendly)
sql-name: Name is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases.
... status 1..1 code draft | active | retired | unknown
Binding: PublicationStatus (required)
... fhirVersion 1..* code FHIR version(s) of the resource for the ViewDefinition
Binding: FHIRVersion (required)
... select C 1..* BackboneElement A collection of columns and nested selects to include in the view.
sql-expressions: Can only have at most one of `forEach` or `forEachOrNull`.
.... modifierExtension ?!Σ 0..* Extension Extensions that cannot be ignored even if unrecognized

doco Documentation for this format

Terminology Bindings

PathConformanceValueSetURI
ViewDefinition.statusrequiredPublicationStatus
http://hl7.org/fhir/ValueSet/publication-status
from the FHIR Standard
ViewDefinition.resourcerequiredResourceType
http://hl7.org/fhir/ValueSet/resource-types
from the FHIR Standard
ViewDefinition.fhirVersionrequiredFHIRVersion
http://hl7.org/fhir/ValueSet/FHIR-version
from the FHIR Standard

Constraints

IdGradePath(s)DetailsRequirements
ele-1error**ALL** elementsAll FHIR elements must have a @value or children
: hasValue() or (children().count() > id.count())
ext-1error**ALL** extensionsMust have either extensions or value[x], not both
: extension.exists() != value.exists()
sql-expressionserrorViewDefinition.selectCan only have at most one of `forEach` or `forEachOrNull`.
: (forEach | forEachOrNull).count() <= 1
sql-nameerrorViewDefinition.nameName is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases.
: empty() or matches('^[A-Za-z][A-Za-z0-9_]*$')
NameFlagsCard.TypeDescription & Constraintsdoco
.. ViewDefinition 0..* ViewDefinition
Elements defined in Ancestors:url, identifier, name, title, meta, status, experimental, publisher, contact, description, useContext, copyright, resource, fhirVersion, constant, select, where
View Definition
Instances of this logical model are not marked to be the target of a Reference
... url 1..1 uri Canonical identifier for this view definition, represented as a URI (globally unique)
... identifier 0..1 Identifier Additional identifier for the view definition
... name C 1..1 string Name of view definition (computer and database friendly)
sql-name: Name is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases.
... meta 0..1 Meta Metadata about the view definition
... status 1..1 code draft | active | retired | unknown
Binding: PublicationStatus (required)
... experimental 0..1 boolean For testing purposes, not real usage
... publisher 0..1 string Name of the publisher/steward (organization or individual)
... contact 0..* ContactDetail Contact details for the publisher
... description 0..1 markdown Natural language description of the view definition
... useContext 0..* UsageContext The context that the content is intended to support
... copyright 0..1 markdown Use and/or publishing restrictions
... resource 1..1 code FHIR resource for the ViewDefinition
Binding: ResourceType (required)
... fhirVersion 1..* code FHIR version(s) of the resource for the ViewDefinition
Binding: FHIRVersion (required)
... constant 0..* BackboneElement Constant that can be used in FHIRPath expressions
.... @id 0..1 id Unique id for inter-element referencing
.... extension 0..* Extension Additional content defined by implementations
Slice: Unordered, Open by value:url
.... modifierExtension ?!Σ 0..* Extension Extensions that cannot be ignored even if unrecognized
.... name C 1..1 string Name of constant (referred to in FHIRPath as %[name])
sql-name: Name is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases.
.... value[x] 1..1 Value of constant
..... valueBase64Binary base64Binary
..... valueBoolean boolean
..... valueCanonical canonical(Any)
..... valueCode code
..... valueDate date
..... valueDateTime dateTime
..... valueDecimal decimal
..... valueId id
..... valueInstant instant
..... valueInteger integer
..... valueInteger64 integer64
..... valueOid oid
..... valueString string
..... valuePositiveInt positiveInt
..... valueTime time
..... valueUnsignedInt unsignedInt
..... valueUri uri
..... valueUrl url
..... valueUuid uuid
... select C 1..* BackboneElement A collection of columns and nested selects to include in the view.
sql-expressions: Can only have at most one of `forEach` or `forEachOrNull`.
.... @id 0..1 id Unique id for inter-element referencing
.... extension 0..* Extension Additional content defined by implementations
Slice: Unordered, Open by value:url
.... modifierExtension ?!Σ 0..* Extension Extensions that cannot be ignored even if unrecognized
.... column 0..* BackboneElement A column to be produced in the resulting table.
..... @id 0..1 id Unique id for inter-element referencing
..... extension 0..* Extension Additional content defined by implementations
Slice: Unordered, Open by value:url
..... modifierExtension ?!Σ 0..* Extension Extensions that cannot be ignored even if unrecognized
..... path 1..1 string FHIRPath expression that creates a column and defines its content
..... name C 1..1 string Column name produced in the output
sql-name: Name is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases.
..... description 0..1 markdown Description of the column
..... collection 0..1 boolean Indicates whether the column may have multiple values.
..... type 1..1 uri A FHIR StructureDefinition URI for the column's type.
..... tag 0..* BackboneElement Additional metadata describing the column
...... @id 0..1 id Unique id for inter-element referencing
...... extension 0..* Extension Additional content defined by implementations
Slice: Unordered, Open by value:url
...... modifierExtension ?!Σ 0..* Extension Extensions that cannot be ignored even if unrecognized
...... name 1..1 string Name of tag
...... value 1..1 string Value of tag
.... select 0..* ​See select (ViewDefinition) Nested select relative to a parent expression.
.... (Choice of one) 0..1
..... forEach 0..1 string A FHIRPath expression to retrieve the parent element(s) used in the containing select. The default is effectively `$this`.
..... forEachOrNull 0..1 string Same as forEach, but will produce a row with null values if the collection is empty.
.... unionAll 0..* ​See select (ViewDefinition) Creates a union of all rows in the given selection structures.
... where 0..* BackboneElement A series of zero or more FHIRPath constraints to filter resources for the view.
.... @id 0..1 id Unique id for inter-element referencing
.... extension 0..* Extension Additional content defined by implementations
Slice: Unordered, Open by value:url
.... modifierExtension ?!Σ 0..* Extension Extensions that cannot be ignored even if unrecognized
.... path 1..1 string A FHIRPath expression defining a filter condition
.... description 0..1 string A human-readable description of the above where constraint.

doco Documentation for this format

Terminology Bindings

PathConformanceValueSetURI
ViewDefinition.statusrequiredPublicationStatus
http://hl7.org/fhir/ValueSet/publication-status
from the FHIR Standard
ViewDefinition.resourcerequiredResourceType
http://hl7.org/fhir/ValueSet/resource-types
from the FHIR Standard
ViewDefinition.fhirVersionrequiredFHIRVersion
http://hl7.org/fhir/ValueSet/FHIR-version
from the FHIR Standard

Constraints

IdGradePath(s)DetailsRequirements
ele-1error**ALL** elementsAll FHIR elements must have a @value or children
: hasValue() or (children().count() > id.count())
ext-1error**ALL** extensionsMust have either extensions or value[x], not both
: extension.exists() != value.exists()
sql-expressionserrorViewDefinition.selectCan only have at most one of `forEach` or `forEachOrNull`.
: (forEach | forEachOrNull).count() <= 1
sql-nameerrorViewDefinition.name, ViewDefinition.constant.name, ViewDefinition.select.column.nameName is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases.
: empty() or matches('^[A-Za-z][A-Za-z0-9_]*$')

This structure is derived from ViewDefinition

Summary

Mandatory: 3 elements(1 nested mandatory element)

Differential View

This structure is derived from ViewDefinition

NameFlagsCard.TypeDescription & Constraintsdoco
.. ViewDefinition 0..* ViewDefinition
Elements defined in Ancestors:url, identifier, name, title, meta, status, experimental, publisher, contact, description, useContext, copyright, resource, fhirVersion, constant, select, where
View Definition
Instances of this logical model are not marked to be the target of a Reference
... url 1..1 uri Canonical identifier for this view definition, represented as a URI (globally unique)
... name 1..1 string Name of view definition (computer and database friendly)
... fhirVersion 1..* code FHIR version(s) of the resource for the ViewDefinition
.... column
..... type 1..1 uri A FHIR StructureDefinition URI for the column's type.

doco Documentation for this format

Key Elements View

NameFlagsCard.TypeDescription & Constraintsdoco
.. ViewDefinition 0..* ViewDefinition
Elements defined in Ancestors:url, identifier, name, title, meta, status, experimental, publisher, contact, description, useContext, copyright, resource, fhirVersion, constant, select, where
View Definition
Instances of this logical model are not marked to be the target of a Reference
... url 1..1 uri Canonical identifier for this view definition, represented as a URI (globally unique)
... name C 1..1 string Name of view definition (computer and database friendly)
sql-name: Name is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases.
... status 1..1 code draft | active | retired | unknown
Binding: PublicationStatus (required)
... fhirVersion 1..* code FHIR version(s) of the resource for the ViewDefinition
Binding: FHIRVersion (required)
... select C 1..* BackboneElement A collection of columns and nested selects to include in the view.
sql-expressions: Can only have at most one of `forEach` or `forEachOrNull`.
.... modifierExtension ?!Σ 0..* Extension Extensions that cannot be ignored even if unrecognized

doco Documentation for this format

Terminology Bindings

PathConformanceValueSetURI
ViewDefinition.statusrequiredPublicationStatus
http://hl7.org/fhir/ValueSet/publication-status
from the FHIR Standard
ViewDefinition.resourcerequiredResourceType
http://hl7.org/fhir/ValueSet/resource-types
from the FHIR Standard
ViewDefinition.fhirVersionrequiredFHIRVersion
http://hl7.org/fhir/ValueSet/FHIR-version
from the FHIR Standard

Constraints

IdGradePath(s)DetailsRequirements
ele-1error**ALL** elementsAll FHIR elements must have a @value or children
: hasValue() or (children().count() > id.count())
ext-1error**ALL** extensionsMust have either extensions or value[x], not both
: extension.exists() != value.exists()
sql-expressionserrorViewDefinition.selectCan only have at most one of `forEach` or `forEachOrNull`.
: (forEach | forEachOrNull).count() <= 1
sql-nameerrorViewDefinition.nameName is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases.
: empty() or matches('^[A-Za-z][A-Za-z0-9_]*$')

Snapshot View

NameFlagsCard.TypeDescription & Constraintsdoco
.. ViewDefinition 0..* ViewDefinition
Elements defined in Ancestors:url, identifier, name, title, meta, status, experimental, publisher, contact, description, useContext, copyright, resource, fhirVersion, constant, select, where
View Definition
Instances of this logical model are not marked to be the target of a Reference
... url 1..1 uri Canonical identifier for this view definition, represented as a URI (globally unique)
... identifier 0..1 Identifier Additional identifier for the view definition
... name C 1..1 string Name of view definition (computer and database friendly)
sql-name: Name is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases.
... meta 0..1 Meta Metadata about the view definition
... status 1..1 code draft | active | retired | unknown
Binding: PublicationStatus (required)
... experimental 0..1 boolean For testing purposes, not real usage
... publisher 0..1 string Name of the publisher/steward (organization or individual)
... contact 0..* ContactDetail Contact details for the publisher
... description 0..1 markdown Natural language description of the view definition
... useContext 0..* UsageContext The context that the content is intended to support
... copyright 0..1 markdown Use and/or publishing restrictions
... resource 1..1 code FHIR resource for the ViewDefinition
Binding: ResourceType (required)
... fhirVersion 1..* code FHIR version(s) of the resource for the ViewDefinition
Binding: FHIRVersion (required)
... constant 0..* BackboneElement Constant that can be used in FHIRPath expressions
.... @id 0..1 id Unique id for inter-element referencing
.... extension 0..* Extension Additional content defined by implementations
Slice: Unordered, Open by value:url
.... modifierExtension ?!Σ 0..* Extension Extensions that cannot be ignored even if unrecognized
.... name C 1..1 string Name of constant (referred to in FHIRPath as %[name])
sql-name: Name is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases.
.... value[x] 1..1 Value of constant
..... valueBase64Binary base64Binary
..... valueBoolean boolean
..... valueCanonical canonical(Any)
..... valueCode code
..... valueDate date
..... valueDateTime dateTime
..... valueDecimal decimal
..... valueId id
..... valueInstant instant
..... valueInteger integer
..... valueInteger64 integer64
..... valueOid oid
..... valueString string
..... valuePositiveInt positiveInt
..... valueTime time
..... valueUnsignedInt unsignedInt
..... valueUri uri
..... valueUrl url
..... valueUuid uuid
... select C 1..* BackboneElement A collection of columns and nested selects to include in the view.
sql-expressions: Can only have at most one of `forEach` or `forEachOrNull`.
.... @id 0..1 id Unique id for inter-element referencing
.... extension 0..* Extension Additional content defined by implementations
Slice: Unordered, Open by value:url
.... modifierExtension ?!Σ 0..* Extension Extensions that cannot be ignored even if unrecognized
.... column 0..* BackboneElement A column to be produced in the resulting table.
..... @id 0..1 id Unique id for inter-element referencing
..... extension 0..* Extension Additional content defined by implementations
Slice: Unordered, Open by value:url
..... modifierExtension ?!Σ 0..* Extension Extensions that cannot be ignored even if unrecognized
..... path 1..1 string FHIRPath expression that creates a column and defines its content
..... name C 1..1 string Column name produced in the output
sql-name: Name is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases.
..... description 0..1 markdown Description of the column
..... collection 0..1 boolean Indicates whether the column may have multiple values.
..... type 1..1 uri A FHIR StructureDefinition URI for the column's type.
..... tag 0..* BackboneElement Additional metadata describing the column
...... @id 0..1 id Unique id for inter-element referencing
...... extension 0..* Extension Additional content defined by implementations
Slice: Unordered, Open by value:url
...... modifierExtension ?!Σ 0..* Extension Extensions that cannot be ignored even if unrecognized
...... name 1..1 string Name of tag
...... value 1..1 string Value of tag
.... select 0..* ​See select (ViewDefinition) Nested select relative to a parent expression.
.... (Choice of one) 0..1
..... forEach 0..1 string A FHIRPath expression to retrieve the parent element(s) used in the containing select. The default is effectively `$this`.
..... forEachOrNull 0..1 string Same as forEach, but will produce a row with null values if the collection is empty.
.... unionAll 0..* ​See select (ViewDefinition) Creates a union of all rows in the given selection structures.
... where 0..* BackboneElement A series of zero or more FHIRPath constraints to filter resources for the view.
.... @id 0..1 id Unique id for inter-element referencing
.... extension 0..* Extension Additional content defined by implementations
Slice: Unordered, Open by value:url
.... modifierExtension ?!Σ 0..* Extension Extensions that cannot be ignored even if unrecognized
.... path 1..1 string A FHIRPath expression defining a filter condition
.... description 0..1 string A human-readable description of the above where constraint.

doco Documentation for this format

Terminology Bindings

PathConformanceValueSetURI
ViewDefinition.statusrequiredPublicationStatus
http://hl7.org/fhir/ValueSet/publication-status
from the FHIR Standard
ViewDefinition.resourcerequiredResourceType
http://hl7.org/fhir/ValueSet/resource-types
from the FHIR Standard
ViewDefinition.fhirVersionrequiredFHIRVersion
http://hl7.org/fhir/ValueSet/FHIR-version
from the FHIR Standard

Constraints

IdGradePath(s)DetailsRequirements
ele-1error**ALL** elementsAll FHIR elements must have a @value or children
: hasValue() or (children().count() > id.count())
ext-1error**ALL** extensionsMust have either extensions or value[x], not both
: extension.exists() != value.exists()
sql-expressionserrorViewDefinition.selectCan only have at most one of `forEach` or `forEachOrNull`.
: (forEach | forEachOrNull).count() <= 1
sql-nameerrorViewDefinition.name, ViewDefinition.constant.name, ViewDefinition.select.column.nameName is limited to letters, numbers, or underscores and cannot start with an underscore -- i.e. with a regular expression of: ^[A-Za-z][A-Za-z0-9_]*$ This makes it usable as table names in a wide variety of databases.
: empty() or matches('^[A-Za-z][A-Za-z0-9_]*$')

This structure is derived from ViewDefinition

Summary

Mandatory: 3 elements(1 nested mandatory element)

 

Other representations of profile: CSV, Excel

Notes:

Required FHIRPath Expressions/Functions

All View Runners claiming conformance to the Shareable View Definition profile must implement these FHIRPath capabilities:

Experimental FHIRPath Functions

The following functions are intended for eventual inclusion in the required subset, however they are not yet a part of the normative FHIRPath release and may still be subject to change: