SQL on FHIR
0.0.1-pre - ci-build
SQL on FHIR, published by HL7. This guide is not an authorized publication; it is the continuous build for version 0.0.1-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
Official URL: http://hl7.org/fhir/uv/sql-on-fhir/StructureDefinition/ShareableViewDefinition | Version: 0.0.1-pre | |||
Draft as of 2024-05-16 | Computable Name: ShareableViewDefinition |
A profile for View Definitions intended to be shared between multiple systems. This requires there be a defined URL, name, and version. Also, each column must have specified type so consuming systems
Shareable View Definitions often also use the TabularViewDefinition profile, requiring that the view be shareable and contain only tabular values, as is common in many databases and analytic tools.
Usage:
Description of Profiles, Differentials, Snapshots and how the different presentations work.
This structure is derived from ViewDefinition
Name | Flags | Card. | Type | Description & Constraints |
---|---|---|---|---|
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 | |
select | ||||
column | ||||
type | 1..1 | uri | A FHIR StructureDefinition URI for the column's type. | |
Documentation for this format |
Name | Flags | Card. | Type | Description & Constraints |
---|---|---|---|---|
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) | |
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) | |
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 |
Documentation for this format |
Path | Conformance | ValueSet | URI |
ViewDefinition.status | required | PublicationStatushttp://hl7.org/fhir/ValueSet/publication-status from the FHIR Standard | |
ViewDefinition.resource | required | ResourceTypehttp://hl7.org/fhir/ValueSet/resource-types from the FHIR Standard | |
ViewDefinition.fhirVersion | required | FHIRVersionhttp://hl7.org/fhir/ValueSet/FHIR-version from the FHIR Standard |
Id | Grade | Path(s) | Details | Requirements |
ele-1 | error | **ALL** elements | All FHIR elements must have a @value or children : hasValue() or (children().count() > id.count()) | |
ext-1 | error | **ALL** extensions | Must have either extensions or value[x], not both : extension.exists() != value.exists() | |
sql-expressions | error | ViewDefinition.select | Can only have at most one of `forEach` or `forEachOrNull`. : (forEach | forEachOrNull).count() <= 1 | |
sql-name | error | ViewDefinition.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. : empty() or matches('^[A-Za-z][A-Za-z0-9_]*$') |
Name | Flags | Card. | Type | Description & Constraints |
---|---|---|---|---|
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. |
title | 0..1 | string | Name for this view definition (human friendly) | |
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. | |
Documentation for this format |
Path | Conformance | ValueSet | URI |
ViewDefinition.status | required | PublicationStatushttp://hl7.org/fhir/ValueSet/publication-status from the FHIR Standard | |
ViewDefinition.resource | required | ResourceTypehttp://hl7.org/fhir/ValueSet/resource-types from the FHIR Standard | |
ViewDefinition.fhirVersion | required | FHIRVersionhttp://hl7.org/fhir/ValueSet/FHIR-version from the FHIR Standard |
Id | Grade | Path(s) | Details | Requirements |
ele-1 | error | **ALL** elements | All FHIR elements must have a @value or children : hasValue() or (children().count() > id.count()) | |
ext-1 | error | **ALL** extensions | Must have either extensions or value[x], not both : extension.exists() != value.exists() | |
sql-expressions | error | ViewDefinition.select | Can only have at most one of `forEach` or `forEachOrNull`. : (forEach | forEachOrNull).count() <= 1 | |
sql-name | error | ViewDefinition.name, ViewDefinition.constant.name, ViewDefinition.select.column.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. : empty() or matches('^[A-Za-z][A-Za-z0-9_]*$') |
This structure is derived from ViewDefinition
Differential View
This structure is derived from ViewDefinition
Name | Flags | Card. | Type | Description & Constraints |
---|---|---|---|---|
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 | |
select | ||||
column | ||||
type | 1..1 | uri | A FHIR StructureDefinition URI for the column's type. | |
Documentation for this format |
Key Elements View
Name | Flags | Card. | Type | Description & Constraints |
---|---|---|---|---|
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) | |
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) | |
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 |
Documentation for this format |
Path | Conformance | ValueSet | URI |
ViewDefinition.status | required | PublicationStatushttp://hl7.org/fhir/ValueSet/publication-status from the FHIR Standard | |
ViewDefinition.resource | required | ResourceTypehttp://hl7.org/fhir/ValueSet/resource-types from the FHIR Standard | |
ViewDefinition.fhirVersion | required | FHIRVersionhttp://hl7.org/fhir/ValueSet/FHIR-version from the FHIR Standard |
Id | Grade | Path(s) | Details | Requirements |
ele-1 | error | **ALL** elements | All FHIR elements must have a @value or children : hasValue() or (children().count() > id.count()) | |
ext-1 | error | **ALL** extensions | Must have either extensions or value[x], not both : extension.exists() != value.exists() | |
sql-expressions | error | ViewDefinition.select | Can only have at most one of `forEach` or `forEachOrNull`. : (forEach | forEachOrNull).count() <= 1 | |
sql-name | error | ViewDefinition.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. : empty() or matches('^[A-Za-z][A-Za-z0-9_]*$') |
Snapshot View
Name | Flags | Card. | Type | Description & Constraints |
---|---|---|---|---|
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. |
title | 0..1 | string | Name for this view definition (human friendly) | |
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. | |
Documentation for this format |
Path | Conformance | ValueSet | URI |
ViewDefinition.status | required | PublicationStatushttp://hl7.org/fhir/ValueSet/publication-status from the FHIR Standard | |
ViewDefinition.resource | required | ResourceTypehttp://hl7.org/fhir/ValueSet/resource-types from the FHIR Standard | |
ViewDefinition.fhirVersion | required | FHIRVersionhttp://hl7.org/fhir/ValueSet/FHIR-version from the FHIR Standard |
Id | Grade | Path(s) | Details | Requirements |
ele-1 | error | **ALL** elements | All FHIR elements must have a @value or children : hasValue() or (children().count() > id.count()) | |
ext-1 | error | **ALL** extensions | Must have either extensions or value[x], not both : extension.exists() != value.exists() | |
sql-expressions | error | ViewDefinition.select | Can only have at most one of `forEach` or `forEachOrNull`. : (forEach | forEachOrNull).count() <= 1 | |
sql-name | error | ViewDefinition.name, ViewDefinition.constant.name, ViewDefinition.select.column.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. : empty() or matches('^[A-Za-z][A-Za-z0-9_]*$') |
This structure is derived from ViewDefinition