In today’s healthcare data landscape, FHIR has become the standard for structured clinical data exchange. However, while FHIR excels at interoperability, its JSON format makes analytics challenging—including FHIR QuestionnaireResponse.
This project demonstrates how to transform FHIR QuestionnaireResponse data from nested JSON into relational SQL tables and vector embeddings. By integrating the InterSystems IRIS FHIR SQL Builder and Vector Search, we unlock the semantic meaning behind patient answers.
Three Steps to Build It
1. Design and Collect the Questionnaire
Start by designing a FHIR questionnaire using the National Library of Medicine(NLM) Form Builder. This tool helps design structured clinical forms that follow FHIR standards. In this project, 100 synthetic patient responses were collected and saved as FHIR QuestionnaireResponse JSON file, ready to be imported into a FHIR server.
2. Transform and Query the Questionnaire Data via SQL
After loading FHIR QuestionnaireResponse resources into the server, use the InterSystems IRIS FHIR SQL Builder to automatically create relational SQL tables. This flattens the nested JSON structure, enabling easy analysis of questionnaire data with standard SQL—all configurable in just a few clicks.
- Refer to the README for the complete FHIR SQL Builder configuration
- The generated SQL table from QuestionnaireResponse data is ready for querying and analysis
3. Add Vector Search for Semantic Understanding
Finally, integrate IRIS Vector Search to add semantic intelligence on top of the structured questionnaire data. It allows users to search and interact with questionnaire responses based on meaning and context rather than exact words, turning the data into a more intuitive, intelligent tool.
Examples:
- A search for “diabetes medication” retrieves responses mentioning drugs like metformin, insulin glargine, or acarbose—even if the exact phrase isn’t present.
- When a user asks, “Which patients have a family history of heart disease and take cholesterol-lowering medication?”, the system semantically links related data—connecting cardiac conditions with drugs like propranolol, spironolactone, or atorvastatin—and produces a concise summary of at-risk patients.
To explore the full workflow and code implementation, visit the Open Exchange.
Key Takeaways
Through three key steps—designing questionnaires with the NLM Form Builder, transforming them into SQL tables, and enhancing them with vector search—this workflow turns FHIR QuestionnaireResponse data into a powerful tool for clinical understanding and decision support.
References
1. National Library of Medicine (NLM) Form Builder
2. InterSystems IRIS For Health FHIR SQL Builder
3. InterSystems IRIS Vector Search
4. https://openexchange.intersystems.com/package/iris-fhirsqlbuilder
5. https://www.youtube.com/watch?v=ewxyh2XNLv0
Open Exchange