Why FHIR Resists the Spreadsheet
FHIR is a tree. A Patient carries an array of names, each name carries an array of given names; an Observation nests a code inside a CodeableConcept inside an array of Codings. Every analytical tool you actually want to use — a spreadsheet, a SQL table, a dataframe, a BI dashboard — wants a rectangle. Getting from one to the other is the single most common piece of unglamorous work in clinical data engineering, and it is where most people lose fidelity without noticing.
This guide is about the conceptual problem rather than any one script: what a "flat row" of FHIR even means, how array indices should be handled, what you give up when you choose one row per resource, and how to pick columns that answer a real question. If you want to see the ideas working on your own data, the FHIR Bundle Explorer applies exactly this model in the browser. For the structural background — what a Bundle is and what lives inside entry — start with What Is a FHIR Bundle.
Leaf Paths: The Unit of Flattening
The right mental model is not "fields" but leaf paths. Walk the resource tree until you reach a primitive — a string, a number, a boolean — and record the dotted path that got you there. A Patient produces leaves like id, gender, birthDate, name.family, name.given, telecom.value, address.city. An Observation produces status, code.coding.code, code.coding.display, valueQuantity.value, valueQuantity.unit, subject.reference.
Leaves are the right unit because they are the only things a cell can hold. An intermediate node like Observation.code is an object; there is no honest way to put it in a cell without either serialising it back to JSON (which defeats the purpose) or silently choosing one of its children. Working leaf-first forces the choice into the open: you decide, explicitly, that the column you want is code.coding.code and not code.text.
Some subtrees are noise for analysis and are better excluded outright. meta holds server bookkeeping (version id, last-updated, profile URLs). text holds the human-readable narrative as an XHTML div — a paragraph of prose that would blow up any table it landed in. extension subtrees are unbounded and site-specific. Dropping meta, text and extension before you enumerate leaves is what keeps a Patient at a dozen useful paths instead of eighty mostly-empty ones. The Bundle Explorer excludes exactly those three by default for this reason.
Depth Is Not Your Friend
FHIR permits contained resources — a whole resource embedded inside another because it has no independent existence. Contained resources add a nesting level and can, in pathological documents, nest further. Any flattening pass needs a depth ceiling so a strange document cannot spin forever; a limit somewhere around a dozen levels is far past anything clinical data actually contains while still terminating on a malformed file.
Collapsing Array Indices: The Decision That Shapes Everything
Here is the fork in the road. A Patient with two names produces, index-faithfully, name[0].family and name[1].family — two different paths. A Patient with three names produces three. If you keep indices in the path, your column set becomes a function of the widest resource in the file: one unusually verbose patient adds columns that are empty for everyone else, and two files of the same data type do not share a schema.
Collapsing the indices — treating name[0].family and name[1].family as the same path, name.family — fixes that. Every value belonging to the same conceptual field lands in one column, the column set depends only on which fields are populated, and two exports of the same resource type line up. This is the model the FHIR Bundle Explorer uses: paths are written without indices, so all values of a repeating field share a single column.
The cost is that a column can now hold more than one value for a single resource, and you have to say what happens then. That is the next decision.
One Row Per Resource, or One Value Per Cell?
There are exactly two defensible answers, and they trade against each other.
Join Mode — One Row Per Resource
Keep the row count equal to the resource count and pack repeated values into the cell with a separator, conventionally a semicolon: a patient with given names Maria and Elena shows Maria;Elena in the name.given column. Nothing is discarded, the row count is predictable, and a COUNT(*) over the table answers "how many patients?" correctly.
The cost is that multi-valued cells are not directly analysable. You cannot group by name.given and get meaningful buckets, because Maria;Elena is its own bucket. Anything downstream that cares about individual values has to split the cell first.
Expand Mode — One Value Per Cell
The alternative is to emit one row per repeat. If a resource's widest selected column holds three values, that resource produces three rows; each row takes the i-th value of every multi-valued column, and single-valued columns repeat down the rows so each row stays self-describing — the resource id and the subject reference appear on every one of the three.
Now every cell holds one value and grouping, filtering and joining behave the way a spreadsheet user expects. The cost is that the row count no longer equals the resource count, so naive counting over-reports, and repeated single values will double-count if you sum them. Both are fine as long as you know which mode you are in; the failure mode is switching modes and forgetting.
A practical rule: use join mode when the table is a human-readable inventory or an export destined for a system that will re-parse it, and expand mode when the table feeds an aggregation — value distributions, code frequencies, counts by category. And when the destination is a file rather than a screen, the same choice governs the export; our guide to converting a FHIR Bundle to CSV covers how the two modes land in an RFC 4180 file.
Zipping Is Not Always Semantic Alignment
One honest caveat about expand mode: it zips columns positionally. If telecom.value holds two entries and address.city holds two entries, row one pairs the first of each and row two the second — but FHIR makes no promise that the first phone number belongs with the first address. Positional zipping is a display convenience, not a semantic join. Where the pairing genuinely matters, select columns from a single repeating branch (all of name.*, say) rather than mixing branches, and the alignment becomes real.
Choosing Columns: Curated, Derived, or Everything
Enumerating leaves gives you every possible column, which is rarely what you want. A realistic Observation from a lab feed can expose forty or more distinct paths, most of which are populated in a handful of instances. Three strategies cover almost every need.
- Curated presets. For the resource types that dominate real bundles, a hand-picked column list answers the obvious question immediately. The Bundle Explorer ships nine: Patient, Observation, Condition, Encounter, MedicationRequest, Procedure, AllergyIntolerance, Immunization and DiagnosticReport. A preset is filtered to the paths actually present in your data, so a bundle whose patients carry no address never shows an empty address column.
- Derived columns. There are around 150 resource types in R4 and nobody should hand-maintain presets for all of them. For anything uncurated — or a curated type whose preset paths are all absent — rank every discovered path by how many resources populate it and take the top twelve. Frequency is a good proxy for usefulness: a path present in every resource is almost certainly part of the record's identity, one present in two is an edge case.
- Everything. Keep an escape hatch that shows all discovered paths. This is how you find the field the preset did not anticipate — a site-specific identifier, an unusual
value[x]choice — and it is the honest answer to "did the tool drop my data?"
Whatever the strategy, columns should carry both a short label for reading and the full dotted path for exporting. The short label (value for valueQuantity.value) keeps a table legible; the full path is what belongs in a CSV header, because it is unambiguous and round-trippable.
The value[x] Problem
FHIR's choice-of-type elements deserve a specific warning. Observation.value[x] serialises as valueQuantity, valueString, valueCodeableConcept, valueBoolean and others depending on what the observation measured. Flattened leaf-first, these are different paths and therefore different columns — so a file mixing numeric lab results and coded findings produces a sparse table where each row fills only one of the value columns. That is correct, not a bug: the underlying data really is heterogeneous. If you need a single value column, you have to decide the coalescing rule yourself, and that rule is domain knowledge no generic flattener can supply.
References Survive Flattening as Strings
A flat row cannot contain a relationship, only a pointer to one. Observation.subject.reference flattens to a string like Patient/123 or urn:uuid:8f2c…, which is exactly what a foreign key looks like in a relational table. That makes per-type tables joinable: export Patients keyed on id, export Observations carrying subject.reference, and the join is obvious.
What flattening cannot do is tell you whether that pointer resolves. Resolution is a separate concern, and a strictly local one when patient data is involved — the Bundle Explorer matches references against resources inside the same file only, by Type/id, bare id, fullUrl and the trailing Type/id of a fullUrl, and never fetches anything over the network. References that match nothing are surfaced as unresolved rather than quietly dropped. For the full picture of literal versus logical references, see How FHIR References Work; for what goes wrong specifically inside transaction bundles, see Debugging a FHIR Transaction Bundle.
Scale, and Where Flattening Stops Being the Right Tool
Flattening in a browser has a ceiling, and an honest tool states it. The Bundle Explorer accepts up to 25 MB of input and 20,000 resources, and when a cap is hit it reports exactly how many resources were dropped rather than presenting a silently short table. Above that, the work belongs in a pipeline: stream the NDJSON, flatten per line, write to Parquet or a database.
It is also worth being clear about what flattening is not. A flattener reads; it does not validate. It will not tell you a required element is missing, that a code is not in its value set, or that a resource fails a profile — those are conformance questions and belong to a validator. Flattening answers a different question: what is actually in this file, and can I look at it in rows?
A Working Sequence
Put together, the process is short. Parse the input — a Bundle, a bare resource, or NDJSON from a bulk export. Group by resourceType, because a table mixing Patients and Observations has no coherent column set. Enumerate leaf paths with indices collapsed and meta, text and extension excluded. Choose columns: a curated preset if one fits, otherwise the most-populated paths, with "show everything" one click away. Pick join or expand depending on whether the destination counts resources or values. Export with the full paths as headers.
Every one of those steps is a decision about fidelity, and none of them has a universally right answer — which is why a flattener that hides them is more dangerous than one that exposes them. Load a file into the FHIR Bundle Explorer, switch between join and expand on the same data, and the trade-off stops being abstract. Because it runs entirely in your browser, you can do that with real records without any of them leaving your machine.