Querying with AQL

The Archetype Query Language (AQL 1.1) is how you read data out of FerroEHR. Instead of querying hidden database tables, you query the clinical model directly: you name the RM types and archetypes you want, express structural nesting with CONTAINS, and select values by their path within an archetype. The same query runs unchanged on any conformant openEHR system. This chapter is a practical walkthrough — the language, how to run queries over HTTP, parameters, stored queries, version scope, terminology, pagination, and the supported feature envelope.

The shape of a query
Running a query over HTTP
- Scoping to an EHR
The result set
Parameters
Stored queries
Version scope: LATEST_VERSION and ALL_VERSIONS
Terminology in queries
Pagination and limits
What is supported

The shape of a query

An AQL statement has the familiar SELECT … FROM … WHERE … ORDER BY skeleton, but the “tables” are RM types and the “columns” are archetype paths:

SELECT
    c/name/value AS composition_name,
    o/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value/magnitude AS systolic
FROM EHR e
    CONTAINS COMPOSITION c
        CONTAINS OBSERVATION o[openEHR-EHR-OBSERVATION.blood_pressure.v2]
WHERE o/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value/magnitude > 140
ORDER BY systolic DESC

FROM binds variables to RM types (EHR e, COMPOSITION c, OBSERVATION o). A type can be constrained by archetype id in square brackets (OBSERVATION o[openEHR-EHR-OBSERVATION.blood_pressure.v2]). Naming a parent archetype also returns data recorded under its specialisations, as openEHR requires: for ADL 1.4 identifiers the specialisation is the hyphen-extended concept (…blood_pressure matches …blood_pressure-cuff), and for ADL 2 identifiers — where the hyphen carries no such meaning — the lineage is read from the ADL 2 archetypes and templates you have uploaded, so the parent matches every stored specialisation of it whatever its concept is named. In both cases the major version is a hard boundary: .v1 never matches .v2 data.
CONTAINS expresses structural containment — “an EHR that contains a composition that contains a blood-pressure observation”. Chains can nest several deep, and combine with AND, OR, and NOT.
SELECT projects values by path. Paths use archetype node ids (at0004) and RM attribute names (value/magnitude); AS names a column.
WHERE filters on typed leaf values, with comparisons, EXISTS, LIKE, MATCHES, and boolean combinators. Comparisons over multi-valued paths use any-match semantics: when a path matches several nodes, the predicate holds if any matched value satisfies it (the AQL specification is silent here; any-match is this engine’s documented convention — deterministic and index-friendly).
ORDER BY, LIMIT, and OFFSET behave as you expect; quantities order by their openEHR magnitude semantics.

Running a query over HTTP

The query API lives under the base path at /query/aql. The simplest form is a POST with a JSON body:

curl -u ferroehr:ferroehr \
  -H 'Content-Type: application/json' \
  -d '{"q":"SELECT e/ehr_id/value FROM EHR e"}' \
  http://localhost:8080/ferroehr/rest/openehr/v1/query/aql

The body fields are:

Field	Meaning
`q`	The AQL text (required).
`offset`	Rows to skip (default 0).
`fetch`	Maximum rows to return.
`query_parameters`	An object of named parameter values (see below).

There is also a GET /query/aql form taking q, offset, fetch, an optional ehr_id, and query_parameters as query-string parameters — convenient for simple, cacheable reads.

The query API is JSON only (Accept: application/json).

Scoping to an EHR

You can restrict a query to one EHR without writing the constraint into the AQL: pass an ehr_id query-string parameter, or the openehr-ehr-id request header. Both forms work on every execution endpoint — ad-hoc and stored, GET and POST alike. (openEHR-EHR-id is the deprecated spelling of the same header and still resolves, HTTP header names being case-insensitive.)

If a request carries both forms they must name the same EHR; a request whose parameter and header name different EHRs is self-contradictory and is rejected with a 400 Bad Request.

The id must exist: a malformed id is a 400, and a well-formed id that matches no EHR is an honest 404 Not Found rather than an empty result set, so a typo cannot masquerade as “no data”.

The result set

A query returns a RESULT_SET: a description of the columns and an array of row tuples.

{
  "q": "SELECT e/ehr_id/value FROM EHR e",
  "columns": [
    { "name": "#0", "path": "/ehr_id/value" }
  ],
  "rows": [
    [ "7d44b88c-4199-4bad-9764-5da0e2a97441" ],
    [ "b1e2c3d4-5678-90ab-cdef-1234567890ab" ]
  ]
}

Each entry in columns names the column — the AS alias, or #<index> when you did not alias it — and its path. Each row in rows is an array of cells, one per column in column order. A cell can be a scalar or a full RM object (for example {"_type":"DV_TEXT","value":"Labs"}) depending on what you selected.

The response also carries a meta block. Its _executed_aql field is the AQL the server actually ran, with your named parameters substituted in as literals — paste it straight back into an ad-hoc query when debugging a parameterised call. The top-level q keeps the text exactly as you submitted it.

Query responses carry a weak ETag that is a content digest of the result set: two runs returning identical results carry the identical tag, so a client can cheaply detect “nothing changed” between polls.

Parameters

Parameterise a query with named placeholders (a name preceded by a dollar sign) and supply the values in query_parameters. This is the safe way to inject values — no string concatenation:

curl -u ferroehr:ferroehr -H 'Content-Type: application/json' -d '{
  "q": "SELECT c FROM EHR e CONTAINS COMPOSITION c WHERE c/name/value = $name",
  "query_parameters": { "name": "Vital signs" }
}' http://localhost:8080/ferroehr/rest/openehr/v1/query/aql

