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.

#Looking for more examples?

Starting points for your own implementations:

eobClaimsOverview — Lightweight overview of EOB resources
practitionerBasic — Basic practitioner information
organizationBasic — Basic organization information

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()",
        },
      ],
    },
  ],
}

#More examples

Starting points for your own implementations:

eobClaimsOverview — Lightweight overview of EOB resources

import { ViewDefinition } from '@medplum/fhirtypes';

export const eobClaimsOverview = {
  resourceType: 'ViewDefinition',
  resource: 'ExplanationOfBenefit',
  name: 'eob-claims-overview',
  status: 'active',
  description: 'Lightweight overview of all EOB types for search and filtering',
  select: [
    {
      column: [
        {
          name: 'id',
          path: 'getResourceKey()',
          description: 'Unique identifier for the EOB resource',
        },
        {
          name: 'type',
          path: "type.coding.where(system='http://terminology.hl7.org/CodeSystem/claim-type').code.first()",
          description: 'Claim type: pharmacy, institutional, professional, or vision',
        },
        {
          name: 'subType',
          path: 'subType.coding.code.first()',
          description: 'Claim subtype (e.g., inpatient, outpatient)',
        },
        {
          name: 'status',
          path: 'status',
          description: 'Status of the EOB',
        },
        {
          name: 'outcome',
          path: 'outcome',
          description: 'Outcome of the EOB',
        },
        {
          name: 'billablePeriod_start',
          path: 'billablePeriod.start',
          description: 'Service start date',
        },
        {
          name: 'billablePeriod_end',
          path: 'billablePeriod.end',
          description: 'Service end date',
        },
        {
          name: 'created',
          path: 'created',
          description: 'Date the EOB resource was created',
        },
        {
          name: 'payment_date',
          path: 'payment.date',
          description: 'Date when payment was made',
        },
        {
          name: 'diagnosis_codes',
          path: 'diagnosis.diagnosisCodeableConcept.coding.code.distinct()',
          collection: true,
          description: 'All diagnosis codes from this claim',
        },
        {
          name: 'diagnosis_systems',
          path: 'diagnosis.diagnosisCodeableConcept.coding.system.distinct()',
          collection: true,
          description: 'Code systems for diagnoses (e.g., ICD-10)',
        },
        {
          name: 'procedure_codes',
          path: 'procedure.procedureCodeableConcept.coding.code.distinct()',
          collection: true,
          description: 'All procedure codes from this claim',
        },
        {
          name: 'procedure_systems',
          path: 'procedure.procedureCodeableConcept.coding.system.distinct()',
          collection: true,
          description: 'Code systems for procedures (e.g., ICD-10-PCS)',
        },
        {
          name: 'service_codes',
          path: 'item.productOrService.coding.code.distinct()',
          collection: true,
          description: 'Service/product codes (CPT, HCPCS, NDC, etc.)',
        },
        {
          name: 'service_systems',
          path: 'item.productOrService.coding.system.distinct()',
          collection: true,
          description: 'Code systems for services',
        },
        {
          name: 'medication_codes',
          path: "item.where(productOrService.coding.system.contains('ndc')).productOrService.coding.code.distinct()",
          collection: true,
          description: 'NDC codes for medications (pharmacy claims only)',
        },
        {
          name: 'item_productOrService_display',
          path: 'item.productOrService.coding.display.distinct()',
          collection: true,
          description: 'Display names for products/services/medications from items',
        },
        {
          name: 'provider_reference',
          path: 'provider.reference',
          description: 'Reference to the primary provider',
        },
        {
          name: 'provider_display',
          path: 'provider.display',
          description: 'Provider display name if available',
        },
        {
          name: 'insurer_reference',
          path: 'insurer.reference',
          description: 'Reference to the insurance organization',
        },
        {
          name: 'insurer_display',
          path: 'insurer.display',
          description: 'Insurer display name if available',
        },
        {
          name: 'facility_reference',
          path: 'facility.reference',
          description: 'Reference to the facility (institutional claims)',
        },
        {
          name: 'facility_display',
          path: 'facility.display',
          description: 'Facility display name if available',
        },
        {
          name: 'careTeam_provider_references',
          path: "careTeam.provider.reference.join(',')",
          description: 'References to all care team members',
        },
        {
          name: 'careTeam_provider_displays',
          path: "careTeam.provider.display.join(',')",
          description: 'Display names for care team members',
        },
        {
          name: 'total_submitted',
          path: "total.where(category.coding.where(system='http://terminology.hl7.org/CodeSystem/adjudication').code='submitted').amount.value.first()",
          description: 'Total amount submitted',
        },
        {
          name: 'total_benefit',
          path: "total.where(category.coding.where(system='http://terminology.hl7.org/CodeSystem/adjudication').code='benefit').amount.value.first()",
          description: 'Total benefit amount paid by insurance',
        },
        {
          name: 'total_memberliability',
          path: "total.where(category.coding.where(system='http://hl7.org/fhir/us/carin-bb/CodeSystem/C4BBAdjudication').code='memberliability').amount.value.first()",
          description: 'Total amount patient is responsible for',
        },
        {
          name: 'total_deductible',
          path: "total.where(category.coding.where(system='http://terminology.hl7.org/CodeSystem/adjudication').code='deductible').amount.value.first()",
          description: 'Total deductible amount',
        },
        {
          name: 'total_copay',
          path: "total.where(category.coding.where(system='http://hl7.org/fhir/us/carin-bb/CodeSystem/C4BBAdjudication').code='copay').amount.value.first()",
          description: 'Total copay amount',
        },
        {
          name: 'claim_type_details',
          path: "supportingInfo.where(category.coding.code='typeofbill').code.coding.code.first()",
          description: 'Type of bill code for institutional claims',
        },
      ],
    },
  ],
} as ViewDefinition;

