One question: which patients have a diabetes diagnosis?
Start with a simple task:
Return patients whose diagnosis code belongs to the Diabetes ValueSet.
We need two inputs: the patients' diagnosis codes and the codes included in the ValueSet. A table of conditions gives us the first, but not the second.
Here are three diagnosis rows and the two members of our example Diabetes ValueSet:
conditions:
- patient_id: p1
system: http://snomed.info/sct
version: "2026-02"
code: "73211009"
- patient_id: p2
system: http://snomed.info/sct
version: "2026-02"
code: "44054006"
- patient_id: p3
system: http://snomed.info/sct
version: "2026-02"
code: "22298006"
diabetes_codes:
- system: http://snomed.info/sct
version: "2026-02"
code: "73211009"
display: Diabetes mellitus
- system: http://snomed.info/sct
version: "2026-02"
code: "44054006"
display: Type 2 diabetes mellitus
The answer should be p1 and p2. The diagnosis code for p3 is not in this ValueSet. We will build both inputs and try two ways to ask the same question.
Examples use YAML for readability and illustrative terminology versions. This tiny ValueSet is not a complete clinical definition. Resource excerpts are not a runnable package, and the ValueSet interface is still a proposal.
I see two phases here. When we flatten FHIR resources, we can normalize terminology — for example, translate diagnosis codes using FHIRPath functions. Later, when we build a cohort, we need to use terminology inside a SQL query. Those are related tasks, but they need different interfaces.
For some measures, we already take an expanded ValueSet, treat it as a table, and write SQL against it. The question is how to make that a common interface rather than something each implementation invents.
John Grimes connected this to the problem terminology servers already solve:
“This is the problem that the terminology server solves for preparing terminology data for runtime query. We're effectively trying to do the same thing for analytic use cases.”
— John Grimes, September 15 (lightly edited)
First, turn Conditions into rows
A SQL on FHIR ViewDefinition describes how to extract rows and columns from FHIR resources using FHIRPath. It does not contain SQL.
Here is a simplified view over Condition:
resourceType: ViewDefinition
url: http://example.org/ViewDefinition/conditions
version: 1.0.0
name: conditions
status: draft
resource: Condition
select:
- column:
- name: patient_id
path: subject.getReferenceKey(Patient)
select:
- forEach: code.coding
column:
- name: system
path: system
- name: version
path: version
- name: code
path: code
The nested select combines the patient identifier from the Condition with each item in code.coding. For these examples, assume patient references are Patient/p1, Patient/p2 and Patient/p3, and the runner represents their keys as p1, p2 and p3. Actual key representations are runner-defined; they must be consistent with the corresponding Patient view. getReferenceKey(Patient) restricts the reference type to Patient.
We use versioned codings to make the matching explicit; real data often omits the version.
Now we have patient data we can query. We still need to know which codes count as diabetes.
Declare both inputs to the SQL view
A SQL view or query works on the rows produced by ViewDefinitions. SQL on FHIR represents SQL queries using a FHIR Library resource. Its relatedArtifact list declares dependencies: type: depends-on says an input is needed, resource identifies it, and label gives it a local name for SQL.
On September 1, I proposed applying the same pattern to ValueSets:
“We can refer to a ViewDefinition in the SQL view through relatedArtifact, give it a table name and use it in a join. We can use a similar approach for the ValueSet: my SQL view depends on a ValueSet, and I'm giving it a name.”
— Nikolai Ryzhikov, September 1 (lightly edited)
For our diabetes query, the proposed dependency declaration looks like this Library excerpt:
resourceType: Library
url: http://example.org/Library/patients-with-diabetes
status: draft
type:
coding:
- system: http://hl7.org/fhir/uv/sql-on-fhir/CodeSystem/LibraryTypesCodes
code: sql-query
relatedArtifact:
- type: depends-on
label: conditions
resource: http://example.org/ViewDefinition/conditions|1.0.0
- type: depends-on
label: diabetes_codes
resource: http://example.org/ValueSet/diabetes|2026
content:
- contentType: application/sql
url: https://example.org/queries/patients-with-diabetes.sql
The first dependency supplies the condition rows. The second supplies the ValueSet membership. The part after | requests a particular version of that artifact.
The SQL itself belongs in Library.content: an Attachment with contentType: application/sql. Here, url points to an illustrative SQL file; alternatively, data can contain the SQL encoded as base64. The two queries below are alternative contents of that file. We show them as readable YAML sql: | blocks, not as additional Library fields.
That is what I mean by first-class ValueSets: the query explicitly declares the terminology it needs, alongside its data views. The runner — the software executing the query — resolves the dependencies. SQL uses their local names instead of resolving URLs itself.
John described the same separation from the runner's side:
“You're just saying I have a dependency on this value set. […] For the SQL abstraction, we want to keep it super simple, especially for the simple cases.”
— John Grimes, September 15 (lightly edited)
Membership, not an expansion algorithm
The second dependency supplies the diabetes_codes membership shown at the start.
Owen Loveluck asked whether the abstraction should be a view rather than a table. That distinction matters: a relation does not have to be a physical table. It can be a view, cached data or something evaluated when needed. As I put it on September 8:
“We can treat the pre-expanded ValueSet as a relation. We don't care how we got there — is it a dynamic expansion query, or pre-expanded stuff loaded into the database? But I can join and build my measure.”
— Nikolai Ryzhikov, September 8 (lightly edited)
The proposal does not standardize how ValueSet.compose, ECL or VCL are evaluated. It describes how the resulting membership is made available to SQL.
Option A: join the ValueSet as a relation
The runner exposes diabetes_codes as a named relation. Our query joins against it:
# Query text for illustration; `sql` is not a FHIR Library field.
sql: |
SELECT DISTINCT c.patient_id
FROM conditions c
JOIN diabetes_codes vs
ON vs.system = c.system
AND vs.version = c.version
AND vs.code = c.code
expected_patient_ids: [p1, p2]
Option B: test membership with a function
The same dependency could instead be available through member_of:
# Alternative query text, using the proposed function.
sql: |
SELECT DISTINCT c.patient_id
FROM conditions c
WHERE member_of(
c.system, c.version, c.code, 'diabetes_codes'
)
expected_patient_ids: [p1, p2]
What changes between the two options?
Both queries return p1 and p2. What differs is how we write the query and what else the interface lets us do.
This is ordinary SQL. The membership is visible: we can inspect the codes, count them or include display in our output. If the cohort is wrong, we can look at both inputs.
Additional properties can be columns too. Gino Canessa pointed this out while discussing codes that are not selectable:
“If not-selectable is a property you care about, you make that a column during your extraction and then you have access to it in SQL directly. The functions don't allow that as nicely.”
— Gino Canessa, September 15 (lightly edited)
The cost is a repeated multi-column join. The draft also needs a clear uniqueness rule for (system, version, code): duplicate membership rows can multiply query results. DISTINCT protects this patient list, but not every aggregate someone might write later.
The function is concise, fits into boolean expressions and does not multiply input rows. Its implementation could use an efficient lookup rather than a join. Which performs better depends on the engine.
The trade-off is portability: member_of is a proposed SQL on FHIR function, not a standard SQL function. Engines need to support it or translate it. It answers a membership question but does not expose the members or return their display text.
If a runner supports both interfaces, they should agree on membership for the same inputs and terminology snapshot. Our two queries should return the same patients.
A runner also needs to declare whether it supports the relation, the function or both. Matching membership results does not by itself make a query portable.
Both options need clear version rules
There are two different versions in the example: diabetes|2026 identifies the ValueSet definition, while each membership row carries a CodeSystem version. Pinning the ValueSet alone does not necessarily pin every terminology dependency used to expand it.
I care about this because we once broke validation in Aidbox by using “latest.” The validator picked up changed encounter statuses from R5, and validation broke on production R4 servers. Since then we pin as much as possible and update the pins deliberately.
For our query, the risk is a changed patient list even though the SQL has not changed. If two runners resolve an unversioned dependency differently, they can return different answers. Owen raised exactly that concern in the meeting.
The draft proposes consistent membership for each job, recording resolved versions and rejecting ambiguous resolution. But how unversioned dependencies should work is still open. Our example deliberately avoids that ambiguity; an implementation needs to handle it explicitly, not silently guess.
Implement, experiment, then decide
The working group currently leans toward the relation as the baseline, with the function as an optional convenience. That is a direction, not a decision. Both options have useful properties, and we need to try them on real queries.
John Grimes offered a practical next step:
“I think we should create a branch and start to build stuff out in the spec and the reference implementation just to get a feel for it.”
— John Grimes, September 15 (filler words removed)
We will start with queries like this one and check three things: do the interfaces return the same patients, how do they handle versions, and how easy are they to implement and use? We also need to test additional properties and measure performance. Then we can decide what every runner should support.
The immediate goal is simple: a query should be able to say I depend on this ValueSet. The experiment should help us decide how runners expose it to SQL and what every implementation must support.
Join the Terminology in SQL on FHIR discussion on Zulip, and follow the SQL on FHIR working-group meetings. Bring a query you need to run, a ValueSet that is difficult to handle, or experience implementing either approach — those examples will help us test the options and decide.
Try SQL on FHIR and terminology for yourself
At Health Samurai, we build both sides of this: Aidbox for working with FHIR data and SQL on FHIR, and Termbox for FHIR terminology. These are technologies we develop and use ourselves — and bring back to the working group as practical experience, not just design ideas.
You can try both for free. Explore SQL on FHIR in Aidbox, and use Termbox to work with terminology and ValueSet expansions. Start with your own data and a ValueSet you actually use; that is a better test than any demo.
The first-class ValueSet interface discussed here is still a proposal. The trials let you explore the existing capabilities of the two products, not an already-shipped version of this proposal.
Based on the SQL on FHIR working-group discussions on September 1, 8 and 15, 2026, and my ValueSet abstraction draft. Meeting excerpts are lightly edited for readability.
See also: SQL on FHIR WG meetings.
![Terminology is fun: CodeableConcept.coding[]](/assets/images/articles/horizontal-3.avif?v=2nw7.muc)
