Ema Dash · SQL on FHIR, actually running

SQL on FHIR is the HL7 standard that turns FHIR resources into flat tables with no ETL: the view IS the query. Each tab is a real service and each panel a question that service asks of its data, answered by a ViewDefinition you can read, edit and run right here.

Who are my patients, what coverage do they arrive with, which diagnoses concentrate demand, and how much activity do I record?

Loading the FHIRPath engine...

Insurance coverage

The roster by payor: the first question any finance team asks, and the one that usually lives in a separate spreadsheet.

See the ViewDefinition, the SQL and the table

The ViewDefinition behind it

Edit it: the charts and the table recompute as you type.

The SQL it stands for

Illustrative: each engine (Pathling, Aidbox, dbt over a FHIR warehouse) materialises it in its own dialect. What does not change is that no ETL sits in between.

-- Un padrón con cobertura previsional por paciente
CREATE VIEW patient_coverage AS
SELECT
  coverage.beneficiary.getReferenceKey() AS patient_id,
  coverage.type.coding.display.first() AS coverage_type,
  coverage.payor.display.first() AS payor,
  coverage.status AS status
FROM coverage
WHERE status = 'active';

Result table0 rows

No resource produces rows for this view.

Diagnosis burden

ICD-10 per patient, with clinical status alongside. The same view feeds a chronic-disease registry or a report to the health authority.

See the ViewDefinition, the SQL and the table

The ViewDefinition behind it

Edit it: the charts and the table recompute as you type.

The SQL it stands for

Illustrative: each engine (Pathling, Aidbox, dbt over a FHIR warehouse) materialises it in its own dialect. What does not change is that no ETL sits in between.

-- Diagnósticos codificados en CIE-10 por paciente
CREATE VIEW condition_burden AS
SELECT
  condition.subject.getReferenceKey() AS patient_id,
  condition.clinicalStatus.coding.code.first() AS clinical_status,
  condition.recordedDate AS recorded_date,
  code.code AS icd10,
  code.display AS diagnosis
FROM condition
CROSS JOIN UNNEST(condition.code.coding.where(system = 'http://hl7.org/fhir/sid/icd-10')) AS code;

Result table0 rows

No resource produces rows for this view.

Care activity

Volume by encounter type and by month. The month comes from a substring over the encounter date — temporal aggregation needs no pipeline either.

See the ViewDefinition, the SQL and the table

The ViewDefinition behind it

Edit it: the charts and the table recompute as you type.

The SQL it stands for

Illustrative: each engine (Pathling, Aidbox, dbt over a FHIR warehouse) materialises it in its own dialect. What does not change is that no ETL sits in between.

-- Atenciones por tipo, modalidad y mes
CREATE VIEW encounter_activity AS
SELECT
  encounter.getResourceKey() AS encounter_id,
  encounter.subject.getReferenceKey() AS patient_id,
  encounter.class.display AS modality,
  encounter.type.coding.display.first() AS encounter_type,
  encounter.period.start.substring(0, 7) AS month,
  encounter.period.start AS started
FROM encounter
WHERE status = 'finished';

Result table0 rows

No resource produces rows for this view.

Without it, every question asked of clinical data goes through a pipeline someone builds, maintains and repairs. With it, the question is written once and runs over the data exactly as stored.

A browser implementation of SQL on FHIR v2: column, forEach, forEachOrNull, nested select, unionAll, where, constant, %resource, getResourceKey() and getReferenceKey(). Not a certified runner — collection and type-coercion corners are approximated.

SQL on FHIR v2 specificationEMA’s FHIR guides