Ingest HL7 FHIR R5 MedicationRequest and Coverage resources, including contained Medication definitions, dosage instructions, dispense requests, and pharmacy-benefit class arrays, from raw JSON.
-- 1. Zone + schema
CREATE ZONE IF NOT EXISTS {{zone_name}} TYPE EXTERNAL;
CREATE SCHEMA IF NOT EXISTS {{zone_name}}.fhir_demos
COMMENT 'FHIR-backed external tables: HL7 FHIR R5 resources as JSON';
-- 2. MedicationRequest, prescriptions / orders
CREATE EXTERNAL TABLE IF NOT EXISTS {{zone_name}}.fhir_demos.prescriptions
USING JSON
LOCATION '{{data_path}}'
OPTIONS (
file_filter = 'medicationrequest*.json',
json_flatten_config = '{
"root_path": "$",
"include_paths": [
"$.resourceType", "$.id", "$.status", "$.intent", "$.category",
"$.medication", "$.subject", "$.encounter", "$.authoredOn",
"$.requester", "$.reason", "$.dosageInstruction",
"$.dispenseRequest", "$.substitution", "$.contained",
"$.note", "$.insurance"
],
"json_paths": [
"$.dosageInstruction", "$.contained", "$.dispenseRequest",
"$.medication", "$.subject", "$.encounter", "$.requester",
"$.substitution"
],
"column_mappings": {
"$.id": "prescription_id",
"$.authoredOn": "authored_date",
"$.dosageInstruction": "dosage_instructions",
"$.dispenseRequest": "dispense_request"
},
"max_depth": 4, "separator": "_",
"default_array_handling": "to_json", "infer_types": true
}',
file_metadata = '{"columns":["df_file_name","df_row_number"]}'
);
-- 3. Coverage, insurance / self-pay
CREATE EXTERNAL TABLE IF NOT EXISTS {{zone_name}}.fhir_demos.coverage
USING JSON
LOCATION '{{data_path}}'
OPTIONS (
file_filter = 'coverage-example*.json',
json_flatten_config = '{
"include_paths": [
"$.resourceType", "$.id", "$.status", "$.kind", "$.type",
"$.subscriber", "$.beneficiary", "$.period", "$.class",
"$.insurer", "$.policyHolder"
],
"json_paths": ["$.class", "$.type", "$.subscriber", "$.beneficiary",
"$.period", "$.insurer", "$.policyHolder"],
"column_mappings": {"$.id": "coverage_id"},
"max_depth": 3, "separator": "_", "infer_types": true
}',
file_metadata = '{"columns":["df_file_name","df_row_number"]}'
);
-- 4. Prescription status distribution
SELECT status, COUNT(*) AS rx_count
FROM {{zone_name}}.fhir_demos.prescriptions
GROUP BY status
ORDER BY rx_count DESC;
-- 5. Cleanup
DROP EXTERNAL TABLE IF EXISTS {{zone_name}}.fhir_demos.prescriptions WITH FILES;
DROP EXTERNAL TABLE IF EXISTS {{zone_name}}.fhir_demos.coverage WITH FILES;
DROP SCHEMA IF EXISTS {{zone_name}}.fhir_demos;## When to Use Use this demo for pharmacy and revenue-cycle analytics: prescription lifecycle tracking, PBM (pharmacy benefit manager) integration, dispense-quantity forecasting, or coverage verification. FHIR `MedicationRequest` is one of the most structurally rich resources, it commonly uses CONTAINED `Medication` sub-resources, deeply nested `dosageInstruction[]` (timing, route, dose ranges), and `dispenseRequest` (validity period, quantity, supply duration). `Coverage` adds the insurance classification hierarchy (`class[]` with group, plan, rxbin, rxpcn, rxgroup). ## What You Will Learn 1. How FHIR *contained* resources work, a `Medication` embedded inside the `MedicationRequest` via `contained[]` rather than as a separate file, and how `json_paths` preserves them for downstream extraction. 2. How to surface prescription lifecycle fields (`status`, `intent`, `authoredOn`, `authored_date`) while keeping the complex `dosageInstruction[]` and `dispenseRequest` sub-objects as JSON blobs. 3. How FHIR `Coverage.class[]` encodes pharmacy-benefit routing (rxid / rxbin / rxgroup / rxpcn) and why it must stay as JSON for PBM downstream use. 4. How `status` (active/completed/cancelled/…) and `intent` (proposal/plan/order/…) together describe a prescription's authority level. 5. How `kind` on Coverage distinguishes `insurance`, `self-pay`, and `other`, critical for claims routing. 6. How two external tables (`prescriptions`, `coverage`) can be joined through patient references for a pharmacy cohort view. ## Prerequisites - Completion of `fhir-patient-demographics` and `fhir-clinical-records`. - Familiarity with FHIR choice elements (`medication[x]`, `effective[x]`) and CONTAINED resources. - Working knowledge of pharmacy-benefit concepts (PBM, formulary, dispense quantity) helps interpret the data.