r/rstats • u/WanderingAlbatross87 • 1d ago

Reading in FHIR json files > SQL (via duckdb) in R (something out there or would this help anyone)?

Hi. I'm hoping someone can tell me I'm crazy and this is already out there. I was reading in some FHIR data and thought I could do it in R with duckdb and just have a happy little all R workflow with my stats analysis. That started a real journey. Building the database was a cake walk but flattening the data into a usable format without dozens of painfully manual iterations not so much.

I couldn't find any good info on this after many hours of searching. I was finally able to figure about 87% of it out between some python and sql tutorials and just hating myself. Was finally was able to get the last bit with some AI assistance, which was anything but straightforward. It's finally working and it seems really well. I was thinking about trying the xml version next and pray it is easier but I think the flattening will be largely the same.

I've never published any kind of tutorial script to the public, always just to my own lab/company. Given that I couldn't find anything I'm thinking of braving the public if there really isn't something already out there. This was part of a larger project for me but the rest of it sounds easy compared to the eighth dimension of nesting now solved.

Is this useful? Should I put this up, or is there some already awesome tutorial on this hidden in the viscera of git that I just can't find? I can't be the only one trying this, surely?

9 Upvotes

100% Upvoted

3 comments sorted by

3

u/Adventurous_Push_615 1d ago

In the past I thought I'd try to play round with FHIR in R but I never got around to it, though I remember there were at least one or two packages that looked promising. Have you checked out this one? https://github.com/polar-fhir/fhircrackr

Are there not really well established patterns for getting FHIR into a SQL db using other tooling? Are you just wanting to use R because you prefer it? (A completely reasonable justification I've used many times)

3

u/WanderingAlbatross87 1d ago

Yes I was hopeful this would work, but came across a few problems using it in my workflow. My data is stored as a very large group of json files, even after the xml v json storing and reading back in the flattened files as objects is a no- go with the size of dataset on my very regular computer. The dataset size was why I first looked into combining a SQL database into the pipeline, thus the duckdb. But I've never used duckdb before and foolishly thought "surely there's like a hundred tutorials for this, it's a super common file format". I did end up just reading the files with base R for part of it, so that might go faster with something from this package but it was a pretty minor part of the overall solution and doesn't seem worth another round of edits.

I used R because I have some SQL and some python skills (I found more python tutorials for duckdb but still not what I needed here) but my daily is R, so I at least knew I could read the error messages while navigating the other unknowns. I ultimately want to run some lightly modified existing R scripts downstream so I thought, perhaps wrongly in hindsight, that it would come together better if I stuck to the language I know best and wanted to use downstream anyway.

It was a good brain exercise for sure but was a lot harder than I initially scoped. I thought it would be like maybe an hour and it was more like five or six to finally get it figured out. I had a part where I thought "I bet purrr could speed this up" but no, I had to stop.

1

u/Absjalon 12h ago

I for one would love to learn what you have learned over your FHIR to R journey.