Querying an openEHR CDR with SQL, Cypher, and GraphQL? Yes, it’s possible.

Implementation
1

I just posted an article on LinkedIn but I’m posting most of it here for a more technical discussion.

AQL is a great technical achievement designed specifically for hierarchical health data :clap:

But let’s be honest: most developers and analysts outside the openEHR ecosystem have never heard of it. When onboarding new engineers to a health tech project, teaching them AQL from scratch is a massive bottleneck. Usually, they have to write or look at something like this just to get blood pressure data:

2026-07-07 - Query openEHR - AQL
2026-07-07 - Query openEHR - AQL1446×394 89.9 KB

What if we could build an openEHR Clinical Data Repository (CDR) but let the users use the technologies they already know? They could even treat the CDR as a PostgreSQL database.

By leveraging ArcadeDB, we can enable polyglot querying over openEHR data structures. The developers and analysts can pick the exact query language that fits their background:

  • SQL: Extended with graph traversal, full-text search, vector, and time-series functions.
  • Cypher (openCypher): A drop-in replacement for Neo4j Cypher.
  • Gremlin: Apache TinkerPop graph traversal.
  • GraphQL: Schema-driven queries natively over HTTP.
  • MongoDB QL: Document-oriented queries via the MongoDB protocol.
  • GQL: Native Graph Query Language support coming soon.

The best part? Because ArcadeDB supports the PostgreSQL wire protocol, the users can use standard Postgres tools and drivers to connect to the server and run these queries. For advanced use cases, even more power can be unlocked directly via the Java API.

SQL and Cypher are particularly powerful for handling the deeply nested, interconnected nature of clinical models.

Here is how that exact same blood pressure AQL query looks when translated into SQL and Cypher, along with the clean JSON output you get back.

  1. The SQL Approach (Using ArcadeDB’s Extended SQL)

2026-07-07 - Query openEHR - SQL 2
2026-07-07 - Query openEHR - SQL 2994×422 46 KB

Resulting JSON:

2026-07-07 - Query openEHR - SQL result
2026-07-07 - Query openEHR - SQL result936×720 45.8 KB

  1. The Cypher Approach (Graph & List Comprehension)

For those that come from a Neo4j or graph background, they can use Cypher’s native pattern matching and projections to extract the exact schema they want:

2026-07-07 - Query openEHR - Cypher 2
2026-07-07 - Query openEHR - Cypher 2964×802 37.2 KB

We don’t have to force users to learn another query language just to use openEHR.

Moving from a working multi-model query proof-of-concept to building the official openEHR REST API layer is where this transforms from a cool database experiment into a production-ready, interoperable Clinical Data Repository (CDR).

Where the Project Stands Today (and What’s Next)

I have built a working proof of concept of an openEHR CDR using the Apache 2.0 licensed ArcadeDB. Currently, it can successfully load any openEHR COMPOSITION and make the data immediately available for multi-model querying.

Next up on the roadmap: Adding the official openEHR REST API layer on top of this engine to handle standardized EHR management and composition ingestion compliance.

Since ArcadeDB is already handling the heavy lifting of storing and querying the raw COMPOSITIONs via its multi-model engine, the REST API layer will essentially act as the compliance and translation gateway.

This approach is great for analyzing openEHR data. Could it be built into a full open source version CDR?

What do you think?

2

I think that any querying mechanism or technology that allows data accessibility in different ways, for different use cases, that’s reliable and that can perform well on millions of records, is worth using.

Personally I think AQL is a half baked spec that focus too much on the language and not so much on the logic and models involved, areas in which I think standardizing adds more value to implementers.

3

Made some progress with SQL queries using graph traversal:

`openEHR-EHR-COMPOSITION.encounter.v1` has 'openEHR-EHR-OBSERVATION.blood_pressure.v2'

SQL:

SELECT $blood_pressure_obs.data.events.data.items AS blood_pressure,
       $pulse_obs.data.events.data.items AS pulse