practitionerBasic — Basic practitioner information

import { ViewDefinition } from '@medplum/fhirtypes';

export const practitionerBasic = {
  resourceType: 'ViewDefinition',
  resource: 'Practitioner',
  name: 'practitioner-basic',
  status: 'active',
  description: 'Basic practitioner information for referenced practitioners in EOBs',
  select: [
    {
      column: [
        {
          name: 'id',
          path: 'getResourceKey()',
          description: 'Unique identifier for the Practitioner resource',
        },
        {
          name: 'active',
          path: 'active',
          description: "Whether this practitioner's record is in active use",
        },
        {
          name: 'name_text',
          path: "name.text.join(', ')",
          description: 'Full name as a single text string',
        },
        {
          name: 'name_family',
          path: "name.family.join(', ')",
          description: 'Family name (surname)',
        },
        {
          name: 'name_given',
          path: "name.given.join(', ')",
          description: 'Given names (first, middle)',
        },
        {
          name: 'name_prefix',
          path: "name.prefix.join(', ')",
          description: 'Name prefix (Dr., Mr., etc.)',
        },
        {
          name: 'name_suffix',
          path: "name.suffix.join(', ')",
          description: 'Name suffix (Jr., Sr., MD, etc.)',
        },
        {
          name: 'identifier_system',
          path: 'identifier.system',
          collection: true,
          description: 'Code system for identifiers (e.g., NPI system)',
        },
        {
          name: 'identifier_value',
          path: 'identifier.value',
          collection: true,
          description: 'Identifier values (e.g., NPI number)',
        },
        {
          name: 'identifier_type_code',
          path: 'identifier.type.coding.code',
          collection: true,
          description: 'Type of identifier',
        },
        {
          name: 'telecom_system',
          path: 'telecom.system',
          collection: true,
          description: 'Telecom system (phone, email, fax, etc.)',
        },
        {
          name: 'telecom_value',
          path: 'telecom.value',
          collection: true,
          description: 'Telecom contact value',
        },
        {
          name: 'telecom_use',
          path: 'telecom.use',
          collection: true,
          description: 'Telecom use (home, work, mobile, etc.)',
        },
        {
          name: 'address_text',
          path: 'address.text',
          collection: true,
          description: 'Full address as text',
        },
        {
          name: 'address_line',
          path: 'address.line',
          collection: true,
          description: 'Street address lines',
        },
        {
          name: 'address_city',
          path: 'address.city',
          collection: true,
          description: 'City',
        },
        {
          name: 'address_state',
          path: 'address.state',
          collection: true,
          description: 'State or province',
        },
        {
          name: 'address_postalCode',
          path: 'address.postalCode',
          collection: true,
          description: 'Postal/ZIP code',
        },
        {
          name: 'address_country',
          path: 'address.country',
          collection: true,
          description: 'Country',
        },
        {
          name: 'gender',
          path: 'gender',
          description: 'Gender',
        },
        {
          name: 'qualification_code',
          path: 'qualification.code.coding.code',
          collection: true,
          description: 'Qualification codes (certifications, specialties)',
        },
        {
          name: 'qualification_display',
          path: 'qualification.code.coding.display',
          collection: true,
          description: 'Qualification descriptions',
        },
        {
          name: 'qualification_system',
          path: 'qualification.code.coding.system',
          collection: true,
          description: 'Code systems for qualifications',
        },
      ],
    },
  ],
} as ViewDefinition;

