Parsing FHIR data guide

SQL on FHIR is a standard for defining flat, tabular views of FHIR data. Instead of writing custom parsing code, you declare what you want to extract—and a ViewRunner handles the rest.

#Why it matters

FHIR data is deeply nested. A single resource can contain:

Arrays within arrays
Polymorphic fields (e.g., value[x])
CodeableConcepts requiring system URL filtering
Optional fields at every level

Most use cases need this data flattened into rows and columns. Writing that transformation code manually is tedious, error-prone, and hard to reuse.

#What SQL on FHIR provides

ViewDefinitions — Declarative schemas that specify what to extract
FHIRPath expressions — A query language for navigating FHIR structures
Portable definitions — Same view works across different ViewRunner implementations

SQL on FHIR

A specification for transforming FHIR resources into flat, tabular structures using declarative view definitions

View Specification →

FHIRPath

A path-based navigation and extraction language designed for FHIR data structures

View Specification →

Medplum ViewRunner

A JavaScript/TypeScript implementation of SQL on FHIR for executing ViewDefinitions

View Documentation →

#Comparison

#Manual parsing

FHIR resources are deeply nested JSON (or XML) structures. You can parse them with any standard JSON parser and access fields using familiar patterns—dot notation, array indexing, optional chaining.

The challenge is the complexity of the schema itself. A single ExplanationOfBenefit resource can contain dozens of nested objects, arrays within arrays, polymorphic fields, and codeable concepts that require filtering by system URL. Consider extracting:

Procedure codes and dates
Service/product codes (CPT, HCPCS, NDC)
Provider references and names
Care team members and their roles
Item-level details (line items in a claim)
Financial totals (submitted, benefit, member liability, deductible, copay, coinsurance)
Pharmacy-specific fields (days supply, refill numbers, DAW codes)
Institutional-specific fields (type of bill, admission dates, DRG codes)

You'd need hundreds of lines of similar parsing logic, with null checks at every level, all requiring maintenance as your needs evolve or as you encounter edge cases across different payers' implementations.

Manual parsing

function parseEOB(eob: ExplanationOfBenefit) {
  // Extract diagnosis codes
  const diagnosisCodes: string[] = [];
  if (eob.diagnosis) {
    for (const dx of eob.diagnosis) {
      if (dx.diagnosisCodeableConcept?.coding) {
        for (const coding of dx.diagnosisCodeableConcept.coding) {
          if (coding.system === 'http://hl7.org/fhir/sid/icd-10-cm' && coding.code) {
            diagnosisCodes.push(coding.code);
          }
        }
      }
    }
  }

  // Extract primary provider
  let providerName: string | null = null;
  if (eob.careTeam) {
    const primary = eob.careTeam.find((ct) =>
      ct.role?.coding?.some((c) => c.code === 'primary')
    );
    providerName = primary?.provider?.display ?? null;
  }

  // Extract benefit paid amount
  let benefitPaid: number | null = null;
  if (eob.total) {
    const benefit = eob.total.find((t) =>
      t.category?.coding?.some(
        (c) =>
          c.system === 'http://terminology.hl7.org/CodeSystem/adjudication' &&
          c.code === 'benefit'
      )
    );
    benefitPaid = benefit?.amount?.value ?? null;
  }

  return { diagnosisCodes, providerName, benefitPaid };
}

#SQL on FHIR approach

With SQL on FHIR, you write a ViewDefinition that declares what to extract using FHIRPath expressions. A ViewRunner executes the definition against FHIR resources and returns flat, predictable output.

Each column specifies a name and a path. The path is a FHIRPath expression—a query language designed for FHIR that handles null safety, array traversal, and filtering automatically.

You still need to know what data you're looking for. SQL on FHIR doesn't eliminate the need to understand FHIR or know which clinical concepts you need—it just makes extracting them much easier. The good news? We can help you determine which FHIR paths you need, and AI tools are excellent at writing FHIRPath expressions once you explain what you want.

Equivalent SQL on FHIR

{
  name: 'diagnosis_codes',
  path: "diagnosis.diagnosisCodeableConcept.coding.where(system='http://hl7.org/fhir/sid/icd-10-cm').code",
  collection: true
},
{
  name: 'provider_name',
  path: "careTeam.where(role.coding.code='primary').provider.display.first()"
},
{
  name: 'benefit_paid',
  path: "total.where(category.coding.code='benefit').amount.value.first()"
}

#How it works

SQL on FHIR has three components: ViewDefinitions (what to extract), ViewRunners (the execution engine), and FHIRPath (the navigation language).

ViewDefinition

Declarative schema for what to extract

Purpose:

Define column names, FHIRPath expressions, and output structure

Portable:

Same definition works across any ViewRunner implementation

