SQL on FHIR
2.0.0 - release International flag

This page is part of the SQL on FHIR (v2.0.0: Release) based on FHIR (HL7® FHIR® Standard) v5.0.0. This is the current published version in its permanent home (it will always be available at this URL). For a full list of available versions, see the Directory of published versions

Logical Model: View Definition

Official URL: https://sql-on-fhir.org/ig/StructureDefinition/ViewDefinition Version: 2.0.0
Draft as of 2024-10-09 Computable Name: ViewDefinition

A ViewDefinition represents a tabular projection of a FHIR resource, where the columns and inclusion criteria are defined by FHIRPath expressions.

It is the central piece of the View Layer and represents a tabular projection of FHIR resources with the columns and filtering criteria defined by FHIRPath expressions. The logical model is described below.

Key Elements

Resource

Each ViewDefinition instance is tied to a single FHIR resource type, such as Patient or Observation. It will then create zero or more rows for each resource instance. Examples include simple tabular views of patients, unrolling patient addresses into an address table, views of certain types of observations, and so on.

Select

Each ViewDefinition instance has a select that specifies the content and names for the columns in the view. The content for each column is defined with FHIRPath expressions that return specific data elements from the FHIR resources.

Where

The ViewDefinition may include one or more where clauses that may be used to further limit, or filter, the resources included in the view. For instance, users may have different views for blood pressure observations or other observation types.

Constants

The ViewDefinition may include one or more constants, which are simply values that can be reused in FHIRPath expressions.

View Runner

A View Runner implementation can execute a ViewDefinition and return the results as a table that can be used for further processing using the user's chosen tech stack. See System Layers for details.

Profiling

ViewDefinitions may be profiled to meet specific needs. For instance, the Shareable View Definition profile adds constraints for ViewDefinitions intended to be shared between systems. Implementers may create their own ViewDefinition profiles for further specialized needs.

Usage:

Formal Views of Profile Content

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

This structure is derived from Base

NameFlagsCard.TypeDescription & Constraintsdoco
.. ViewDefinition 0..* Base View Definition
Instances of this logical model are not marked to be the target of a Reference
... url 0..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 0..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 0..* code FHIR version(s) of the resource for the ViewDefinition
Binding: FHIRVersion (required)
... constant 0..* BackboneElement Constant that can be used in FHIRPath expressions
.... 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`.
.... column 0..* BackboneElement A column to be produced in the resulting table.
..... 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 0..1 uri A FHIR StructureDefinition URI for the column's type.
..... tag 0..* BackboneElement Additional metadata describing the column
...... name 1..1 string Name of tag
...... value 1..1 string Value of tag
.... select 0..* See select 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 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.
.... 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 (Differential)

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
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_]*$')
NameFlagsCard.TypeDescription & Constraintsdoco
.. ViewDefinition 0..* Base View Definition
Instances of this logical model are not marked to be the target of a Reference
... url 0..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 0..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 0..* code FHIR version(s) of the resource for the ViewDefinition
Binding: FHIRVersion (required)
... constant 0..* BackboneElement Constant that can be used in FHIRPath expressions
.... 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`.
.... modifierExtension ?!Σ 0..* Extension Extensions that cannot be ignored even if unrecognized
.... column 0..* BackboneElement A column to be produced in the resulting table.
..... 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 0..1 uri A FHIR StructureDefinition URI for the column's type.
..... tag 0..* BackboneElement Additional metadata describing the column
...... 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 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 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.
.... 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_]*$')
NameFlagsCard.TypeDescription & Constraintsdoco
.. ViewDefinition 0..* Base View Definition
Instances of this logical model are not marked to be the target of a Reference
... url 0..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 0..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 0..* 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 0..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 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 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 Base

Summary

Mandatory: 0 element(10 nested mandatory elements)

Differential View

This structure is derived from Base

