SQL on FHIR
3.0.0-ballot - STU 3 Ballot International flag

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

OperationDefinition: SQL Run

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:

  • Interactive development and debugging of ViewDefinitions
  • Running ad-hoc analytics queries
  • Interactive query development and testing
  • Real-time data retrieval with bound parameters

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:

  1. Resolve the subject named by subjectCanonical, subjectReference or subjectResource
  2. Branch on what it resolves to:
    • A ViewDefinition is evaluated directly, against the supplied resource values where present and otherwise against server data
    • A SQLQuery or SQLView Library has each relatedArtifact 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 label
  3. Bind parameters values to the SQL placeholders the Library declares (SQL subjects only)
  4. Evaluate the view, or execute the SQL
  5. Return results in the requested format (a raw stream for flat formats, a 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

Parameters

UseNameScopeCardinalityTypeBindingDocumentation
INsubjectCanonical0..1canonical (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.

INsubjectReference0..1Reference (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.

INsubjectResource0..1CanonicalResource (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).

INparameters0..1Parameters

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.

INcontext0..*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).

INresource0..*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_format0..1codeOutput 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).

INheader0..1boolean

Include CSV headers (default true). Applies only when csv output is requested.

INpatient0..*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).

INgroup0..*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_since0..1instant

Include only resources whose state changed after this instant. See Common Operation Behavior (operations-common.html#since-filter).

INsource0..1string

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_limit0..1integer

Maximum number of rows to return.

OUTreturn1..1Binary

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).

Notes:

HTTP Methods

  • GET: for invocations in which every supplied input parameter is primitive.
  • POST: required when 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 Method Limitations
  1. Available parameters: only those that can be passed as query parameters are supported over GET:
    • subjectCanonical - canonical URL of the ViewDefinition, SQLQuery or SQLView, with any | percent-encoded as %7C
    • subjectReference - literal location of the subject on the server
    • _format - output format specification
    • header - include CSV headers (for CSV format)
    • patient - filter by patient reference, repeated to name several patients
    • group - filter by group membership
    • _since - filter by last updated time
    • _limit - limit the number of result rows
    • source - external data source
  2. When POST is required: use POST instead of GET when you need to:
    • name the subject inline via subjectResource
    • bind parameter values via parameters
    • supply a supporting artifact inline via context
    • supply FHIR resources to transform via resource

    Each 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.

Data Sources

The operation can process data from:

  1. Direct resources - Provided via the resource parameter in the request, where the subject is a ViewDefinition
  2. Server resources - From the server's data store (default)
  3. External source - Specified via the source parameter

Input Parameters

The 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 1 Canonical URL of the subject. Details
subjectReference Reference 1 Literal location of the subject on the server. Details
subjectResource ViewDefinition | SQLQuery | SQLView² 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.

Output Parameter
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
Naming the subject

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.

Supporting artifacts (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.

Resource parameter and Bundle inputs

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.

Filtering

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.

Row Limit

_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.

Format Parameter Clarification

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:

  • It is RECOMMENDED to support json, ndjson and csv by default; servers MAY support parquet and fhir, and SHALL document supported formats in the CapabilityStatement.
  • If _format is omitted (and no format is derivable from Accept), the server SHALL return the result in ndjson format.
  • When _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.
  • The response of any format MAY use Transfer-Encoding: chunked; chunked transfer is independent of the format. See Streaming and Transfer Encoding.

Examples

Running a ViewDefinition over GET

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.

Running a SQLQuery over GET

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.

Running a SQLQuery with bound parameters over POST
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.

Naming the subject by literal reference
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" }
      ]
    }}
  ]
}
Running an inline ViewDefinition

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"}
]
Running a ViewDefinition over inline resources

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
Supplying inline resources as a Bundle

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.

Fully ad-hoc: an inline query with an inline supporting artifact

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
Transitive resolution through a supplied SQLView

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"
    }}
  ]
}
An unmatched context entry is rejected

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"]
  }]
}
Naming two patients over GET

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.

Capping result rows with _limit

Use _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 }
  ]
}
Default format (_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"}
Response

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
FHIR Format Response

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"
}

SQL to FHIR type mapping

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 Passing

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.

Error Handling

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.