ViewRunner

Execution engine that applies definitions

Purpose:

Execute FHIRPath expressions, handle nulls, iterate arrays

Options:

Medplum, Aidbox, Pathling, and more

FHIRPath

Navigation language for FHIR structures

Purpose:

Navigate nested data, filter arrays, extract values

Features:

Null-safe, type-aware, with built-in functions

#ViewDefinition

A ViewDefinition is a FHIR Logical Model that specifies how to flatten FHIR resources into tabular structures (JSON, database tables, etc). It's a declarative specification in that you describe what you want, not how to get it. Each ViewDefinition includes:

Table name: How you want to reference this view
Status: Whether the definition is active, draft, or retired
Target resource type: Which FHIR resource this operates on (Patient, ExplanationOfBenefit, etc.)
Column definitions: What fields to extract and what to call them
Optional constants: Fixed values you want in every row
Filtering conditions: Rules for which data to include

Notice how the ViewDefinition handles complexity for you:

The forEach directive iterates through arrays automatically
The forEachOrNull handles optional fields gracefully
FHIRPath expressions like name.where(use='official').first() filter and extract data
You get consistent column names regardless of source structure

{
  "resource": "Patient",
  "status": "active",
  "name": "patient_table",
  "select": [
    {
      "column": [
        {
          "name": "patient_id",
          "path": "getResourceKey()"
        },
      ],
    },
    {
      "forEach": "name.where(use='official').first()",
      "column": [
        {
          "name": "first_name",
          "path": "given.join(' ')"
        },
        {
          "name": "last_name",
          "path": "family"
        },
      ],
    },
    {
      "forEachOrNull": "telecom.where(system='phone' and use='home').first()",
      "column": [
        {
          "name": "home_phone",
          "path": "value"
        },
      ],
    },
  ]
}

patient_id	first_name	last_name	home_phone
e16a32f6...	Alverta	Dach	555-867-5309

#ViewRunner

A ViewRunner is the execution engine that takes your ViewDefinition and applies it to FHIR resources. Think of it as an interpreter: it reads your declarative specification and performs all the actual data extraction, transformation, and flattening work.

What ViewRunners do:

Parse ViewDefinitions and validate their structure
Execute FHIRPath expressions against FHIR resources
Handle null safety and missing fields automatically
Manage array iteration with forEach and forEachOrNull
Apply filters and transformations
Produce consistent output regardless of input variance

For Flexpa integrations, we recommend Medplum's ViewRunner. It's lightweight, works in any JavaScript/TypeScript environment (Node.js, browser, edge functions), and is easy to integrate.

#FHIRPath

FHIRPath is the expression language that powers SQL on FHIR. It's specifically designed for navigating FHIR data structures—think of it as "JSONPath for FHIR" with healthcare-specific features built in.

If you're familiar with JSONPath or XPath, FHIRPath will feel familiar. But unlike writing imperative JavaScript with null checks, FHIRPath expressions are declarative and handle edge cases automatically.

Key FHIRPath features:

Null safety: If a field doesn't exist, expressions return an empty collection instead of throwing errors
Array flattening: Navigating through arrays automatically flattens results
Type awareness: FHIRPath understands FHIR data types (CodeableConcept, Reference, Period, etc.)
Built-in functions: Filtering (where), joining (join), deduplication (distinct), and more

AI can help write FHIRPath expressions! Large language models are excellent at translating plain English descriptions like "get all diagnosis codes from ICD-10-CM" into the corresponding FHIRPath expressions.

// Get all given names from a Patient
Patient.name.given
// Returns: ['John', 'Michael']

// Get distinct diagnosis codes
diagnosis.diagnosisCodeableConcept.coding.code.distinct()
// Returns: ['E11.9', 'I10', 'Z79.4']

// Filter care team by role
careTeam.where(role.coding.code='primary').provider.reference
// Returns: 'Practitioner/123456'

// Join multiple values with commas
careTeam.provider.display.join(',')
// Returns: 'Dr. Smith, Nurse Johnson, Dr. Chen'

// Get medication NDC codes from a specific coding system
item.productOrService.coding
  .where(system='http://hl7.org/fhir/sid/ndc')
  .code
// Returns: ['00591-0405-01', '00781-1506-10']

#Examples

#Patient demographics

Install the Medplum packages which provide TypeScript types and a ViewRunner implementation.

npm install @medplum/core @medplum/fhirtypes @flexpa/node-sdk

Now we'll create a ViewDefinition that extracts basic patient information. Every ViewDefinition has three key parts:

name — table identifier (lowercase with underscores)
resource — FHIR resource type (Patient, ExplanationOfBenefit, etc.)
select — array of column definitions with name, path, and optional description

