FHIR Patient Demographics: Basics

Ingest HL7 FHIR R5 Patient resources from bulk NDJSON and individual JSON files, flattening nested name, telecom, and address arrays into queryable SQL columns.

Syntax

-- 1. Zone + schema (idempotent)
CREATE ZONE IF NOT EXISTS {{zone_name}} TYPE EXTERNAL
    COMMENT 'External tables, demo datasets and file-backed data';

CREATE SCHEMA IF NOT EXISTS {{zone_name}}.fhir_demos
    COMMENT 'FHIR-backed external tables: HL7 FHIR R5 resources as JSON';

-- 2. Bulk NDJSON table, 50 patients, one per line
CREATE EXTERNAL TABLE IF NOT EXISTS {{zone_name}}.fhir_demos.patients_bulk
USING JSON
LOCATION '{{data_path}}'
OPTIONS (
    file_filter = '*.ndjson',
    json_flatten_config = '{
        "root_path": "$",
        "include_paths": ["$.resourceType", "$.id", "$.name", "$.gender", "$.birthDate"],
        "column_mappings": {"$.id": "patient_id", "$.birthDate": "birth_date"},
        "max_depth": 3,
        "separator": "_",
        "default_array_handling": "to_json",
        "infer_types": true
    }',
    file_metadata = '{"columns":["df_file_name","df_row_number"]}'
);

-- 3. Rich individual-file table, 7 Patient resources with full nesting
CREATE EXTERNAL TABLE IF NOT EXISTS {{zone_name}}.fhir_demos.patients_detailed
USING JSON
LOCATION '{{data_path}}'
OPTIONS (
    file_filter = 'patient-example*.json',
    json_flatten_config = '{
        "root_path": "$",
        "include_paths": [
            "$.resourceType", "$.id", "$.active", "$.gender", "$.birthDate",
            "$.deceasedBoolean", "$.name", "$.telecom", "$.address",
            "$.maritalStatus", "$.managingOrganization"
        ],
        "json_paths": ["$.maritalStatus", "$.managingOrganization"],
        "column_mappings": {
            "$.id": "patient_id",
            "$.birthDate": "birth_date",
            "$.deceasedBoolean": "is_deceased",
            "$.managingOrganization": "managing_org"
        },
        "max_depth": 3,
        "separator": "_",
        "default_array_handling": "to_json",
        "infer_types": true
    }',
    file_metadata = '{"columns":["df_file_name","df_row_number"]}'
);

-- 4. Analyse: gender distribution from bulk export
SELECT gender, COUNT(*) AS patient_count
FROM {{zone_name}}.fhir_demos.patients_bulk
GROUP BY gender
ORDER BY patient_count DESC;

-- 5. Cleanup
DROP EXTERNAL TABLE IF EXISTS {{zone_name}}.fhir_demos.patients_bulk WITH FILES;
DROP EXTERNAL TABLE IF EXISTS {{zone_name}}.fhir_demos.patients_detailed WITH FILES;
DROP SCHEMA IF EXISTS {{zone_name}}.fhir_demos;

Description

## When to Use Reach for this demo when onboarding a hospital's Patient registry exported from an EHR or FHIR server. It models the two canonical FHIR delivery shapes side-by-side: a FHIR Bulk Data NDJSON export (one Patient per line, for batch analytics) and individually-stored Patient JSON resources (rich, deeply nested, for clinical review). It is the right starting point before tackling Observation, Condition, or MedicationRequest. ## What You Will Learn 1. How DeltaForge's `json_flatten_config` + `include_paths` pattern flattens FHIR's nested `name[]`, `telecom[]`, and `address[]` arrays without losing structure. 2. How `column_mappings` rename FHIR JSONPath expressions (`$.id`, `$.birthDate`, `$.deceasedBoolean`) into analyst-friendly snake_case columns. 3. How NDJSON (newline-delimited JSON) is parsed line-by-line via `file_filter = '*.ndjson'`, giving one row per line. 4. How two external tables can point at the same directory and partition the files via `file_filter` (`*.ndjson` vs `patient-example*.json`). 5. How `file_metadata` injects `df_file_name` and `df_row_number` for clinical data lineage and audit trails. 6. How FHIR schema evolution: `deceasedBoolean`, `maritalStatus`, `managingOrganization` appearing only in some patients, produces NULL-padded columns automatically. ## Prerequisites - DeltaForge workspace with permission to `CREATE ZONE` and `CREATE SCHEMA` (external zone type). - Read access to a directory containing FHIR R5 Patient JSON and/or NDJSON files. - Familiarity with FHIR resource basics (Patient, HumanName, Identifier) is helpful but not required.

Pitfalls

• NDJSON is NOT a JSON array, it is newline-delimited, one object per line. Setting `file_filter = '*.json'` on an NDJSON file, or omitting the filter so both are mixed, will either parse the whole file as one row (JSON mode) or silently mix the two shapes into one table.
• FHIR `Patient.gender` is bound to a required ValueSet (`male | female | other | unknown`). If your source EHR mis-exports custom values (e.g. 'M', 'F', 'U'), grouping queries will produce unexpected bucket counts, validate against the FHIR ValueSet before aggregating.
• `birthDate` is a FHIR `date` type that supports partial precision (`YYYY`, `YYYY-MM`, `YYYY-MM-DD`). Lexicographic `SUBSTRING(birth_date, 1, 3)` for decade bucketing works only when all rows are YYYY-MM-DD; partial-precision dates will bucket into the wrong decade.
• `deceasedBoolean` is a choice element: FHIR also allows `deceasedDateTime`. A column mapping for `$.deceasedBoolean` will be NULL on resources that chose the datetime form; add `$.deceasedDateTime` to `include_paths` if your source uses it.
• Using `file_filter = 'patient-example*.json'` on a directory that also contains `Patient.ndjson` is safe, but the inverse filter `'*.json'` will accidentally sweep NDJSON files whose extension happens to match, always anchor filters to the resource-specific prefix.
• `maritalStatus` and `managingOrganization` are CodeableConcept / Reference types; listing them in `json_paths` keeps them as JSON blobs. Omitting them from `json_paths` asks the flattener to walk into them, which produces dozens of per-coding columns.
• `df_row_number` is 1-based per file. For individual JSON files it will always be 1, do not use it as a unique cross-file key; combine with `df_file_name` instead.

See Also

• demo_fhir_clinical_observations
• demo_fhir_clinical_records
• demo_fhir_medications_prescriptions
• demo_fhir_xml_clinical_resources