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

1. A composition JSON is submitted.
2. Archie parses and validates it.
3. 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:

1. Loads the CSV corresponding to the COMPOSITION archetype.
2. If no COMPOSITION archetype is specified:
   • Scans all CSVs in the template directory.
   • Selects only those containing all archetypes used in the AQL.
3. 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?

• Git
• Docker (if you are using Windows, make sure you set your Docker installation to use "Linux containers").
• Docker Compose
• Visual Studio Code + InterSystems ObjectScript VSCode Extension

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).
• 2. 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)
• 3. 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).