The example on the right extracts five fields from a Patient resource: the patient ID, family name, given names (joined into a single string), birth date, and medical record number (MRN). The FHIRPath expressions handle the nested structure—name.family navigates to the family name, while identifier.where(type.coding.code='MR').value filters to find the MRN identifier.

Save the example on the right to example.ts. Next, run the example with:

FLEXPA_ACCESS_TOKEN=... node example.ts

How does this work?

We use the Flexpa SDK to fetch the Patient resource, then pass it through evalSqlOnFhir with our ViewDefinition. The result is a simple JavaScript object with consistent field names—no null checks, no array iteration, no special handling for missing fields.

Patient demographics example

import { FlexpaClient } from '@flexpa/node-sdk'
import { evalSqlOnFhir } from '@medplum/core'
import { ViewDefinition } from '@medplum/fhirtypes'

// Define what fields to extract
const patientBasicInfo: ViewDefinition = {
  resourceType: 'ViewDefinition',
  name: 'patient_basic_info',
  status: 'active',
  resource: 'Patient',
  select: [
    {
      column: [
        { name: 'id', path: 'id' },
        { name: 'family_name', path: 'name.family' },
        { name: 'given_names', path: "name.given.join(' ')" },
        { name: 'birth_date', path: 'birthDate' },
        { name: 'mrn', path: "identifier.where(type.coding.code='MR').value" },
      ],
    },
  ],
}

// Fetch and transform
const flexpa = FlexpaClient.fromBearerToken(process.env.FLEXPA_ACCESS_TOKEN!)
const patient = await flexpa.read('Patient', '$PATIENT_ID')
const result = evalSqlOnFhir(patientBasicInfo, [patient])

Result

{
  "id": "a85f4c92-...",
  "family_name": "Smith",
  "given_names": "John Michael",
  "birth_date": "1985-03-15",
  "mrn": "MRN-789456"
}

#Claims extraction

EOBs are notoriously complex—deeply nested structures for diagnoses, procedures, medications, and providers. This view definition extracts a lightweight overview for search, filtering, and display.

#Breakdown

This view definition extracts both clinical and financial information from EOBs:

Clinical data: Diagnoses, procedures, and medications are extracted using collection: true to return arrays, with .distinct() to avoid duplicates
Provider information: Both primary provider and care team members, with .join(',') combining multiple names into a single string
Financial totals: Submitted amount, benefit paid, and patient liability extracted using complex where() filtering on adjudication categories
Smart filtering: Medication codes are filtered with .contains('ndc') to handle different NDC system URLs across payers

Notice that collection: true returns arrays in the output, while .join() in the path expression combines values into delimited strings. This gives you flexibility in how you structure the output.

Claims overview

import { ViewDefinition } from '@medplum/fhirtypes'

export const eobClaimsOverview: ViewDefinition = {
  resourceType: 'ViewDefinition',
  resource: 'ExplanationOfBenefit',
  name: 'eob-claims-overview',
  status: 'active',
  select: [
    {
      column: [
        {
          name: 'id',
          path: 'getResourceKey()',
        },
        {
          name: 'type',
          path: "type.coding.where(system='http://terminology.hl7.org/CodeSystem/claim-type').code.first()",
        },
        {
          name: 'status',
          path: 'status',
        },
        {
          name: 'billablePeriod_start',
          path: 'billablePeriod.start',
        },
        {
          name: 'diagnosis_codes',
          path: 'diagnosis.diagnosisCodeableConcept.coding.code.distinct()',
          collection: true,
        },
        {
          name: 'procedure_codes',
          path: 'procedure.procedureCodeableConcept.coding.code.distinct()',
          collection: true,
        },
        {
          name: 'medication_codes',
          path: "item.where(productOrService.coding.system.contains('ndc')).productOrService.coding.code.distinct()",
          collection: true,
        },
        {
          name: 'provider_display',
          path: 'provider.display',
        },
        {
          name: 'total_submitted',
          path: "total.where(category.coding.code='submitted').amount.value.first()",
        },
        {
          name: 'total_benefit',
          path: "total.where(category.coding.code='benefit').amount.value.first()",
        },
      ],
    },
  ],
}

#Reference ViewDefinitions

These production-ready ViewDefinitions are designed to flatten FHIR data into tabular formats suitable for analytics, reporting, and data exports. They align with the data categories in our Data Overview.

#Patient

Extracts core patient demographics: name, identifiers, contact information, gender, birth date, ethnicity, and emergency contacts. Maps to the Patient Identification and Contact section of our data overview.

Key fields extracted:

Given and family name
Identifiers (member ID, MRN)
Phone and email (telecom)
Gender, birth date, deceased status
Ethnicity (US Core extension)
Address
Communication preferences
Emergency contacts