FROM   `openEHR-EHR-COMPOSITION.encounter.v1`
LET    $blood_pressure = out('has_openEHR-EHR-OBSERVATION.blood_pressure.v2'),
       $pulse = out('has_openEHR-EHR-OBSERVATION.pulse.v2'),
WHERE  $blood_pressure CONTAINS (data.events CONTAINS (data.items CONTAINS (
          archetype_node_id = "at0004" AND  -- Systolic 
          value.magnitude > 140 AND value.units = "mm[Hg]"))) AND
       $pulse CONTAINS (data.events CONTAINS (data.items CONTAINS (
          archetype_node_id = "at0004" AND  -- Rate 
          value.magnitude > 80 AND value.units = "/min")))

Result:

{
  "blood_pressure": [[[
    {"_type": "ELEMENT", "archetype_node_id": "at0004",
      "name": {"_type": "DV_TEXT", "value": "Systolic"},
      "value": {"_type": "DV_QUANTITY",
        "magnitude": 140.5,
        "units": "mm[Hg]"
      }
    },
    {"_type": "ELEMENT", "archetype_node_id": "at0005",
      "name": {"_type": "DV_TEXT", "value": "Diastolic"},
      "value": {"_type": "DV_QUANTITY",
        "magnitude": 80.1,
        "units": "mm[Hg]"
      }
    }
  ]]],
  "pulse": [[[
    {"_type": "ELEMENT", "archetype_node_id": "at0004",
      "name": {"_type": "DV_TEXT", "value": "Rate"},
      "value": {"_type": "DV_QUANTITY",
        "magnitude": 83.7,
        "units": "/min"
      }
    }
  ]]]
}
4

Hey @borut.jures

I think what you are doing here is super useful. There is a real bottleneck when it comes to new developers learning openEHR, and being able to query an openEHR CDR using SQL, Cypher, or GraphQL helps with that.

We have had the privilege of training hundreds of developers and having them build applications using openEHR. Because of that, I have seen what they build, what they struggle with, and where most implementations eventually end up.

In my experience, actual application use cases are quite simple. They do not use a lot of what the RM can represent. Even with events, most developers do not use multiple types of events inside an archetype. They usually constrain the cardinality to 0..1 in the template and treat it as one record.

I still think the main value that openEHR brings is the maximal clinical data models. The archetypes give us a detailed, clinically reviewed starting point. I am less convinced that every application needs to implement all the complexity of the RM in its operational database.

This is where I think our approaches are different.

As I mentioned in the openEHR archetypes as SQL Tables discussion, I am not trying to implement the complete openEHR RM relationally. That approach has been attempted several times and has many of the problems mentioned in that thread. You end up with many tables, many joins, complex cardinalities, and SQL queries that still require an understanding of the RM.

Your blood pressure query is a good example. It uses SQL, but the developer still needs to understand:

  • The composition and observation archetype IDs
  • The data.events.data.items structure
  • Archetype node IDs such as at0004
  • DV_QUANTITY
  • Magnitude and units
  • How the openEHR RM is represented in the database

For someone who already knows openEHR, this is useful because they can use SQL instead of AQL. But if I show this query to a developer who only knows SQL, they still need to learn quite a lot about openEHR before they can work with the data.

What we are trying at Medblocks is more opinionated. We want to generate ordinary PostgreSQL tables from the archetypes. Ideally, there should be no indication in those tables that the model came from openEHR.

A blood pressure table would have columns such as:

create table blood_pressure (
    id uuid primary key default uuid_generate_v4(),
    form_id uuid not null references form(id) on delete cascade,

    event_type text not null default 'any_event' check (
        event_type in (
            'any_event',
            '24_hour_average'
        )
    ),

    systolic_pressure_magnitude numeric,
    systolic_pressure_units text check (
        systolic_pressure_units in ('mm[Hg]')
    ),

    diastolic_pressure_magnitude numeric,
    diastolic_pressure_units text check (
        diastolic_pressure_units in ('mm[Hg]')
    )
    ...
)