NameFlagsCard.TypeDescription & Constraintsdoco
.. ViewDefinition 0..* Base View Definition
Instances of this logical model are not marked to be the target of a Reference
... url 0..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 0..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 0..* code FHIR version(s) of the resource for the ViewDefinition
Binding: FHIRVersion (required)
... constant 0..* BackboneElement Constant that can be used in FHIRPath expressions
.... 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`.
.... column 0..* BackboneElement A column to be produced in the resulting table.
..... 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 0..1 uri A FHIR StructureDefinition URI for the column's type.
..... tag 0..* BackboneElement Additional metadata describing the column
...... name 1..1 string Name of tag
...... value 1..1 string Value of tag
.... select 0..* See select 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 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.
.... 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 (Differential)

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
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_]*$')

Key Elements View

NameFlagsCard.TypeDescription & Constraintsdoco
.. ViewDefinition 0..* Base View Definition
Instances of this logical model are not marked to be the target of a Reference
... url 0..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 0..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 0..* code FHIR version(s) of the resource for the ViewDefinition
Binding: FHIRVersion (required)
... constant 0..* BackboneElement Constant that can be used in FHIRPath expressions
.... 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`.
.... modifierExtension ?!Σ 0..* Extension Extensions that cannot be ignored even if unrecognized
.... column 0..* BackboneElement A column to be produced in the resulting table.
..... 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 0..1 uri A FHIR StructureDefinition URI for the column's type.
..... tag 0..* BackboneElement Additional metadata describing the column
...... 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 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 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.
.... 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_]*$')

Snapshot View

NameFlagsCard.TypeDescription & Constraintsdoco
.. ViewDefinition 0..* Base View Definition
Instances of this logical model are not marked to be the target of a Reference
... url 0..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 0..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 0..* 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 0..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 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 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 Base

Summary

Mandatory: 0 element(10 nested mandatory elements)

 

Other representations of profile: CSV, Excel

Notes:

FHIRPath Functionality

Columns within a view are defined using the FHIRPath language. FHIRPath contains a large number of functions and syntax constructs - not all of these are required to be implemented within a SQL on FHIR view runner.

View runner implementations MUST support the following required additional FHIRPath functions. In addition to this, runners SHOULD implement the FHIRPath subset defined in the Shareable View Definition profile if the intent is for the runner to be able to execute shared view definitions.

Required Additional Functions

All View Runners must implement these functions that extend the FHIRPath specification. Despite not being in the FHIRPath specification, they are necessary in the context of defining views:

getResourceKey() : KeyType

This is invoked at the root of a FHIR Resource and returns an opaque value to be used as the primary key for the row associated with the resource. In many cases the value may just be the resource id, but exceptions are described below.

This function is used in tandem with getReferenceKey, which returns an equal value from references that point to this resource.

The returned KeyType is implementation dependent, but must be a FHIR primitive type that can be used for efficient joins in the system's underlying data storage. Integers, strings, UUIDs, and other primitive types may be appropriate.

See the Joins with Resource and Reference Keys section below for details.

getReferenceKey([resource: type specifier]) : KeyType

This is invoked on Reference elements and returns an opaque value that represents the database key of the row being referenced. The value returned must be equal to the getResourceKey value returned on the resource itself.

Users may pass an optional resource type (e.g., Patient or Observation) to indicate the expected type that the reference should point to. The getReferenceKey function will return an empty collection (effectively null since FHIRPath always returns collections) if the reference is not of the expected type. For example, Observation.subject.getReferenceKey(Patient) would return a row key if the subject is a Patient, or the empty collection ( i.e., {}) if it is not.

The returned KeyType is implementation dependent, but must be a FHIR primitive type that can be used for efficient joins in the systems underlying data storage. Integers, strings, UUIDs, and other primitive types may be appropriate.

The getReferenceKey function has both required and optional functionality:

  • Implementations MUST support the relative literal form of reference ( e.g., Patient/123), and MAY support other types of references. If the implementation does not support the reference type, or is unable to resolve the reference, it MUST return the empty collection (i.e., {}).
  • Implementations MAY generate a list of unprocessable references through query responses, logging or reporting. The details of how this information would be provided to the user is implementation specific.