flat-file-patient.json

{
  "resourceType": "ViewDefinition",
  "name": "flat-file-patient",
  "title": "Patient",
  "status": "active",
  "resource": "Patient",
  "select": [
    {
      "column": [
        { "name": "Given_name", "path": "name.first().given" },
        { "name": "Family_name", "path": "name.first().family" },
        { "name": "identifier_system", "path": "identifier.system", "collection": true },
        { "name": "identifier_code", "path": "identifier.value", "collection": true },
        { "name": "telcom_system", "path": "telecom.system", "collection": true },
        { "name": "telcom_value", "path": "telecom.value", "collection": true },
        { "name": "gender", "path": "gender" },
        {
          "name": "ethnicity",
          "path": "extension.where(url=\"http://hl7.org/fhir/us/core/StructureDefinition/us-core-ethnicity\").extension.where(url=\"text\").valueString"
        },
        { "name": "birthDate", "path": "birthDate" },
        { "name": "deceased?", "path": "deceasedBoolean" },
        { "name": "address", "path": "address.first().line" },
        { "name": "communication_language", "path": "communication.language.coding.code", "collection": true },
        { "name": "contact_relationship", "path": "contact.relationship.coding.code", "collection": true },
        { "name": "contact_name", "path": "contact.name.text", "collection": true },
        { "name": "contact_telcom_system", "path": "contact.telecom.system", "collection": true },
        { "name": "contact_telcom_value", "path": "contact.telecom.value", "collection": true }
      ]
    }
  ]
}

#Institutional claims

Extracts hospital and facility-based claims (inpatient, outpatient, SNF). Maps to Institutional Claims in our data overview. Includes a where clause to filter only institutional claim types.

Key fields extracted:

Claim identifiers and status
Billable period (admission dates)
Revenue codes and descriptions
Type of bill, admission type, point of origin
Discharge status
Diagnoses with type (principal, admitting, etc.)
Procedures with dates
Financial totals (submitted, benefit, deductible, member liability)
Care team and provider references
Payment information

flat-file-eob-institutional.json