organizationBasic — Basic organization information

import { ViewDefinition } from '@medplum/fhirtypes';

export const organizationBasic = {
  resourceType: 'ViewDefinition',
  resource: 'Organization',
  name: 'organization-basic',
  status: 'active',
  description:
    'Basic organization information for referenced organizations in EOBs (insurers, providers, facilities)',
  select: [
    {
      column: [
        {
          name: 'id',
          path: 'getResourceKey()',
          description: 'Unique identifier for the Organization resource',
        },
        {
          name: 'active',
          path: 'active',
          description: "Whether this organization's record is in active use",
        },
        {
          name: 'name',
          path: 'name',
          description: 'Name of the organization',
        },
        {
          name: 'alias',
          path: "alias.join(', ')",
          description: 'Alternate names for the organization',
        },
        {
          name: 'identifier_system',
          path: 'identifier.system',
          collection: true,
          description: 'Code system for identifiers (e.g., NPI, Tax ID)',
        },
        {
          name: 'identifier_value',
          path: 'identifier.value',
          collection: true,
          description: 'Identifier values (e.g., NPI number, Tax ID)',
        },
        {
          name: 'identifier_type_code',
          path: 'identifier.type.coding.code',
          collection: true,
          description: 'Type of identifier (e.g., payerid, naiccode)',
        },
        {
          name: 'type_code',
          path: 'type.coding.code',
          collection: true,
          description: 'Organization type codes',
        },
        {
          name: 'type_display',
          path: 'type.coding.display',
          collection: true,
          description:
            'Organization type descriptions (e.g., Healthcare Provider, Insurance Company)',
        },
        {
          name: 'type_system',
          path: 'type.coding.system',
          collection: true,
          description: 'Code systems for organization types',
        },
        {
          name: 'telecom_system',
          path: 'telecom.system',
          collection: true,
          description: 'Telecom system (phone, email, fax, url, etc.)',
        },
        {
          name: 'telecom_value',
          path: 'telecom.value',
          collection: true,
          description: 'Telecom contact value',
        },
        {
          name: 'telecom_use',
          path: 'telecom.use',
          collection: true,
          description: 'Telecom use (work, etc.)',
        },
        {
          name: 'address_text',
          path: 'address.text',
          collection: true,
          description: 'Full address as text',
        },
        {
          name: 'address_line',
          path: 'address.line',
          collection: true,
          description: 'Street address lines',
        },
        {
          name: 'address_city',
          path: 'address.city',
          collection: true,
          description: 'City',
        },
        {
          name: 'address_state',
          path: 'address.state',
          collection: true,
          description: 'State or province',
        },
        {
          name: 'address_postalCode',
          path: 'address.postalCode',
          collection: true,
          description: 'Postal/ZIP code',
        },
        {
          name: 'address_country',
          path: 'address.country',
          collection: true,
          description: 'Country',
        },
        {
          name: 'partOf',
          path: 'partOf.reference',
          description: 'Reference to parent organization',
        },
      ],
    },
  ],
} as ViewDefinition;

#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'