See the Joins with Resource and Reference Keys section below for details.

Specifying a Select

A select specifies the content and names for the columns in the view. The content for each column is defined with a FHIRPath expression that returns a specific data element from the FHIR resources. More complex views can be specified to create resource or reference keys, unnest a collection of items returned by a FHIRPath expression, nest or concatenate the results from multiple selects, and so on.

Unnesting Semantics

It is often desirable to unroll the collection returned from a FHIRPath expression into a separate row for each item in the collection. This is done with forEach or forEachOrNull.

For instance, each Patient resource can have multiple addresses, which users can expand into a separate patient_addresses table with one row per address. Each row would still have a patient_id field to know which patient that address row is associated with. You can see this in the PatientAddresses example, which unrolls addresses as described above.

forEach and forEachOrNull apply both to the columns within a select and any nested selects it contains. Therefore, the following selects will produce the same results:

"select": [{
  "forEach": "address",
  "column": [{"name": "zip", "path": "postalCode"}]
}]

"select": [{
  "forEach": "address",
  "select": [{"column": [{"name": "zip", "path": "postalCode"}]}]
}]

While a forEach is similar to an INNER JOIN supported by many SQL engines, a forEachOrNull is analogous to a LEFT OUTER JOIN. forEach will produce a row only if the FHIRPath expression returns one or more items in the collection. On the other hand, forEachOrNull will produce a row even if the FHIRPath expression returns an empty collection. With forEachOrNull, all columns will be included but, if nothing is returned by the forEachOrNull expression, the row produced will have null values.

To illustrate this, the following expression in a Patient view uses forEach, and will therefore return a row for each Patient resource only if the Patient resource has at least one Patient.address, similar to an INNER JOIN in SQL:

"select": [
  {
    "column": [{"name": "id", "path": "getResourceKey()"}]
  },
  {
    "forEach": "address",
    "select": [{"column": [{"name": "zip", "path": "postalCode"}]}]
  }
]

In contrast, this view with forEachOrNull will produce the id column for every Patient resource. If the Patient resource has no Patient.address, there will be a single row for the Patient resource and the zip column will contain null. For Patient resources with one or more Patient.address, the result will be identical to the expression above.

"select": [
  {
    "column": [{"name": "id", "path": "getResourceKey()"}]
  },
  {
    "forEachOrNull": "address",
    "select": [{"column": [{"name": "zip", "path": "postalCode"}]}]
  }
]

Multiple Select Expressions

A ViewDefinition may have multiple selects, which can be organized as siblings or in parent/child relationships. This is typically done as different selects may use different forEach or forEachOrNullexpressions to unroll different parts of the resources.

The multiple rows produced using forEach or forEachOrNull from a selectare joined to others with the following rules:

  • Parent/child selects will repeat values from the parent select for each item in the child select.
  • Sibling selects are effectively cross joined, where each row in each select is duplicated for every row in sibling selects.

The examples illustrate this behavior.

Unions

A select can have an optional unionAll, which contains a list of selects that are used to create a union. unionAll effectively concatenates the results of the nested selects that it contains, but without a guarantee that row ordering will be preserved. Each select contained in the unionAll must produce the same columns including their specified names and FHIR types.

"select": {
  "unionAll": [
    {
      "forEach": "address",
      "column": [
        {"path": "postalCode", "name": "zip"},
        {"path": "true", "name": "is_patient"}
      ]
    },
    {
      "forEach": "contact.address",
      "column": [
        {"path": "postalCode", "name": "zip"},
        {"path": "false", "name": "is_patient"}
      ]
    }
  ]
}

The above example uses forEach to select different data elements from the resources to be included in the union. For other use cases, it is possible to define the columns directly in the select. See the PatientAndContactAddressUnion example for a complete version of the above.

The columns produced from the unionAll list are effectively added to the parent select, following any other columns from its parent for column ordering. See the column ordering section below for details.

The selects in a unionAll MUST have matching columns. Specifically, each nested selection structure MUST produce the same number of columns with the same names and order, and the values for the columns MUST be of the same types as determined by the column types part of this specification.