{
  "resourceType": "ViewDefinition",
  "name": "flat-file-eob-institutional",
  "title": "Institutional Claims",
  "status": "active",
  "resource": "ExplanationOfBenefit",
  "select": [
    {
      "column": [
        { "name": "resource_id", "path": "id" },
        { "name": "endpointId", "path": "meta.tag.where(system=\"https://fhir.flexpa.com/identifiers/EndpointId\").first().code" },
        { "name": "unique_claim_id", "path": "identifier.where(type.coding.code=\"uc\").first().value" },
        { "name": "patient", "path": "patient.reference" },
        { "name": "type", "path": "type.coding.first().code" },
        { "name": "subType_code", "path": "subType.first().coding.first().code" },
        { "name": "status", "path": "status" },
        { "name": "disposition", "path": "disposition" },
        { "name": "item_productOrService_system", "path": "item.productOrService.coding.first().system", "collection": true },
        { "name": "item_productOrService_code", "path": "item.productOrService.coding.first().code", "collection": true },
        { "name": "item_productOrService_display", "path": "item.productOrService.coding.first().display", "collection": true },
        { "name": "total_submitted", "path": "total.where(category.coding.code=\"submitted\").first().amount.value" },
        { "name": "total_benefit", "path": "total.where(category.coding.code=\"benefit\").first().amount.value" },
        { "name": "total_discount", "path": "adjudication.where(category.coding.code=\"discount\").first().amount.value" },
        { "name": "total_memberliability", "path": "total.where(category.coding.code=\"memberliability\").first().amount.value" },
        { "name": "total_deductible", "path": "total.where(category.coding.code=\"deductible\").first().amount.value" },
        { "name": "billablePeriod_start", "path": "billablePeriod.start" },
        { "name": "billablePeriod_end", "path": "billablePeriod.end" },
        { "name": "item_revenue_code", "path": "item.revenue.coding.first().code", "collection": true },
        { "name": "item_revenue_display", "path": "item.revenue.coding.first().display", "collection": true },
        { "name": "provider", "path": "provider.reference" },
        { "name": "careTeam_role_code", "path": "careTeam.role.coding.first().code", "collection": true },
        { "name": "careTeam_provider_reference", "path": "careTeam.provider.reference", "collection": true },
        { "name": "admissionperiod_timingPeriod", "path": "supportingInfo.where(category.coding.code=\"admissionperiod\").first().timingPeriod" },
        { "name": "clmrecvddate_timeDate", "path": "supportingInfo.where(category.coding.code=\"clmrecvddate\").first().timingDate" },
        { "name": "typeofbill_system", "path": "supportingInfo.where(category.coding.code=\"typeofbill\").first().code.coding.first().system" },
        { "name": "typeofbill_code", "path": "supportingInfo.where(category.coding.code=\"typeofbill\").first().code.coding.first().code" },
        { "name": "typeofbill_expansion", "path": "supportingInfo.where(category.coding.code=\"typeofbill\").first().code.coding.first().display" },
        { "name": "pointoforigin_system", "path": "supportingInfo.where(category.coding.code=\"pointoforigin\").first().code.coding.first().system" },
        { "name": "pointoforigin_code", "path": "supportingInfo.where(category.coding.code=\"pointoforigin\").first().code.coding.first().code" },
        { "name": "pointoforigin_display", "path": "supportingInfo.where(category.coding.code=\"pointoforigin\").first().code.coding.first().display" },
        { "name": "admtype_system", "path": "supportingInfo.where(category.coding.code=\"admtype\").first().code.coding.first().system" },
        { "name": "admtype_code", "path": "supportingInfo.where(category.coding.code=\"admtype\").first().code.coding.first().code" },
        { "name": "admtype_display", "path": "supportingInfo.where(category.coding.code=\"admtype\").first().code.coding.first().display" },
        { "name": "discharge_status_system", "path": "supportingInfo.where(category.coding.code=\"discharge-status\").first().code.coding.first().system" },
        { "name": "discharge_status_code", "path": "supportingInfo.where(category.coding.code=\"discharge-status\").first().code.coding.first().code" },
        { "name": "discharge_status_display", "path": "supportingInfo.where(category.coding.code=\"discharge-status\").first().code.coding.first().display" },
        { "name": "diagnosis_type_code", "path": "diagnosis.type.coding.first().code", "collection": true },
        { "name": "diagnosis_system", "path": "diagnosis.diagnosisCodeableConcept.coding.first().system", "collection": true },
        { "name": "diagnosis_code", "path": "diagnosis.diagnosisCodeableConcept.coding.first().code", "collection": true },
        { "name": "diagnosis_display", "path": "diagnosis.diagnosisCodeableConcept.coding.first().display", "collection": true },
        { "name": "procedure_date", "path": "procedure.date", "collection": true },
        { "name": "procedure_type_code", "path": "procedure.type.coding.first().code", "collection": true },
        { "name": "procedure_system", "path": "procedure.procedureCodeableConcept.coding.first().system", "collection": true },
        { "name": "procedure_code", "path": "procedure.procedureCodeableConcept.coding.first().code", "collection": true },
        { "name": "procedure_display", "path": "procedure.procedureCodeableConcept.coding.first().display", "collection": true },
        { "name": "payment_type_code", "path": "payment.type.coding.first().code" },
        { "name": "payment_date", "path": "payment.date" }
      ]
    }
  ],
  "where": [
    { "path": "type.coding.where(system='http://terminology.hl7.org/CodeSystem/claim-type').code = 'institutional'" }
  ]
}

#Professional claims

Extracts office visits, outpatient services, and other professional claims. Maps to Professional Claims in our data overview. Includes a where clause to filter only professional claim types.

Key fields extracted:

Claim identifiers and status
Service/procedure codes (CPT, HCPCS)
Modifier codes
Place of service (location)
Diagnoses
Financial totals
Provider and care team
Payment information

flat-file-eob-professional.json

