OpenEHR Demo Repository (IRIS + JSON + AQL→SQL)

This project is a functional example of an openEHR-style clinical data repository built on InterSystems IRIS.
It demonstrates how to store compositions as JSON, validate them with the Archie library, and query them using an AQL→SQL transformer based on JSON_TABLE.

1. Repository Design

The repository uses a hybrid strategy:

Raw openEHR compositions are stored as JSON without flattening the structure.
A companion SQL table (OPENEHR_Object.Composition) accelerates querying by storing metadata extracted from the JSON.
Runtime queries use SQL’s JSON_TABLE to project only the AQL-requested parts of a composition.

This preserves openEHR semantics while enabling efficient relational querying.

1.1 Raw Composition Storage

All compositions are persisted as full JSON documents, including all openEHR RM structures such as:

Context
Content
Clusters
Events
Items and values

The storage table typically contains:

Column	Description
`doc`	Raw openEHR JSON
`ehrId`	EHR identifier
`compositionUid`	Composition UID
Metadata	Timestamps, etc.

Deep JSON values are resolved at query time using SQL functions — no schema flattening is required.

1.2 Accelerating Queries with OPENEHR_Object.Composition

A helper table stores selected metadata extracted from each composition JSON:

The list of archetypes contained in the composition
Start/end times from the composition context
EHR ID and composition UID

This enables:

Fast filtering by date
Restriction to compositions containing certain archetypes
Efficient grouping and counting

JSON parsing occurs only when projecting fields into JSON_TABLE.

2. Composition Validation Using Archie

The project integrates the Archie Java library to validate compositions before storing them.

Archie provides:

Full openEHR RM support
Archetype and template processing
Structural and semantic validation

2.1 Validation Workflow

A composition JSON is submitted.
Archie parses and validates it.
Only valid compositions are stored in the repository.

This ensures the repository behaves similarly to production-grade openEHR CDR systems.

3. AQL → SQL Transformation

The method OPENEHR.Utils.AuxilaryFunctions.Transform converts AQL queries into IRIS SQL queries.

It supports:

Full parsing of SELECT, CONTAINS, WHERE, ORDER BY, LIMIT/TOP
Dynamic JSON path building
MATCH/MATCHES translation
COUNT and DISTINCT detection
Multi-composition UNION generation
Reserved word normalization (date_value, time_value)
Template-driven JSON_TABLE base paths loaded from CSV files

The generated SQL uses JSON_TABLE to dynamically extract JSON fragments into relational columns.

3.1 JSON_TABLE Base Paths from CSV Templates

Each COMPOSITION archetype has a CSV file defining the JSON base paths for all contained archetypes.

Example schema:

composition,archetype_id,json_path
openEHR-EHR-COMPOSITION.encounter.v1,openEHR-EHR-OBSERVATION.blood_pressure.v2,$.content[]?(@…)
openEHR-EHR-COMPOSITION.encounter.v1,openEHR-EHR-CLUSTER.laboratory_test_analyte.v1,$.content[]?(@…).data.items[*]?(@…)

The transformer:

Loads the CSV corresponding to the COMPOSITION archetype.
If no COMPOSITION archetype is specified:
- Scans all CSVs in the template directory.
- Selects only those containing all archetypes used in the AQL.
Avoids duplicate JSON paths to prevent redundant UNIONs.

Each matching CSV produces a JSON_TABLE SELECT.

3.2 Projection of AQL Paths into JSON_TABLE Columns

For each AQL expression such as:

m/data[at0001]/items[at0002]/value/value AS medication_text

The transformer builds a relative JSON path:

$.data[]?(@.archetype_node_id==“at0001”).items[]?(@.archetype_node_id==“at0002”).value.value

This becomes part of a JSON_TABLE definition:

JSON_TABLE(c.doc, ‘’ COLUMNS (medication_text VARCHAR(4000) PATH ‘$.data[*]?(@…)’))

3.3 MATCHES / MATCH Translation

The transformer converts AQL MATCHES expressions into SQL:

AQL Expression	SQL Output
`'.text.'`	`col LIKE '%text%'`
`{'.(A\|B).'}`	`(col LIKE '%A%' OR col LIKE '%B%')`
`{'.A.','.B.'}`	`(col LIKE '%A%' OR col LIKE '%B%')`
`{\|120..140\|}`	`col BETWEEN 120 AND 140`
`{\|<100\|}`	`col < 100`
`{\|>160\|}`	`col > 160`

This makes the AQL semantics fully compatible with SQL WHERE clauses.

3.4 COUNT and DISTINCT Handling

The transformer detects when the SELECT includes:

COUNT(...)
COUNT(DISTINCT ...)
DISTINCT keywords

It rewrites the outer SQL to ensure:

Correct aggregation
Proper alias propagation
Elimination of unintended extra columns

This allows AQL aggregate queries to behave as expected.

3.5 UNION Handling Across Multiple Compositions

If the AQL does not specify a COMPOSITION archetype:

The transformer scans all CSV templates.
It selects only CSVs containing all required archetypes.
JSON paths are deduplicated to avoid redundant SELECTs.
Each distinct CSV generates one SELECT.
All such SELECTs are combined using:

UNION ALL

This allows querying multiple composition types in a single AQL.

3.6 Reserved Word Normalization

To avoid SQL keyword conflicts:

AQL alias	SQL alias
`time`	`time_value`
`date`	`date_value`

Both SELECT and ORDER BY clauses are rewritten accordingly.

4. How to run the Repository?

What do you need to install?

Setup

Build the image wich we are going to use during the workshop:

$ git clone https://github.com/intersystems-ib/workshop-openehr
$ cd workshop-openehr
$ docker-compose build

Testing the project

Run the containers to deploy IRIS:

docker-compose up -d

Automatically an IRIS instance will be configured and deployed.

Open the Management Portal.
Login using the default superuser/ SYS account.
Import OPENEHR.postman_collection.json into your local POSTMAN.

You can test the openEHR repository following these steps:

1. Open Save OPT2 call from POSTMAN and replace the body of the call with the content of one of the OPT2 json test files from the prohect (/shared/tests/opt2).
1. Open Save COMPOSITION and replace the body of the call with the content of one of the raw json test files (if you posted a diagnostic OPT2 you have to post a diagnostic example)
1. Check the result from SQL Explorer.

5. Summary

This project provides:

A functional openEHR JSON repository on InterSystems IRIS
High-performance metadata-assisted querying
Composition validation using Archie
A complete AQL→SQL transformation engine

It demonstrates how openEHR principles, JSON-native indexing, and SQL analytics can coexist to form a powerful and extensible Clinical Data Repository (CDR).