unionAll behaves similarly to the UNION ALL in SQL and will not filter out duplicate rows. Note that each select can contain only one unionAll list since these items must be combined in a single, logical UNION ALL. Nested selects can be used when multiple unionAlls are needed within a single view.

Composing Multiple Selects and Unions

unionAll produces rows that can be used just like a select expression. These rows can be used by containing selects or unionAlls without needing any special knowledge of how they were produced. This means that unionAll and select operations can be nested with intuitive behavior, similar to how functions can be nested in many programming languages.

For instance, the two expressions below will return the same rows despite the first being a single unionAll and the second being composed of a nested select that contains additional unionAlls.

"select": {
  "unionAll": [
    {
      "forEach": "a",
      "column": [] // snip
    },
    {
      "forEach": "b",
      "column": [] // snip
    },
    {
      "forEach": "c",
      "column": [] // snip
    }
  ]
}

And, the equivalent with a nested structure:

"select": {
  "unionAll": [
    {
      "forEach": "a",
      "column": [] // snip
    },
    {
      "select": {
        "unionAll": [
          {
            "forEach": "b",
            "column": [] // snip
          },
          {
            "forEach": "c",
            "column": [] // snip
          }
        ]
      }
    }
  ]
}

Note the former example is preferred due to its simplicity and the latter is included purely for illustrative purposes.

Column Ordering

View Runners that support column ordering in their output format MUST order the columns of the result according to the rules defined in this section.

selects that have nested selects will place the columns of the parent select before the columns of the nested select, and the columns from a unionAll list are placed last.

To change the column ordering, it is possible to place the columns or the unionAll in a nested select, which can be ordered relative to other nested selects as desired.

For example, the columns in this ViewDefinition will appear in alphabetical order:

{
    "name": "column_order_example",
    "resource": "...",
    "select": [
        {
            "column": [
                {
                    "path": "'A'",
                    "name": "a"
                },
                {
                    "path": "'B'",
                    "name": "b"
                }
            ],
            "select": [
                {
                    "forEach": "aNestedStructure",
                    "column": [
                        {
                            "path": "'C'",
                            "name": "c"
                        },
                        {
                            "path": "'D'",
                            "name": "d"
                        }
                    ]
                }
            ],
            "unionAll": [
                {
                    "column": [
                        {
                            "path": "'E1'",
                            "name": "e"
                        },
                        {
                            "path": "'F1'",
                            "name": "f"
                        }
                    ]
                },
                {
                    "column": [
                        {
                            "path": "'E2'",
                            "name": "e"
                        },
                        {
                            "path": "'F2'",
                            "name": "f"
                        }
                    ]
                }
            ]
        },
        {
            "column": [
                {
                    "path": "'G'",
                    "name": "g"
                },
                {
                    "path": "'H'",
                    "name": "h"
                }
            ]
        }
    ]
}

Column Types

All values in a given column must be of a single data type. The data type can be explicitly specified with the collection and type for a column. In most cases, the data type for a column can be determined by the path expression, allowing users to interactively build and evaluate a ViewDefinition without needing to look up and explicitly specify the data type.

If the column is a primitive type (typical of tabular output), its type is inferred under the following conditions:

  1. If the collection is not set to true, the returned data type must be a single value.
  2. If the path is a series of parent.child.subPath navigation steps from a known data type, either from the root resource or a child of an ofType function, then the data type for each column is determined by the structure definition it comes from.
  3. ​If the terminal expression is one of the supported FHIRPath functions with a defined return type, then the column will be of that data type. For instance, if the path ends in exists() or lowBoundary(), the data type for the column would be boolean or an instant type, respectively.
  4. A path that ends in ofType() will be of the type given to that function.

Note that type inference is an optional feature and some implementations may not support it. Therefore, a ViewDefinition that is intended to be shared between different implementations should have the type for each column set explicitly, even for primitives. It is reasonable for an implementation to treat any non-specified types as strings. Moreover, non-primitive data types will not be supported by all implementations. Therefore, it is important to always explicitly set the type so each column can have its data type easily determined.