{
  "resourceType": "ViewDefinition",
  "name": "flat-file-eob-professional",
  "title": "Professional Claims",
  "status": "active",
  "resource": "ExplanationOfBenefit",
  "select": [
    {
      "column": [
        { "name": "resource_id", "path": "id" },
        { "name": "endpointId", "path": "meta.tag.where(system=\"https://fhir.flexpa.com/identifiers/EndpointId\").first().code" },
        { "name": "unique_claim_id", "path": "identifier.where(type.coding.code=\"uc\").first().value" },
        { "name": "patient", "path": "patient.reference" },
        { "name": "type", "path": "type.coding.first().code" },
        { "name": "status", "path": "status" },
        { "name": "disposition", "path": "disposition" },
        { "name": "item_productOrService_system", "path": "item.productOrService.coding.first().system", "collection": true },
        { "name": "item_productOrService_code", "path": "item.productOrService.coding.first().code", "collection": true },
        { "name": "item_productOrService_display", "path": "item.productOrService.coding.first().display", "collection": true },
        { "name": "item_modifier_system", "path": "item.modifier.coding.first().system", "collection": true },
        { "name": "item_modifier_code", "path": "item.modifier.coding.first().code", "collection": true },
        { "name": "item_modifier_display", "path": "item.modifier.coding.first().display", "collection": true },
        { "name": "location_code", "path": "item.locationCodeableConcept.coding.first().code", "collection": true },
        { "name": "location_display_(place_of_service)", "path": "item.locationCodeableConcept.coding.first().display", "collection": true },
        { "name": "total_submitted", "path": "total.where(category.coding.code=\"submitted\").first().amount.value" },
        { "name": "total_benefit", "path": "total.where(category.coding.code=\"benefit\").first().amount.value" },
        { "name": "total_discount", "path": "adjudication.where(category.coding.code=\"discount\").first().amount.value" },
        { "name": "total_memberliability", "path": "total.where(category.coding.code=\"memberliability\").first().amount.value" },
        { "name": "total_deductible", "path": "total.where(category.coding.code=\"deductible\").first().amount.value" },
        { "name": "billablePeriod_start", "path": "billablePeriod.start" },
        { "name": "billablePeriod_end", "path": "billablePeriod.end" },
        { "name": "provider", "path": "provider.reference" },
        { "name": "careTeam_role_code", "path": "careTeam.role.coding.first().code", "collection": true },
        { "name": "careTeam_provider_reference", "path": "careTeam.provider.reference", "collection": true },
        { "name": "clmrecvddate_date", "path": "supportingInfo.where(category.coding.code=\"clmrecvddate\").first().timingDate" },
        { "name": "diagnosis_type_code", "path": "diagnosis.type.coding.first().code", "collection": true },
        { "name": "diagnosisCodeableConcept_system", "path": "diagnosis.diagnosisCodeableConcept.coding.first().system", "collection": true },
        { "name": "diagnosisCodeableConcept_code", "path": "diagnosis.diagnosisCodeableConcept.coding.first().code", "collection": true },
        { "name": "diagnosisCodeableConcept_display", "path": "diagnosis.diagnosisCodeableConcept.coding.first().display", "collection": true },
        { "name": "payment_type_code", "path": "payment.type.coding.first().code" },
        { "name": "payment_date", "path": "payment.date" }
      ]
    }
  ],
  "where": [
    { "path": "type.coding.where(system='http://terminology.hl7.org/CodeSystem/claim-type').code = 'professional'" }
  ]
}

#Pharmacy claims

Extracts prescription drug claims with pharmacy-specific fields. Maps to Pharmacy / Rx Information in our data overview. Includes a where clause to filter only pharmacy claim types.

Key fields extracted:

Drug codes (NDC)
Days supply
Refill information (number, authorized)
DAW (dispense as written) code
Brand/generic indicator
Prescription origin code
Compound code
Financial totals
Provider and care team
Payment information

flat-file-eob-pharmacy.json

{
  "resourceType": "ViewDefinition",
  "name": "flat-file-eob-pharmacy",
  "title": "Pharmacy Claims",
  "status": "active",
  "resource": "ExplanationOfBenefit",
  "select": [
    {
      "column": [
        { "name": "resource_id", "path": "id" },
        { "name": "endpointId", "path": "meta.tag.where(system=\"https://fhir.flexpa.com/identifiers/EndpointId\").first().code" },
        { "name": "unique_claim_id", "path": "identifier.where(type.coding.code=\"uc\").first().value" },
        { "name": "patient", "path": "patient.reference" },
        { "name": "type", "path": "type.coding.first().code" },
        { "name": "status", "path": "status" },
        { "name": "disposition", "path": "disposition" },
        { "name": "outcome", "path": "outcome" },
        { "name": "total_submitted", "path": "total.where(category.coding.code=\"submitted\").first().amount.value" },
        { "name": "total_benefit", "path": "total.where(category.coding.code=\"benefit\").first().amount.value" },
        { "name": "total_discount", "path": "adjudication.where(category.coding.code=\"discount\").first().amount.value" },
        { "name": "total_memberliability", "path": "total.where(category.coding.code=\"memberliability\").first().amount.value" },
        { "name": "total_deductible", "path": "total.where(category.coding.code=\"deductible\").first().amount.value" },
        { "name": "billablePeriod_start", "path": "billablePeriod.start" },
        { "name": "billablePeriod_end", "path": "billablePeriod.end" },
        { "name": "clmrecvddate_date", "path": "supportingInfo.where(category.coding.code=\"clmrecvddate\").first().timingDate" },
        { "name": "item_productOrService_system", "path": "item.productOrService.coding.first().system", "collection": true },
        { "name": "item_productOrService_code", "path": "item.productOrService.coding.first().code", "collection": true },
        { "name": "item_productOrService_display", "path": "item.productOrService.coding.first().display", "collection": true },
        { "name": "provider", "path": "provider.reference" },
        { "name": "careTeam_role_code", "path": "careTeam.role.coding.first().code", "collection": true },
        { "name": "careTeam_provider_reference", "path": "careTeam.provider.reference", "collection": true },
        { "name": "dayssupply_value", "path": "supportingInfo.where(category.coding.code=\"dayssupply\").first().valueQuantity.value" },
        { "name": "dawcode_code", "path": "supportingInfo.where(category.coding.code=\"dawcode\").first().code.coding.first().code" },
        { "name": "dawcode_display", "path": "supportingInfo.where(category.coding.code=\"dawcode\").first().code.coding.first().display" },
        { "name": "refillnum", "path": "supportingInfo.where(category.coding.code=\"refillnum\").first().valueQuantity.value" },
        { "name": "refillsauthorized", "path": "supportingInfo.where(category.coding.code=\"refillsauthorized\").first().valueQuantity.value" },
        { "name": "brandgenericindicator_code", "path": "supportingInfo.where(category.coding.code=\"brandgenericindicator\").first().code.coding.first().code" },
        { "name": "brandgenericindicator_display", "path": "supportingInfo.where(category.coding.code=\"brandgenericindicator\").first().code.coding.first().display" },
        { "name": "rxorigincode_code", "path": "supportingInfo.where(category.coding.code=\"rxorigincode\").first().code.coding.first().code" },
        { "name": "rxorigincode_display", "path": "supportingInfo.where(category.coding.code=\"rxorigincode\").first().code.coding.first().display" },
        { "name": "compoundcode_code", "path": "supportingInfo.where(category.coding.code=\"compoundcode\").first().code.coding.first().code" },
        { "name": "compoundcode_display", "path": "supportingInfo.where(category.coding.code=\"compoundcode\").first().code.coding.first().display" },
        { "name": "payment_type_code", "path": "payment.type.coding.first().code" },
        { "name": "payment_date", "path": "payment.date" }
      ]
    }
  ],
  "where": [
    { "path": "type.coding.where(system='http://terminology.hl7.org/CodeSystem/claim-type').code = 'pharmacy'" }
  ]
}

