I would in most situations where you want to use the CDR for actual clinical work say that it is wise to store the tree structure exactly as a tree stucture similar to what you do @hkrutzer.
For analytical purposes people have differnt opinions. I think @borut.jures solution in Performance of an openEHR CDR based on a graph database proves the point that you can store openEHR in it’s natural tree-shaped form and query efficiently.
That is also what we wanted to show in
ORBDA: An openEHR benchmark dataset for performance assessment of electronic health record servers where both clinical/individual and population queries worked just fine.
My interpretation of some parts of this thread is that there is a world of analytics people that just love to see the world as relational tables no matter what the source is and don’t mind pouring more time into semi-maually sqashing trees into tabular schema and associated documentation than the time it would take to learn/implement how to efficiently store and query trees. I have met several of those that rather adopt the data to the tool than use a tool adopted to the data. (So yes there is a market need for that too.)
If somebody on the other hand really wants to efficiently use relational algebra (e.g. in a map-reduce setting) to query openEHR data without handcrafting Archetype/Template-specific tables and transformations, then i would recommend reading IOS Press Ebooks - Querying Archetype-Based Electronic Health Records Using Hadoop and Dewey Encoding of openEHR Models
Hi!! Welcome to the community. It is great to see new ideas emerging in the CDR space. I doubt there is much disagreement in the community that having some sort of SQL-like interface esp for the analytics community would be really helpful. I know @Seref has been looking at this for some time and ofcourse the SQL-on_fHIR initiative is basically trying to solve the same problem - a SQL facade on native path/graph data.
I’m not an engineer but looking at your eaxmples , my understanding is that intenally most of the current breed of CDRs do something similar - a small set of tables for RM contracts like COMPOSITION, ENTRY etc but with most of the archetype data carried in json or similar objects plus indexing on paths.
Have you looked at SQL-on-FHIR? It does seem to my non-engineer eye to look very close to your create_projection function. SQL-onFHIR (on openEHR) would be an interesting project IMO.
I made several attempts at using RM as close as possible to the native capabilities of the database(s). I settled on a database that perfectly supports implementing RM classes as native types. Simply put, LOCATABLE classes have their own tables, and other classes are composite types. Database strictly validates that the data matches the expected types.
Additionally all archetypes get their own table which inherits from their RM class. For example openEHR-EHR-OBSERVATION.blood_pressure.v2 inherits from OBSERVATION. The archetype tables have no fields of their own; all fields are already defined in their ancestor tables.
Here is an example SQL query used for my database:
SELECT
$blood_pressure.data.events.data.items.value.magnitude AS systolic,
$blood_pressure.data.events.data.items.value.magnitude AS diastolic,
context.start_time AS date_time
FROM `openEHR-EHR-COMPOSITION.encounter.v1` c
LET $blood_pressure = rel(`openEHR-EHR-OBSERVATION.blood_pressure.v2`)
WHERE $blood_pressure CONTAINS (
'/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value/magnitude' >= 130 OR
'/data[at0001]/events[at0006]/data[at0003]/items[at0005]/value/magnitude' >= 90
)
ORDER BY context.start_time DESC
@hkrutzer Can you “hide” the use of functions from the users? SQL doesn’t expect users to specify the types for the selected values.
Here is an example of the SQL query for your approach that I would appreciate as a user:
SELECT
event.'/time/value' AS observed_at,
event.'/data[at0003]/items[at0004]/value/magnitude' AS systolic,
event.'/data[at0003]/items[at0005]/value/magnitude' AS diastolic
FROM
openehr.composition['openEHR-EHR-OBSERVATION.blood_pressure.v2'] AS composition,
composition.'/data[at0001]/events[at0006]' AS event
WHERE composition.template_id = 'vitals'
AND (
event.'/data[at0003]/items[at0004]/value/magnitude' >= 130 OR
event.'/data[at0003]/items[at0005]/value/magnitude' >= 90
)
ORDER BY observed_at
The SQL above looks much more like standard SQL. You would run this SQL through a post-processor to add the function calls shown in your example. With a post-processor you can introduce a shortcut for the CROSS JOIN LITERAL part of the query (I added it to FROM clause in the above example).
Using a post-processor helps the user but it also means inventing your own SQL dialect, which I wanted to avoid. I wanted to use the SQL that the database already supports. This enables the users to use the SQL tools they are already familiar with.