Importantly, the above determines the FHIR type produced for the column. How that type is physically manifested depends on the implementation. Implementations may map these to native database types or have each column simply produce a string, as would be done in a CSV-based implementation. See the database type hints section below if finer database-specific type control is needed.

Using Constants

A ViewDefinition may include one or more constants, which are simply values that can be reused in FHIRPath expressions. This can improve readability and reduce redundancy. Constants can be used in path expressions by simply using %[name]. Effectively, these placeholders are replaced by the value of the constant before the FHIRPath expression is evaluated.

This is an example of a constant used in the where constraint of a view:

{
  // <snip>
  "constant": [{
    "name": "bp_code",
    "valueCode": "8480-6"
  }],
  // <snip>
  "where": [{
    "path": "code.coding.exists(system='http://loinc.org' and code=%bp_code)"
  }],
}

Joins with Resource and Reference Keys

While ViewDefinitions do not directly implement joins across resources, the views produced should be easily joined by the database or analytic tools of the user's choice. This can be done by including primary and foreign keys as part of the tabular view output, which can be done with the getResourceKey and getReferenceKey functions.

Users may call getResourceKey to obtain a resources primary key, and call getReferenceKeyto get the corresponding foreign key from a reference pointing at that resource/row.

For example, a minimal view of Patient resources could look like this:

{
  "name": "active_patients",
  "resource": "Patient",
  "select": [{
    "column": [
      {
        "path": "getResourceKey()",
        "name": "id"
      },
      {
        "path": "active"
      }
    ]
  }]
}

A view of Observation resources would then have its own row key and a foreign key to easily join to the view of Patient resources, like this:

{
  "name": "simple_obs",
  "resource": "Observation",
  "select": [{
    "column": [
      {
        "path": "getResourceKey()",
        "name": "id"
      },
      {
        // The `Patient` parameter is optional, but ensures the returned value
        // will either be a patient row key or null.
        "path": "subject.getReferenceKey(Patient)",
        "name": "patient_id"
      }
    ]
  }]
}

Users of the views could then join simple_obs.patient_id to active_patients.id using common join semantics.

Suggested Implementations for getResourceKey() and getReferenceKey()

While getResourceKey and getReferenceKey must return matching values for the same row, how they do so is left to the implementation. This is by design, allowing ViewDefinitions to be run across a wide set of systems that have different data invariants or pre-processing capabilities.

Here are some implementation options to meet different needs:

Approach Details
Return the Resource ID If the system can guarantee that each resource has a simple id and the corresponding references have simple, relative ids that point to it (e.g., Patient/123), getResourceKey and getReferenceKey implementations may simply return those values. This is the simplest case and will apply to many (but not all) systems.
Return a "Primary" Identifier Since the resource id is by definition a system-specific identifier, it may change as FHIR data is exported and loaded between systems, and therefore not be a reliable target for references. For instance, a bulk export from one source system could be loaded into a target system that applies its own ids to the resources, requiring that joins be done on the resource's identifier rather than its id.

In this case, implementations will need to determine row keys based on the resource identifier and corresponding identifiers in the references.

The simplest variation of this is when there is only one identifier for each resource. In other cases, the implementation may be able to select a "primary" identifier, based on the identifier.system namespace, identifier.use code, or other property. For instance, if the primary Identifier.system is example_primary_system, implementations can select the desired identifier to use as a row key by checking for that.

In either case, the resource identifier and corresponding reference identifier can then be converted to a row key, perhaps by building a string or computing a cryptographic hash of the identifiers themselves. The best approach is left to the implementation.
Pre-Process to Create or Resolve Keys The most difficult case is systems where the resource id is a not a reliable row key, and resources have multiple identifiers with no clear way to select one for the key.