#Claim line items

Creates one row per line item within an EOB, enabling detailed analysis of individual services within a claim. Uses forEach to iterate over items and extract item-level adjudication amounts.

Key fields extracted:

Parent claim identifiers (resource_id, endpointId, type, subType)
Item sequence number
Product/service codes and descriptions
Item-level adjudication amounts:
- Submitted, copay, eligible, deductible
- Benefit, coinsurance, noncovered
- Prior payer paid, paid by patient, paid to provider
- Member liability, discount, drug cost

flat-file-claim-line-items.json

{
  "resourceType": "ViewDefinition",
  "name": "flat-file-claim-line-items",
  "title": "Claim Line Items",
  "status": "active",
  "resource": "ExplanationOfBenefit",
  "select": [
    {
      "column": [
        { "name": "resource_id", "path": "id" },
        { "name": "endpointId", "path": "meta.tag.where(system=\"https://fhir.flexpa.com/identifiers/EndpointId\").first().code" },
        { "name": "type", "path": "type.coding.first().code" },
        { "name": "subType", "path": "subType.first().coding.first().code" }
      ]
    },
    {
      "forEach": "item",
      "column": [
        { "name": "item_sequence", "path": "sequence" },
        { "name": "item_productOrService_system", "path": "productOrService.coding.first().system" },
        { "name": "item_productOrService_code", "path": "productOrService.coding.first().code" },
        { "name": "item_productOrService_display", "path": "productOrService.coding.first().display" },
        { "name": "item_submitted", "path": "adjudication.where(category.coding.code=\"submitted\").first().amount.value" },
        { "name": "item_copay", "path": "adjudication.where(category.coding.code=\"copay\").first().amount.value" },
        { "name": "item_eligible", "path": "adjudication.where(category.coding.code=\"eligible\").first().amount.value" },
        { "name": "item_deductible", "path": "adjudication.where(category.coding.code=\"deductible\").first().amount.value" },
        { "name": "item_benefit", "path": "adjudication.where(category.coding.code=\"benefit\").first().amount.value" },
        { "name": "item_coinsurance", "path": "adjudication.where(category.coding.code=\"coinsurance\").first().amount.value" },
        { "name": "item_noncovered", "path": "adjudication.where(category.coding.code=\"noncovered\").first().amount.value" },
        { "name": "item_priorpayerpaid", "path": "adjudication.where(category.coding.code=\"priorpayerpaid\").first().amount.value" },
        { "name": "item_paidbypatient", "path": "adjudication.where(category.coding.code=\"paidbypatient\").first().amount.value" },
        { "name": "item_paidtoprovider", "path": "adjudication.where(category.coding.code=\"paidtoprovider\").first().amount.value" },
        { "name": "item_memberliability", "path": "adjudication.where(category.coding.code=\"memberliability\").first().amount.value" },
        { "name": "item_discount", "path": "adjudication.where(category.coding.code=\"discount\").first().amount.value" },
        { "name": "item_drugcost", "path": "adjudication.where(category.coding.code=\"drugcost\").first().amount.value" }
      ]
    }
  ]
}

