SQL on FHIR, published by HL7 International / FHIR Infrastructure. This guide is not an authorized publication; it is the continuous build for version 3.0.0-ballot built by the FHIR (HL7® FHIR® Standard) CI Build. This version is based on the current content of https://github.com/HL7/sql-on-fhir/ and changes regularly. See the Directory of published versions
| Official URL: http://hl7.org/fhir/uv/sql-on-fhir/OperationDefinition/SQLRun | Version: 3.0.0-ballot | ||||
| Standards status: Trial-use | Maturity Level: 2 | Computable Name: SQLRun | |||
| Other Identifiers: OID:2.16.840.1.113883.4.642.40.77.33.6 | |||||
Execute a ViewDefinition, SQLQuery Library or SQLView Library and return the result in the requested output format.
Execute one subject - a ViewDefinition, a SQLQuery Library or a SQLView Library - synchronously, returning the result in the requested output format.
Use Cases:
Endpoint:
The operation is invoked at the system level only. The subject is named by a parameter rather than by the request path, so one endpoint serves all three artifact kinds.
| Endpoint | Subject named by |
|---|---|
GET [base]/$sql-run |
subjectCanonical or subjectReference, naming a ViewDefinition, SQLQuery or SQLView |
POST [base]/$sql-run |
subjectCanonical, subjectReference or subjectResource, naming a ViewDefinition, SQLQuery or SQLView |
GET is available whenever every supplied input parameter is primitive.
subjectResource, parameters, context and resource each carry a resource
and so require POST.
Execution Flow:
subjectCanonical, subjectReference or subjectResourceresource values where present and otherwise against server datarelatedArtifact dependency resolved first, preferring an artifact supplied inline via context over one the server can itself resolve, and each resolved artifact bound to the SQL identifier in the dependency's labelparameters values to the SQL placeholders the Library declares (SQL subjects only)Parameters resource for _format=fhir)Implementations SHALL ensure parameter values are safely bound to queries and not subject to SQL injection. Use parameterized queries or equivalent safe binding mechanisms where available. Simple string interpolation SHALL NOT be used to implement parameter binding.
Language: en
URL: [base]/$sql-run
| Use | Name | Scope | Cardinality | Type | Binding | Documentation |
| IN | subjectCanonical | 0..1 | canonical (View Definition, SQL Query Library, SQL View Library) | Canonical URL of the ViewDefinition, SQLQuery Library or SQLView Library to execute, optionally with a |version suffix pinning a version. Exactly one of subjectCanonical, subjectReference and subjectResource is supplied; supplying none, or more than one, is rejected with 400 Bad Request. | ||
| IN | subjectReference | 0..1 | Reference (View Definition, SQL Query Library, SQL View Library) | Literal location of the subject to execute: a relative URL on this server, or an absolute URL. Not a canonical URL; use subjectCanonical for that. Exactly one of subjectCanonical, subjectReference and subjectResource is supplied; supplying none, or more than one, is rejected with 400 Bad Request. | ||
| IN | subjectResource | 0..1 | CanonicalResource (View Definition, SQL Query Library, SQL View Library) | Inline ViewDefinition, SQLQuery Library or SQLView Library to execute. Exactly one of subjectCanonical, subjectReference and subjectResource is supplied; supplying none, or more than one, is rejected with 400 Bad Request. Carries a resource, so it requires POST. The declared type is CanonicalResource because ViewDefinition is a logical model rather than a FHIR resource; see Common Operation Behavior (operations-common.html#declared-type). | ||
| IN | parameters | 0..1 | Parameters | Input parameter values for the subject, bound by name to the parameters the Library declares (Library.parameter.name), using the value[x] type matching each declared type. Permitted only where the subject is a SQLQuery or SQLView; supplying it where the subject is a ViewDefinition is rejected with 400 Bad Request, because a ViewDefinition declares no parameters. Carries a resource, so it requires POST. | ||
| IN | context | 0..* | CanonicalResource (View Definition, SQL View Library) | Supporting artifacts the server cannot itself resolve, supplied inline and matched by canonical URL against the dependencies in the subject's transitive relatedArtifact graph. Accepts inline resources only; there is no context by canonical URL, because a URL is exactly what the server has already failed to resolve. Carries a resource, so it requires POST. See Common Operation Behavior (operations-common.html#context). | ||
| IN | resource | 0..* | Resource | FHIR resources to transform instead of using server data. Repeatable. A Bundle supplied here is unwrapped: the view runs against each Bundle.entry[*].resource rather than against the Bundle itself. Permitted only where the subject is a ViewDefinition; supplying it where the subject is a SQLQuery or SQLView is rejected with 400 Bad Request, because how inline resources reach each dependency view is not specified. Carries a resource, so it requires POST. See OperationDefinition-SQLRun notes (Resource parameter and Bundle inputs). | ||
| IN | _format | 0..1 | code | Output Format Codes (Extensible) | Output format for the result (json, ndjson, csv, parquet, fhir). Use fhir to return results as a FHIR Parameters resource. Optional; if omitted, the server returns ndjson by default. See Common Operation Behavior (operations-common.html). | |
| IN | header | 0..1 | boolean | Include CSV headers (default true). Applies only when csv output is requested. | ||
| IN | patient | 0..* | Reference | Restrict the FHIR resources feeding the view, before projection, to the supplied patient(s). Where the subject is a SQLQuery or SQLView, that means before the SQL executes. An unresolvable patient is rejected with 400 Bad Request. See Common Operation Behavior (operations-common.html#patient-filter). | ||
| IN | group | 0..* | Reference | Restrict the FHIR resources feeding the view, before projection, to members of the supplied group(s). Where the subject is a SQLQuery or SQLView, that means before the SQL executes. An unresolvable group is rejected with 400 Bad Request. See Common Operation Behavior (operations-common.html#group-filter). | ||
| IN | _since | 0..1 | instant | Include only resources whose state changed after this instant. See Common Operation Behavior (operations-common.html#since-filter). | ||
| IN | source | 0..1 | string | External data source to read from instead of the server's own data (for example a URI or bucket name). Where the subject is a SQLQuery or SQLView, this is where the ViewDefinition tables the query selects from are found. | ||
| IN | _limit | 0..1 | integer | Maximum number of rows to return. | ||
| OUT | return | 1..1 | Binary | Result rows in the requested output format, returned as a raw binary stream in the format's native media type, not a serialized Binary resource envelope. When _format=fhir is requested, the response is a Parameters resource instead. See Common Operation Behavior (operations-common.html). |
subjectResource, parameters, context or
resource is supplied, since each carries a resource that cannot be expressed
as a query string.$sql-run is a safe operation, so base FHIR permits GET whenever the supplied
in-parameters are all primitive. The subject is named by a parameter at every
invocation, so a GET is available for a ViewDefinition subject and a SQL
subject alike.
GET:
subjectCanonical - canonical URL of the ViewDefinition, SQLQuery or
SQLView, with any | percent-encoded as %7CsubjectReference - literal location of the subject on the server_format - output format specificationheader - include CSV headers (for CSV format)patient - filter by patient reference, repeated to name several patientsgroup - filter by group membership_since - filter by last updated time_limit - limit the number of result rowssource - external data sourcePOST instead of GET when you need to:
subjectResourceparameterscontextresourceEach of those four carries a resource, which is the reason it cannot be
expressed as a query string. Supplying one over GET is rejected with
400 Bad Request.
The operation can process data from:
resource parameter in the request, where the subject is a ViewDefinitionsource parameterThe following input parameters are passed as query parameters on a GET, or
inside a Parameters resource in the request body on a POST.
| Name | Type | Min | Max | Description |
|---|---|---|---|---|
| subjectCanonical | canonical | 0¹ | 1 | Canonical URL of the subject. Details |
| subjectReference | Reference | 0¹ | 1 | Literal location of the subject on the server. Details |
| subjectResource | ViewDefinition | SQLQuery | SQLView² | 0¹ | 1 | Inline subject resource. Details |
| parameters | Parameters | 0 | 1 | Parameter values bound by name to those the Library declares; requires a SQL subject. Details |
| context | ViewDefinition | SQLView² | 0 | * | Inline supporting artifact, matched to a dependency by canonical URL. Details |
| resource | Resource | 0 | * | FHIR resources to transform; requires a ViewDefinition subject. Details |
| _format | code | 0 | 1 | Output format: json, ndjson, csv, parquet, fhir. Details |
| header | boolean | 0 | 1 | Include CSV headers (default: true). Only applies to csv format |
| patient | Reference | 0 | * | Filter by patient reference, repeated to name several patients. Details |
| group | Reference | 0 | * | Filter by group membership. Details |
| _since | instant | 0 | 1 | Include only resources whose state changed after this instant. Details |
| source | string | 0 | 1 | External data source (e.g. URI, bucket name). If absent, uses server data |
| _limit | integer | 0 | 1 | Maximum number of rows to return. Details |
¹ Exactly one of subjectCanonical, subjectReference or subjectResource is
required. See Naming the subject.
² Declared as CanonicalResource in the OperationDefinition; see
Why the declared type is CanonicalResource.
clientTrackingId is not offered on this operation, and it accepts one subject
rather than a repeating set; see
Parameters that do not apply to every operation.
| Name | Type | Description |
|---|---|---|
| return | Binary | Result rows as a raw stream in the format's native media type, not a serialized Binary envelope (a Parameters resource when _format=fhir is requested). See Return Representation |
The artifact to execute is named in exactly one of three ways, each with its own parameter so that the intended meaning is carried by the parameter's type rather than inferred from the shape of a string. All three admit a ViewDefinition, a SQLQuery Library or a SQLView Library, so the naming form is chosen independently of the subject's kind:
| Parameter | Type | Names the subject by |
|---|---|---|
subjectCanonical |
canonical |
Its canonical URL, optionally with a \|version suffix pinning a version (e.g. http://example.org/ViewDefinition/patient_demographics\|2.0.0). Absent a suffix, the server selects a version according to FHIR's canonical resolution rules |
subjectReference |
Reference |
A literal location: a relative URL on this server (e.g. ViewDefinition/123 or Library/patient-bp-query) or an absolute URL. This is not a canonical URL |
subjectResource |
ViewDefinition | SQLQuery | SQLView² | Carrying the artifact itself in the request |
² subjectResource is declared as CanonicalResource in the
OperationDefinition. ViewDefinition is a logical model in this guide rather than
a FHIR resource, so ViewDefinition is not a value parameter.type accepts;
CanonicalResource is the narrowest declared type admitting all three kinds, and
the real constraint is carried by targetProfile. See
Why the declared type is CanonicalResource.
A request SHALL supply exactly one of the
three. Supplying none, or more than one, is rejected with
400 Bad Request and an OperationOutcome naming the problem.
A subjectCanonical or subjectReference the server cannot resolve is rejected
with 404 Not Found and an OperationOutcome. A resolved artifact conforming to
none of the three profiles is rejected with 422 Unprocessable Entity.
What the subject resolves to determines how it is processed, and which of the
conditional parameters apply: a ViewDefinition is evaluated directly and may be
fed inline resources through resource; a
SQLQuery or SQLView has its dependency graph resolved first and may have values
bound through parameters.
How a server resolves a canonical URL or an absolute reference - from a local artifact registry, by dereferencing the URL, or not at all - is an implementation matter. A server that supports only some of these parameters declares the subset it supports as described in Declaring partial operation support.
context)Where the subject is a SQLQuery or SQLView, the tables it selects from are named
by its relatedArtifact entries and are normally resolved by the server. Where
the server cannot resolve one - typically because the artifact exists only on the
client - the client supplies it inline with the repeating context parameter,
which accepts a ViewDefinition or a
SQLView.
The matching, precedence and error rules are specified once in
Supporting artifacts and apply identically here
and on $sql-export. That section governs;
in outline, the supplied entries are matched by canonical URL against every
dependency in the subject's transitive dependency graph, a supplied entry
outranks an artifact the server could itself resolve, an entry that cannot be
bound or matches nothing is rejected with 400 Bad Request, and a dependency
neither supplied nor resolvable is rejected with 404 Not Found.
Supplying every dependency inline alongside an inline subjectResource makes a
fully ad-hoc query possible, with nothing stored on the server.
context supplies the views a query reads from, not the FHIR resources those
views project. The resource parameter
carries the latter, and requires a ViewDefinition subject: extending it to a SQL
subject would need its own semantics for how supplied resources reach each
dependency view, which is deliberately deferred. See
Parameters that do not apply to every operation.
A ViewDefinition subject contributes no dependencies at all, so a request naming
one has nothing for context to match; a supplied entry would be unmatched and
rejected with 400 Bad Request.
The resource parameter is repeatable and carries the discrete FHIR resources
to transform instead of using server data. It is permitted only where the
subject is a ViewDefinition; supplying it with a SQLQuery or SQLView subject is
rejected with 400 Bad Request and an OperationOutcome naming resource,
because how inline resources would reach each dependency view of a query is not
specified.
Because a Bundle is itself a Resource, a Bundle satisfies the parameter's
Resource type. To avoid ambiguity, the following rule applies:
When a resource value is a Bundle, the server
SHALL unwrap it and run the ViewDefinition against each
Bundle.entry[*].resource, exactly as if those entries had been supplied as
individual repeated resource values. The Bundle itself is not treated
as an input resource for the ViewDefinition.
Unwrapping is applied one level deep. Resources within the bundle are evaluated
against the ViewDefinition's resource type just like directly supplied
resources: entries whose type does not match the ViewDefinition's resource are
ignored. Mixing discrete resource values and Bundle values in the same
request is permitted; the effective input is the union of the discrete resources
and every unwrapped bundle entry.
patient, group and _since restrict the data the subject sees. They carry
the same meaning here as on
$sql-export, and are specified once in
Filtering:
| Parameter | Max | Restricts the data to |
|---|---|---|
patient |
* | The patient compartments of the supplied patients (details) |
group |
* | Members of the supplied Groups (details) |
_since |
1 | Resources whose state changed after the supplied instant (details) |
The filter applies to the FHIR resources feeding a view before projection. Where the subject is a SQLQuery or SQLView, that means it applies to the resources feeding the query's dependency views, before the SQL executes: the SQL sees tables already narrowed to the requested scope, rather than being expected to express the filter itself.
A patient or group naming a resource the server cannot find is rejected with
400 Bad Request; see
Status code for a value that cannot be resolved.
_limit caps the rows the server returns to the client. Its semantics - the
server's option to impose a smaller maximum, the application of the cap after the
subject has been evaluated, and that returning fewer rows is not an error - are
specified once in
Row limit and apply identically here.
Where the subject is a SQLQuery or SQLView, "after the subject has been
evaluated" includes any in-query LIMIT: implementations are free to push the
cap down into the SQL as an optimization, but the observable behaviour is
post-evaluation. A worked example is given under
Capping result rows with _limit.
The supported formats (json, ndjson, csv, parquet, fhir), the default,
the Accept-vs-_format precedence rule, the raw-vs-envelope representation
axis, and transfer framing are defined in
Common Operation Behavior and apply identically to
this operation:
json, ndjson and
csv by default; servers MAY support parquet and fhir, and SHALL document
supported formats in the CapabilityStatement._format is omitted (and no format is
derivable from Accept), the server SHALL return the result in ndjson
format._format is supplied, its value SHALL take
precedence over Accept._format=fhir returns a Parameters resource with one repeating row per
result row, using the
SQL to FHIR type mapping.Transfer-Encoding: chunked; chunked transfer is independent of the
format. See
Streaming and Transfer Encoding.Every parameter here is primitive, so the invocation fits in a query string. The
| separating the canonical URL from its version is percent-encoded as %7C:
GET /$sql-run?subjectCanonical=http%3A%2F%2Fexample.org%2FViewDefinition%2Fpatient_demographics%7C2.0.0&patient=Patient/123&_limit=10&_format=csv HTTP/1.1
HTTP/1.1 200 OK
Content-Type: text/csv
Transfer-Encoding: chunked
id,birthDate,family,given
pt-1,1990-01-15,Smith,John
pt-2,1985-03-22,Johnson,Mary
Omitting %7C2.0.0 selects a version according to FHIR's canonical resolution
rules. A canonical URL the server cannot resolve returns 404 Not Found.
The same endpoint and the same parameter serve a SQL subject; only what the canonical URL resolves to differs:
GET /$sql-run?subjectCanonical=http%3A%2F%2Fexample.org%2FLibrary%2Fpatient-bp-query%7C1.0.0&_format=csv HTTP/1.1
Accept: text/csv
HTTP/1.1 200 OK
Content-Type: text/csv
patient_id,systolic,effective_date
Patient/123,120,2024-01-15
Patient/123,118,2024-02-20
Supplying parameters, context, resource or subjectResource takes the
request outside the GET-available subset, because each carries a resource; use
POST in that case.
POST /$sql-run HTTP/1.1
Content-Type: application/fhir+json
{
"resourceType": "Parameters",
"parameter": [
{ "name": "subjectCanonical", "valueCanonical": "http://example.org/Library/patient-bp-query|1.0.0" },
{ "name": "parameters", "resource": {
"resourceType": "Parameters",
"parameter": [
{ "name": "from_date", "valueDate": "2026-01-01" }
]
}},
{ "name": "_format", "valueCode": "ndjson" }
]
}
HTTP/1.1 200 OK
Content-Type: application/x-ndjson
{"patient_id":"Patient/123","systolic":120,"effective_date":"2026-01-15"}
{"patient_id":"Patient/123","systolic":118,"effective_date":"2026-02-20"}
The values are bound by name to the parameters the Library declares. Supplying
parameters where the subject is a ViewDefinition is rejected with
400 Bad Request.
POST /$sql-run HTTP/1.1
Content-Type: application/fhir+json
{
"resourceType": "Parameters",
"parameter": [
{ "name": "_format", "valueCode": "json" },
{ "name": "subjectReference", "valueReference": {
"reference": "Library/patient-bp-query"
}},
{ "name": "parameters", "resource": {
"resourceType": "Parameters",
"parameter": [
{ "name": "patient_id", "valueString": "Patient/123" }
]
}}
]
}
Pass the ViewDefinition itself in the request as subjectResource:
POST /$sql-run HTTP/1.1
Accept: application/json
Content-Type: application/fhir+json
{
"resourceType": "Parameters",
"parameter": [{
"name": "subjectResource",
"resource": {
"resourceType": "ViewDefinition",
"resource": "Patient",
"select": [{
"column": [
{"name": "id", "type": "id", "path": "getResourceKey()"},
{"name": "birthDate", "type": "date", "path": "birthDate"},
{"name": "family", "type": "string", "path": "name.family"},
{"name": "given", "type": "string", "path": "name.given"}
]
}]
}
}]
}
HTTP/1.1 200 OK
Content-Type: application/json
[
{"id": "pt-1", "birthDate": "1990-01-15", "family": "Smith", "given": "John"},
{"id": "pt-2", "birthDate": "1985-03-22", "family": "Johnson", "given": "Mary"},
{"id": "pt-3", "birthDate": "1992-07-08", "family": "Williams", "given": "Robert"}
]
The subject is an inline ViewDefinition and the data is supplied in the same request, so nothing is stored on the server:
POST /$sql-run HTTP/1.1
Accept: text/csv
Content-Type: application/fhir+json
{
"resourceType": "Parameters",
"parameter": [{
"name": "subjectResource",
"resource": {
"resourceType": "ViewDefinition",
"resource": "Patient",
"select": [{
"column": [
{"name": "id", "type": "id", "path": "getResourceKey()"},
{"name": "birthDate", "type": "date", "path": "birthDate"},
{"name": "family", "type": "string", "path": "name.family"},
{"name": "given", "type": "string", "path": "name.given"}
]
}]
}
},
{
"name": "resource",
"resource": {
"resourceType": "Patient",
"id": "pt-1",
"name": [{ "use": "official", "family": "Cole", "given": ["Joanie"] }],
"birthDate": "2012-03-30"
}
},
{
"name": "resource",
"resource": {
"resourceType": "Patient",
"id": "pt-2",
"name": [{ "use": "official", "family": "Doe", "given": ["John"] }],
"birthDate": "2012-03-30"
}
}]
}
HTTP/1.1 200 OK
Content-Type: text/csv
id,birthDate,family,given
pt-1,2012-03-30,Cole,Joanie
pt-2,2012-03-30,Doe,John
A Bundle supplied as a resource value is unwrapped one level; the
ViewDefinition runs against each entry. This request is equivalent to the one
above, which passed the two Patients as discrete resource values:
POST /$sql-run HTTP/1.1
Accept: text/csv
Content-Type: application/fhir+json
{
"resourceType": "Parameters",
"parameter": [{
"name": "subjectResource",
"resource": {
"resourceType": "ViewDefinition",
"resource": "Patient",
"select": [{
"column": [
{"name": "id", "type": "id", "path": "getResourceKey()"},
{"name": "family", "type": "string", "path": "name.family"}
]
}]
}
},
{
"name": "resource",
"resource": {
"resourceType": "Bundle",
"type": "collection",
"entry": [
{ "resource": { "resourceType": "Patient", "id": "pt-1", "name": [{"family": "Cole"}] } },
{ "resource": { "resourceType": "Patient", "id": "pt-2", "name": [{"family": "Doe"}] } }
]
}
},
{ "name": "_format", "valueCode": "csv" }]
}
HTTP/1.1 200 OK
Content-Type: text/csv
id,family
pt-1,Cole
pt-2,Doe
Supplying resource alongside a SQLQuery or SQLView subject is rejected with
400 Bad Request.
Nothing is stored on the server. The query is supplied as subjectResource and
the ViewDefinition its relatedArtifact entry depends on is supplied as
context, matched to that entry by url and bound to table p:
POST /$sql-run HTTP/1.1
Content-Type: application/fhir+json
{
"resourceType": "Parameters",
"parameter": [
{ "name": "_format", "valueCode": "csv" },
{ "name": "subjectResource", "resource": {
"resourceType": "Library",
"meta": { "profile": ["http://hl7.org/fhir/uv/sql-on-fhir/StructureDefinition/SQLQuery"] },
"type": { "coding": [{ "system": "http://hl7.org/fhir/uv/sql-on-fhir/CodeSystem/LibraryTypesCodes", "code": "sql-query" }] },
"status": "active",
"relatedArtifact": [
{ "type": "depends-on", "resource": "https://example.org/ViewDefinition/patient_view", "label": "p" }
],
"content": [{
"contentType": "application/sql",
"data": "U0VMRUNUIHAuaWQsIHAubmFtZSBGUk9NIHAgV0hFUkUgcC5hY3RpdmUgPSB0cnVl",
"extension": [{
"url": "http://hl7.org/fhir/uv/sql-on-fhir/StructureDefinition/sql-text",
"valueString": "SELECT p.id, p.name FROM p WHERE p.active = true"
}]
}]
}},
{ "name": "context", "resource": {
"resourceType": "ViewDefinition",
"url": "https://example.org/ViewDefinition/patient_view",
"status": "active",
"resource": "Patient",
"select": [{ "column": [
{ "name": "id", "path": "getResourceKey()", "type": "string" },
{ "name": "name", "path": "name.family.first()", "type": "string" },
{ "name": "active", "path": "active", "type": "boolean" }
]}]
}}
]
}
HTTP/1.1 200 OK
Content-Type: text/csv
id,name
pt-1,Smith
pt-2,Johnson
A supplied SQLView brings dependencies of its own, so the entries are matched
against the whole transitive graph. Here the first entry binds to table ap;
traversing it reveals a dependency that the second entry satisfies as table p.
Both entries were selected, so neither is unmatched. The resources are
abbreviated to the elements that drive matching:
POST /$sql-run HTTP/1.1
Content-Type: application/fhir+json
{
"resourceType": "Parameters",
"parameter": [
{ "name": "subjectResource", "resource": {
"resourceType": "Library",
"relatedArtifact": [
{ "type": "depends-on", "resource": "https://example.org/SQLView/active_patients", "label": "ap" }
]
}},
{ "name": "context", "resource": {
"resourceType": "Library",
"url": "https://example.org/SQLView/active_patients",
"relatedArtifact": [
{ "type": "depends-on", "resource": "https://example.org/ViewDefinition/patient_view", "label": "p" }
]
}},
{ "name": "context", "resource": {
"resourceType": "ViewDefinition",
"url": "https://example.org/ViewDefinition/patient_view"
}}
]
}
A typo in a supplied url binds to no dependency, and is reported where the
mistake was made rather than resurfacing later as an SQL error naming table p
as missing:
POST /$sql-run HTTP/1.1
Content-Type: application/fhir+json
{
"resourceType": "Parameters",
"parameter": [
{ "name": "subjectResource", "resource": {
"resourceType": "Library",
"relatedArtifact": [
{ "type": "depends-on", "resource": "https://example.org/ViewDefinition/patient_view", "label": "p" }
]
}},
{ "name": "context", "resource": {
"resourceType": "ViewDefinition",
"url": "https://example.org/ViewDefinition/patient_veiw"
}}
]
}
HTTP/1.1 400 Bad Request
Content-Type: application/fhir+json
{
"resourceType": "OperationOutcome",
"issue": [{
"severity": "error",
"code": "invalid",
"diagnostics": "Supplied context entry 'https://example.org/ViewDefinition/patient_veiw' does not match any relatedArtifact dependency of the subject",
"expression": ["context"]
}]
}
patient repeats, so a cohort of a few known patients needs no Group:
GET /$sql-run?subjectCanonical=http%3A%2F%2Fexample.org%2FViewDefinition%2Fencounters&patient=Patient/123&patient=Patient/456&_format=csv HTTP/1.1
HTTP/1.1 200 OK
Content-Type: text/csv
id,patient,status,period_start
enc-1,Patient/123,finished,2023-01-15T10:00:00Z
enc-4,Patient/456,finished,2023-04-02T09:00:00Z
The result is restricted to those two patients' compartments. Over POST the
same filter is expressed by repeating the patient parameter in the
Parameters body. The filters apply to the resources feeding the view before
projection, and where the subject is a query, before the SQL executes.
_limitUse _limit to ask the server to return at most a given number of rows. The
server may return fewer rows if the subject yields fewer or if its configured
maximum is smaller; see Row Limit for the full semantics.
POST /$sql-run HTTP/1.1
Content-Type: application/fhir+json
{
"resourceType": "Parameters",
"parameter": [
{ "name": "subjectReference", "valueReference": { "reference": "Library/patient-bp-query" } },
{ "name": "_format", "valueCode": "csv" },
{ "name": "_limit", "valueInteger": 100 }
]
}
_format omitted)When _format is omitted and no format is derivable from Accept, the server
returns the result in ndjson format:
POST /$sql-run HTTP/1.1
Content-Type: application/fhir+json
{
"resourceType": "Parameters",
"parameter": [
{ "name": "subjectReference", "valueReference": { "reference": "Library/patient-bp-query" } },
{ "name": "parameters", "resource": {
"resourceType": "Parameters",
"parameter": [
{ "name": "patient_id", "valueString": "Patient/123" }
]
}}
]
}
HTTP/1.1 200 OK
Content-Type: application/x-ndjson
{"patient_id":"Patient/123","systolic":120,"effective_date":"2024-01-15"}
{"patient_id":"Patient/123","systolic":118,"effective_date":"2024-02-20"}
For flat formats (csv, json, ndjson, parquet), the response body is the
raw payload in the format's native media type (the Binary stream), not a
serialized Binary resource envelope; Content-Type is set to that media type.
The response MAY be sent with
Transfer-Encoding: chunked regardless of format.
See Return Representation and
Streaming.
HTTP/1.1 200 OK
Content-Type: text/csv
patient_id,systolic,effective_date
Patient/123,120,2024-01-15
Patient/123,118,2024-02-20
When _format=fhir, the response is a FHIR Parameters resource with each row as a
repeating row parameter.
POST /$sql-run HTTP/1.1
Content-Type: application/fhir+json
{
"resourceType": "Parameters",
"parameter": [
{ "name": "subjectReference", "valueReference": { "reference": "Library/patient-bp-query" } },
{ "name": "_format", "valueCode": "fhir" },
{ "name": "parameters", "resource": {
"resourceType": "Parameters",
"parameter": [
{ "name": "patient_id", "valueString": "Patient/123" }
]
}}
]
}
Response:
{
"resourceType": "Parameters",
"parameter": [
{
"name": "row",
"part": [
{ "name": "patient_id", "valueString": "Patient/123" },
{ "name": "systolic", "valueInteger": 120 },
{ "name": "effective_date", "valueDate": "2024-01-15" }
]
},
{
"name": "row",
"part": [
{ "name": "patient_id", "valueString": "Patient/123" },
{ "name": "systolic", "valueInteger": 118 },
{ "name": "effective_date", "valueDate": "2024-02-20" }
]
}
]
}
When a subject returns zero rows, the response is a Parameters resource with no
parameter elements:
{
"resourceType": "Parameters"
}
When _format=fhir, each result column SHALL be
encoded using a FHIR value[x] type. The following table defines the mapping from
ISO/IEC 9075 SQL types to FHIR
parameter value types.
| ISO/IEC 9075 SQL type | FHIR value type |
|---|---|
| BOOLEAN | valueBoolean |
| TINYINT, SMALLINT, INT, INTEGER | valueInteger |
| BIGINT | valueInteger64 |
| DECIMAL, NUMERIC | valueDecimal |
| REAL | valueDecimal |
| FLOAT, DOUBLE PRECISION | valueDecimal |
| CHARACTER, CHARACTER VARYING, CHARACTER LARGE OBJECT | valueString |
| BINARY, BINARY VARYING, BINARY LARGE OBJECT | valueBase64Binary |
| DATE | valueDate |
| TIME, TIME WITH TIME ZONE | valueTime |
| TIMESTAMP | valueDateTime |
| TIMESTAMP WITH TIME ZONE | valueInstant |
SQL NULL values are represented by omitting the corresponding part from the row parameter.
Conversion of REAL, FLOAT, and DOUBLE PRECISION values to valueDecimal may
introduce representation artifacts due to the difference between binary and
decimal floating point.
TIMESTAMP WITH TIME ZONE values may carry sub-millisecond precision (e.g.
microseconds), but FHIR instant supports at most millisecond precision.
Implementations SHOULD round to the nearest
millisecond when converting to valueInstant.
TIMESTAMP (without time zone) values are converted to valueDateTime without a
timezone offset. FHIR dateTime permits values with or without a timezone, so
the absence of timezone information is preserved rather than trying to infer a
time zone.
ISO/IEC 9075 types not listed in this table (such as INTERVAL, ARRAY, XML, ROW,
and MULTISET) are not supported. If a query produces a result
column with an unsupported type, the server SHALL return a
422 Unprocessable Entity error.
Query authors can work around this by casting unsupported types to a supported
type within the SQL query.
Parameter values are passed as a nested Parameters resource, following the same
pattern as the
CQL $evaluate operation.
See Parameter Types on the
SQLQuery profile for the binding rules and the mapping from
Library.parameter.type to the value[x] element to use.
parameters binds by name to the parameters the subject declares in
Library.parameter, so it requires a SQLQuery or SQLView subject. A
ViewDefinition declares no Library.parameter, so there is nothing for a value
to bind to, and supplying parameters alongside a ViewDefinition subject is
rejected with 400 Bad Request and an OperationOutcome naming parameters.
Binding values to a ViewDefinition's constant elements is deliberately out of
scope. A ViewDefinition.constant already carries a value, so supplying one
here would be substitution of a set value rather than binding of an unbound
placeholder - a different semantic that deserves its own proposal.
| Status | issue.code |
expression |
Condition |
|---|---|---|---|
400 Bad Request |
required |
subject | No subject supplied |
400 Bad Request |
invalid |
subject | More than one of subjectCanonical, subjectReference and subjectResource supplied |
400 Bad Request |
invalid |
parameters |
Supplied where the subject is a ViewDefinition |
400 Bad Request |
invalid |
parameters |
A parameter name the subject does not declare, or a value whose type does not match the declared type |
400 Bad Request |
invalid |
resource |
Supplied where the subject is a SQLQuery or SQLView |
400 Bad Request |
invalid |
context |
An entry with no url, two entries sharing a url, or an entry matching no dependency of the subject |
400 Bad Request |
invalid |
the parameter | A resource-carrying parameter supplied over GET |
400 Bad Request |
not-found |
patient |
A patient naming a resource the server cannot find (see Status code for a value that cannot be resolved) |
400 Bad Request |
not-found |
group |
A group naming a resource the server cannot find |
400 Bad Request |
not-supported |
the parameter | A parameter the server does not support (see Declaring partial operation support) |
400 Bad Request |
not-supported |
_format |
A format the server does not support |
404 Not Found |
not-found |
subject | An unresolvable subjectCanonical or subjectReference |
404 Not Found |
not-found |
- | A dependency neither supplied as a context entry nor resolvable by the server |
406 Not Acceptable |
not-supported |
- | An envelope representation the server declines for the chosen format (see Content Negotiation) |
422 Unprocessable Entity |
invalid |
subject | A resolved artifact conforming to none of ViewDefinition, SQLQuery or SQLView |
422 Unprocessable Entity |
invalid |
subject | A conformant subject that cannot be processed, such as an invalid FHIRPath expression or an SQL syntax error |
422 Unprocessable Entity |
invalid |
- | A result column of an SQL type with no value[x] mapping, where _format=fhir (see type mapping) |
500 Internal Server Error |
exception |
- | Unexpected server error |
All error responses (4xx and 5xx) SHOULD include
an OperationOutcome resource providing details about the error. Where a
request carries both an unresolvable subject and an unresolvable filter value,
the subject failure is the more fundamental: the response is 404 Not Found and
the OperationOutcome reports both issues.