In this case, implementations will likely have to fall back to some form of preprocessing to determine appropriate keys. This may be accomplished by:

  • Pre-processing all data to have clear resource ids or "primary" identifiers and using one of the options above.
  • Building some form of cross-link table dynamically within the implementation itself based on the underlying data. For instance, if an implementation's getResourceKey uses a specific identifier system, getReferenceKey could use a pre-built cross-link table to find the appropriate identifier-based key to return.

There are many variations and alternatives to the above. This specification simply asserts that implementations must be able to produce a row key for each resource and a matching key for references pointing at that resource, and intentionally leaves the specific mechanism to the implementation.

Contained Resources

This specification requires implementers to extract contained resources that need to be included in views into independent resources that can then be accessed via getReferenceKey like any other resource. Implementations SHOULD normalize these resources appropriately whenever possible, such as eliminating duplicate resources contained in many parent resources. Note that this may change in a later version of this specification, allowing users to explicitly create separate views for contained resources that could be distinct from top-level resource views.

Contained resources have different semantics than other resources since they don't have an independent identity, and the same logical record may be duplicated across many containing resources. This makes SQL best practices difficult since the data is denormalized and ambiguous. For instance, Patient.generalPractitioner may be a contained resource that may or may not be the same practitioner seen in other Patient resources. Therefore, the approach in this specification requires systems to pre-process the data into normalized, independent resources if needed.

For the same reason, the output from running a ViewDefinition will not include contained resources. For instance, a view of Practitioner resources will include top-level Practitioner resources but not contained Practitioner resources from inside the Patient resources.

Generating Schemas

The output format produced by a View Runner will be technology-specific, such as a SQL database query or a structured file like Parquet. View Runner implementations SHOULD offer a way to compute the output schema from a ViewDefinition when applicable.

For example, a runner that produces a table in a database system could return a "CREATE TABLE" or "CREATE VIEW" statement based on the ViewDefinition, allowing the system to define tables prior to populating them by evaluating the views over data.

This would not apply to outputs that do not have a way of specifying their schema, like CSV files.

Type Hinting with Tags

Since these analytic views are often used as SQL tables, it can be useful to provide database type information to ensure the desired tables or views are created. This is done by tagging fields with database-specific type information.

For instance, we tag a birth date as an ANSI date. This particular view relies on the birth dates being full dates, which is not guaranteed but is common and can simplify analysis in some systems.

{
    "resourceType": "ViewDefinition",
    "name": "patient_birth_date",
    "resource": "Patient",
    "description": "A view of simple patient birth dates",
    "select": [
        {
            "column": [
                {
                    "path": "id"
                },
                {
                    "name": "birth_date",
                    "path": "birthDate",
                    "tags": [
                        {
                            "name": "ansi/type",
                            "value": "DATE"
                        }
                    ]
                }
            ]
        }
    ]
}

Another use case may be for users to select database-specific numeric types.

Behavior is undefined and left to the runner if the expression returns a value that is incompatible with the underlying database type.

Processing Algorithm

The following description provides an algorithm for how to process a FHIR resource as input for a ViewDefinition. Implementations do not need to follow this algorithm directly, but their outputs should be consistent with what this model produces.

Validate Columns (entry point)

Purpose: This step ensures that a ViewDefinition's columns are valid, by setting up a recursive call.

Inputs

  • V: a ViewDefinition to validate
  1. Call ValidateColumns(V, C) according to the recursive step below.

ValidateColumns(S, C) (recursive step)

Purpose: This step ensures that column names are unique across S and disjoint from C

Inputs

  • S: a single Selection Structure
  • C: a list of Columns that exist prior to this call

Outputs

  • Ret: a list of Columns

Errors

  • Column Already Defined
  • Union Branches Inconsistent

  1. Initialize Ret to equal C

  2. For each Column col in S.column[]

    • If a Column with name col.name already exists in Ret, throw "Column Already Defined"
    • Otherwise, append col to Ret

  3. For each Selection Structure sel in S.select[]

    • For each Column c in Validate(sel, Ret)
      • If a Column with name c.name already exists in Ret, throw "Column Already Defined"
      • Otherwise, append c to the end of Ret

  4. If S.unionAll[] is present

    1. Define u0 as Validate(S.unionAll[0], Ret)
    2. For each Selection Structure sel in S.unionAll[]
      • Define u as ValidateColumns(sel, Ret)
        • If the list of names from u0 is different from the list of names from u, throw "Union Branches Inconsistent"
        • Otherwise, continue
    3. For each Column col in u0
      • Append col to Ret

  5. Return Ret