The developer should not need to know that systolic pressure was at0004, that it was inside an event, or that its original value was a DV_QUANTITY.

The archetype node IDs and RM paths still need to exist somewhere, but they should be generated mapping metadata. The application database itself should not need to expose them.

I think this is valuable for a few reasons.

First, the tables work with the existing SQL ecosystem. A developer can use Django, Express, Prisma, Drizzle, SQLAlchemy, Hibernate, or any other ORM. They can inspect the database in DBeaver. They can use Budibase, Superset, PostgREST, PostGraphile, or Hasura without requiring an openEHR-specific connector.

Second, these clinical models can live in the same database as the rest of the application. The organisation can have its patient, encounter, scheduling, billing, workflow, and administrative tables alongside the clinical tables. Not everything in a healthcare application needs to be represented as openEHR data.

Third, we can make an opinionated decision about what should be computable.

This is also the context behind my question in the discussion about DV_BOOLEAN choice fields and null flavours.

This reflects what I have seen when building EHR systems. Doctors often use the unstructured clinical summary as the definitive record, even when structured forms are available. The structured fields are most useful when their values are unambiguous and directly computable.

The rules we are working on currently include:

  • Mapping simple RM values to native PostgreSQL types
  • Mapping quantities to magnitude and unit columns, with unit and range constraints
  • Mapping coded text to terminology-backed columns using foreign keys
  • Keeping a separate free-text fallback when the archetype explicitly permits DV_CODED_TEXT | DV_TEXT
  • Enforcing that only one option is populated for choice types
  • Flattening observation events so that one SQL row represents one event
  • Adding an event_type only when the archetype contains meaningful event alternatives
  • Flattening instruction activities in a similar way
  • Creating child tables for repeating clusters only when the structure requires them
  • Preserving position for repeating values and ordered structures
  • Failing generation when a choice, repetition, slot, or RM type cannot be mapped safely

Does this preserve every possible nuance of openEHR? No. That is intentional. The SQL schema represents the subset of the archetype that a particular class of applications has decided to support.

My estimate is that this can work for around 95% of the applications we normally see. The remaining applications may need a complete openEHR CDR and the full flexibility of the RM.

The difficult part for us is exporting the data back into openEHR.

Have you tried taking data from an existing SQL-based application and exporting it to an openEHR-compatible REST server? If so, I would be interested in comparing notes, especially around reconstructing compositions and handling template versions.

I would also like to know what tools and parsers you are using for this and whether any of the implementation is already available in the open.

So I think the objective is similar, but the trade-off is different. Would still love to compare notes and collaborate.

5

Thanks for the detailed breakdown. I think your approach at Medblocks is spot on for lowering the barrier to entry, and I agree completely that openEHR’s ultimate value lies in clinically reviewed archetypes.

I agree that nobody should need to know that systolic pressure is at0004. I remember Thomas mentioned that he should have used “keys” (e.g. “systolic_pressure”). In my previous attempts I did away with at-codes by using english terminology terms as “keys”. In the current approach I’m sticking with at-codes for now.
I would still expect openEHR app developers to familiarize themselves with the RM :wink:

Reconstructing compositions from flat relational tables for export is definitely the trickiest part of the equation. This is why my approach is implementing full RM. In your case you could keep the current columns and add a JSON rm_data column(s) for the full RM data (see systolic_pressure_rm_data):

systolic_pressure_magnitude numeric,
systolic_pressure_units text check (
    systolic_pressure_units in ('mm[Hg]')
),
systolic_pressure_rm_data jsonb,

In my approach I want all data stored with strictly typed data types. No schema-less JSON is used in my implementation. RM types are created as ArcadeDB Document Types and ArcadeDB checks that the data conforms to these types. I couldn’t achieve this with Postgres. I tried to implement this at the application level (e.g., using Java Entity classes), but with ArcadeDB I can define custom RM types at the database level.