Stored queries

You can register a query once, under a qualified name and version, and execute it by name later. Storing is done through the definition API with the AQL as a plain-text body; executing is done through the query API.

# Store a query as org.example::bp_over, version 1.0.0
curl -u ferroehr:ferroehr -X PUT \
  -H 'Content-Type: text/plain' \
  --data-binary 'SELECT o/data[at0001]/events[at0006]/data[at0003]/items[at0004]/value/magnitude FROM EHR e CONTAINS OBSERVATION o[openEHR-EHR-OBSERVATION.blood_pressure.v2]' \
  http://localhost:8080/ferroehr/rest/openehr/v1/definition/query/org.example::bp_over/1.0.0

# List and fetch stored queries
curl -u ferroehr:ferroehr \
  http://localhost:8080/ferroehr/rest/openehr/v1/definition/query/org.example::bp_over

# Execute it
curl -u ferroehr:ferroehr \
  http://localhost:8080/ferroehr/rest/openehr/v1/query/org.example::bp_over/1.0.0

PUT /definition/query/{name}[/{version}] — store (version is a SemVer; storing an existing version returns 409). An optional query_type parameter names the formalism, default AQL (case-insensitive); anything other than AQL is rejected with 400 — the server never silently stores a query it cannot execute.
The name is [{namespace}::]{query-name} — the namespace is optional, and the query-name may use letters, digits, _, ., and -, so plain names like my_compositions and dotted names like cnf.ward_dashboard are valid as-is. The query-name aql is reserved (case-insensitive, it would collide with the ad-hoc /query/aql route) and is rejected with 400.
GET /definition/query/{name}[/{version}] — list or fetch.
GET|POST /query/{name}[/{version}] — execute, taking the same offset, fetch, and query_parameters as ad-hoc queries. A version can be given exactly (1.0.0) or as a prefix (1).

Version scope: LATEST_VERSION and ALL_VERSIONS

By default a query sees the latest version of each object. FerroEHR also supports querying the entire version history — a capability many CDRs lack. Wrap a source in VERSION and choose the scope:

SELECT v/commit_audit/time_committed, c/name/value
FROM EHR e
    CONTAINS VERSION v[ALL_VERSIONS]
        CONTAINS COMPOSITION c

LATEST_VERSION (the default) reads only current versions; ALL_VERSIONS reads across history, so you can see how a record changed over time. The VERSION variable also exposes commit metadata — the audit, the committed time, and the version uid.

Terminology in queries

Value filters can be backed by terminology in three ways:

TERMINOLOGY('expand', …) as (or inside) a matches operand expands a value set so a coded field matches any code in it, rather than listing codes by hand;
TERMINOLOGY('validate'|'subsumes', …) = true as a boolean condition evaluates a code-membership or subsumption test once per query;
a terminology URI operand — matches { terminology://… } — expands the set the URI identifies.

These require a terminology source; if external terminology is not configured, the in-process openEHR bundle is used. See Terminology servers for wiring an external FHIR terminology server.

Pagination and limits

Combine LIMIT/OFFSET in the AQL with the fetch/offset request parameters to page through large result sets. When you ask for more than the server will return in one response, page with offset.

Operators can also cap how long any single query may run: set FERROEHR__QUERY__TIMEOUT_MS to a per-query execution budget in milliseconds (unset or 0 = no per-query cap). A query that exceeds the budget returns 408 Request Timeout — narrow the query (add archetype constraints, an ehr_id scope, or a WHERE filter) rather than retrying unchanged.

Tip

The more specific your FROM/CONTAINS (name the archetype, scope by ehr_id), the faster the query: those constraints map to indexed columns, while broad “everything that contains anything” queries do the most work.

What is supported

FerroEHR implements the core AQL 1.1 envelope and rejects out-of-envelope constructs with an explicit, typed error rather than silently returning wrong results. Supported today includes:

SELECT of paths, literals, aliases, DISTINCT, and the aggregates COUNT (including COUNT(DISTINCT)), MIN, MAX, SUM, AVG. MIN and MAX order their operand by type — a quantity by its openEHR magnitude, a date/time chronologically, text lexically — so they work over non-numeric leaves, not just numbers;
FROM over EHR, VERSION (LATEST_VERSION / ALL_VERSIONS), and RM classes with archetype and name predicates;
CONTAINS trees with AND, OR, and NOT CONTAINS;
WHERE comparisons on typed leaves (with openEHR magnitude ordering for quantities), EXISTS, LIKE, MATCHES value lists, and range predicates;
ORDER BY typed leaves, LIMIT/OFFSET, named query parameters, and the ehr_id, offset, and fetch request parameters;
the single-row functions: LENGTH, SUBSTRING, POSITION, the string CONTAINS, CONCAT/CONCAT_WS, ABS, MOD, CEIL, FLOOR, ROUND, and CURRENT_DATE/CURRENT_TIME/CURRENT_DATE_TIME/NOW/ CURRENT_TIMEZONE;
terminology-backed TERMINOLOGY() operands and terminology-URI matches operands (see above).

Semantic analysis is strict: duplicate FROM variable names, LIMIT 0, negative OFFSET, wrong function arity, and SUM/AVG over non-numeric paths are all rejected with a clear message. Variable names are case-insensitive, as the specification requires.

Where a construct is outside the supported set, the server returns a clear error identifying it — you never get a silently incorrect answer.

Keyboard shortcuts

FerroEHR Documentation