Process a Resource (entry point)

Purpose: This step emits all rows produced by a ViewDefinition on an input Resource, by setting up a recursive call.

Inputs

  • V: a ViewDefinition
  • R: a FHIR Resource to process with V

Emits: one output row at a time

  1. Ensure resource type is correct
    • If R.resourceType is different from V.resource, return immediately without emitting any rows
    • Otherwise, continue
  2. If V.where is defined, ensure constraints are met
    • Evaluate fhirpath(V.where.path, R) to determine whether R is a candidate for V
      • If R is not a candidate for V, return immediately without emitting any rows
      • Otherwise, continue
  3. Emit all rows from Process(S, V)

Process(S, N) (recursive step)

Purpose: This step emits all rows for a given Selection Structure and Node. We first generate sets of "partial rows" (i.e., sets of incomplete column bindings from the various clauses of V) and combine them to emit complete rows. For example, if there are two sets of partial rows:

  • [{"a": 1},{"a": 2}] with bindings for the variable a
  • [{"b": 3},{"b": 4}] with bindings for the variable b

Then the Cartesian product of these sets consists of four complete rows:

[
    { "a": 1, "b": 3 },
    { "a": 1, "b": 4 },
    { "a": 2, "b": 3 },
    { "a": 2, "b": 4 }
]

Inputs

  • S: a Selection Structure
  • N: a Node (element) from a FHIR resource

Errors

  • Multiple values found but not expected for column

Emits: One output row at a time

  1. Define a list of Nodes foci as

    • If S.forEach is defined: fhirpath(S.forEach, N)
    • Else if S.forEachOrNull is defined: fhirpath(S.forEachOrNull, N)
    • Otherwise: [N] (a list with just the input node)

  2. For each element f of foci

    1. Initialize an empty list parts (each element of parts will be a list of partial rows)

    2. Process Columns:

      • For each Column col of S.column, define val as fhirpath(col.path, f)

        1. Define b as a row whose column named col.name takes the value
          • If val was the empty set: null
          • Else if val has a single element e: e
          • Else if col.collection is true: val
          • Else: throw "Multiple values found but not expected for column"
        2. Append [b] to parts
        • (Note: append a list so the final element of parts is now a list containing the single row b).

    3. Process Selects:

      • For each selection structure sel of S.select

        1. Define rows as the collection of all rows emitted by Process(sel, f)
        2. Append rows to parts
        • (Note: do not append the elements but the whole list, so the final element of parts is now the list rows)

    4. Process UnionAlls:

      1. Initialize urows as an empty list of rows
      2. For each selection structure u of S.unionAll
        • For each row r in Process(u, f)
          • Append r to urows
      3. Append urows to parts
      • (Note: do not append the elements but the whole list, so the final element of parts is now the list urows)
    5. For every list of partial rows prows in the Cartesian product of parts
      1. Initialize a blank row r
      2. For each partial row p in prows
        • Add p's column bindings to the row r
      3. Emit the row r
        • (Note: the Cartesian product is always between a Selection Structure and its direct children, not deeper descendants. Because the process is recursive, rows generated by, for example, a .select[0].select[0].select[0] will eventually bubble up to the top level, but the bubbling happens one level at a time.)

  3. If foci is an empty list and S.forEachOrNull is defined (Note: when this condition is met, no rows have been emitted so far)

    1. Initialize a blank row r
    2. For each Column c in ValidateColumns(V, [])
      • Bind the column c.name to null in the row r
    3. Emit the row r

Functional Model

ViewDefinitions can be modeled and implemented using the functional paradigm. In fact, the JavaScript reference implementation takes such an approach. See Functional Model for a detailed examination of this approach.