Each openEHR COMPOSITION is mapped to its own Document Type (document types are analogous to tables in a relational database). When adding a new openEHR COMPOSITION, I create a new Document Type and populate it with the COMPOSITION’s data. Document Types are also created for all the archetypes used by the COMPOSITION. Each template inherits from the COMPOSITION type and the archetypes inherit from their RM classes. No database schema migrations are needed, since templates and archetypes don’t add any columns to the RM types they inherit from.

Using edges, I connect the templates/archetypes/EHR/VERSION to each other. This allows us to query/traverse the graph database to retrieve openEHR data. This is much more efficient than traditional relational databases which require many joins to retrieve data.

No special tools or parsers are used in my implementation. It is just a native ArcadeDB implementation of the RM. I have a simple walker through the JSON COMPOSITION data which saves the data into appropriate archetype “tables”.

For new templates/archetypes it creates their tables when needed. It also creates “tables” for the archetypes without version information and then the versioned “tables” inherit from these. Select statements can use the “table” without a version to retrieve data from all versions of a particular archetype. This might help prevent queries from breaking when a new version of an archetype is introduced (I have to analyze some archetypes with multiple versions).

The implementation will be open sourced. Currently it is in private beta.

6

The issue with natural language keys is the language dependency. Let’s say I use all my keys in Spanish. I’m sure that will generate issues for others. That’s the same issue with the current flat formats: using language depending paths.

7

I agree, but I wonder what is better: at-codes that everybody has to look up all the time, or keys.

Technically it is possible to use the keys in multiple languages. Let’s say I use English keys and you use Spanish in your queries. A query could be processed to automatically substitute the English/Spanish words with at-codes. Or the query could be processed to replace English keys with Spanish keys.

I assume the keys are autogenerated from the term definitions.

8

Personally, I would discard “human readability” as a requirement, specially when data is meant to be read by systems.

For instance, you can argue for querying, looking at names is better than looking at codes, and I would argue or question why queries SHOULD be hand written and read by humans at all. Using an editor would make all the problems go away: in the editor you see labels in your own language, though what’s generated (queries for example) are generated/serialized for system consumption only.

Note that editor could be genetic and generate AQL is whatever query syntax you want to use.

9

Just genuinely curious @pablo, when you write code, do you define your variable in Spanish?
Is this common practice to do so in by other professional teams?

10

I have a long answer that would require to explain the differences and the why’s, also my own vision bout t subject, though I don’t want to hijack the thread.

Short answer is yes, developers program in the language that other people will use to read the code. I’ve seen spanish and portuguese code (in terms of the identifiers, like variable names).

Though I think your analogy isn’t correct since it’s not comparing things that are at the same level. Queries are not code. Code is a set of instructions to be executed. Queries are a description of data retrieval.

My personal opinion is: queries shouldn’t be hand written and the query syntax doesn’t matter, so focusing on human readability is meaningless.

I think it’s far superior to have a tool, like an editor, that can be used visually by a human, to generate those descriptions of data retrieval. Those descriptions can have as much metadata (names, descriptions, multi-language support, even bindings with terminologies, etc.) as needed for executing the query and for editing it again using the same tool, like a source format. An optimized version for execution can be created, like an OPT. The query engine then can process the source or the optimized version and retrieve the described data in a standard format, like the AQL result set or even FHIR bundle type=SearchSet.

Then the goal of the query engine is really to translate the “description of data retrieval” a.k.a. query, to the underlying native “query”, SQL or whatever, and transform the native data into the standard format data for retrieval. I think a spec focusing on standardizing this whole process is much valuable than a spec that focuses merely on describing the syntax for the “description of data retrieval”.

Anyway, that’s how I designed it to work in Atomik: visual editor + representation of data retrieval (it’s a vendor neutral JSON) + query engine + standardized result set.

So, yes, I don’t think using names in some identifiers is a good idea, like in paths for queries or flat compositions.