#Advanced patterns

#forEach

Sometimes you want to "unroll" an array—creating one output row per array element. For example, you might want one row per line item in a claim, rather than one row per claim.

The forEach directive tells the view runner to iterate over the specified array. Within the column paths, you can use %resource to reference the parent resource rather than the current array element.

forEach

{
  forEach: 'item',
  column: [
    { name: 'resource_id', path: '%resource.id' },
    { name: 'line_number', path: 'sequence' },
    { name: 'amount', path: 'net.value' },
  ],
}

#forEachOrNull

Sometimes an array might not exist at all. If you use forEach on a non-existent field, you won't get any output rows. If you want to get a row even when the array is empty, use forEachOrNull.

forEachOrNull

{
  forEachOrNull: 'procedure',
  column: [
    {
      name: 'procedure_code',
      path: 'procedureCodeableConcept.coding.code.first()',
    },
  ],
}

#References

FHIR uses references to link resources. SQL on FHIR provides special functions for working with references:

getReferenceKey() extracts just the ID from a reference like "Practitioner/123"
getResourceKey() gets a key suitable for database foreign key relationships

References

// Extracts just the ID from "Practitioner/123"
{
  name: 'provider_id',
  path: 'getReferenceKey(provider.reference)',
}

// Gets a key suitable for database foreign key relationships
{
  name: 'patient_key',
  path: 'getResourceKey(patient)',
}

#Filtering

You can use FHIRPath's where() function extensively to filter and extract specific values. A particularly common use case is filtering by coding system—FHIR codes typically come from multiple coding systems (ICD-10, CPT, NDC, etc.), and you'll often want to extract codes from a specific system.

Common coding systems you'll filter for:

ICD-10-CM: http://hl7.org/fhir/sid/icd-10-cm (diagnoses)
ICD-10-PCS: http://www.cms.gov/Medicare/Coding/ICD10 (procedures)
CPT: http://www.ama-assn.org/go/cpt (professional services)
HCPCS: http://purl.bioontology.org/ontology/HCPCS (healthcare procedures)
NDC: http://hl7.org/fhir/sid/ndc (medications)
LOINC: http://loinc.org (lab results)
SNOMED CT: http://snomed.info/sct (clinical terms)

Filtering by coding system

// Get ICD-10-CM diagnosis codes specifically
'diagnosis.diagnosisCodeableConcept.coding.where(system=\'http://hl7.org/fhir/sid/icd-10-cm\').code'

// Get CPT procedure codes
'item.productOrService.coding.where(system=\'http://www.ama-assn.org/go/cpt\').code.distinct()'

// Get NDC medication codes
'item.productOrService.coding.where(system=\'http://hl7.org/fhir/sid/ndc\').code'

// Handle system URL variations with contains()
"item.where(productOrService.coding.system.contains('ndc')).productOrService.coding.code"

Other filtering patterns

// Get only primary care providers
'careTeam.where(role.coding.code=\'primary\').provider.display'

// Get only line items above a certain amount
'item.where(net.value > 100).productOrService.coding.display'

// Combine filters: ICD-10 diagnoses marked as principal
'diagnosis.where(type.coding.code=\'principal\').diagnosisCodeableConcept.coding.where(system=\'http://hl7.org/fhir/sid/icd-10-cm\').code'

#CLI example

A simple CLI tool that takes a FHIR Bundle and a ViewDefinition as input, then streams the flattened output as JSON. Useful for quick data extraction and piping to other tools.

Save this as flatten.ts and run with:

npx tsx flatten.ts bundle.json view-definition.json

Or pipe directly to other tools:

npx tsx flatten.ts bundle.json view.json | jq '.[] | .diagnosis_codes'

flatten.ts

import { readFileSync } from 'fs';
import { evalSqlOnFhir } from '@medplum/core';
import { Bundle, Resource, ViewDefinition } from '@medplum/fhirtypes';

const [bundlePath, viewPath] = process.argv.slice(2);

if (!bundlePath || !viewPath) {
  console.error('Usage: npx tsx flatten.ts <bundle.json> <view-definition.json>');
  process.exit(1);
}

const bundle: Bundle = JSON.parse(readFileSync(bundlePath, 'utf-8'));
const viewDefinition: ViewDefinition = JSON.parse(readFileSync(viewPath, 'utf-8'));

const resources: Resource[] = bundle.entry?.map((e) => e.resource).filter(Boolean) as Resource[];
const result = evalSqlOnFhir(viewDefinition, resources);

console.log(JSON.stringify(result, null, 2));