# AQL - Querying observations of various types in compositions

**Category:** [AQL](https://discourse.openehr.org/c/aql/43)
**Created:** 2024-09-02 09:31 UTC
**Views:** 84
**Replies:** 4
**URL:** https://discourse.openehr.org/t/aql-querying-observations-of-various-types-in-compositions/5645

---

## Post #1 by @linforest

When trying to return observations of various types in compositions, the following AQL query was used:

```
SELECT e/ehr_id/value, 
	c/archetype_details/template_id/value, 
	c/uid/value,
	obs/data[at0002]/events[at0003]/data[at0001]/items/name/value,
	obs/data[at0002]/events[at0003]/data[at0001]/items/value
FROM EHR e CONTAINS COMPOSITION c 
	CONTAINS OBSERVATION obs [openEHR-EHR-OBSERVATION.pulse.v1]
```

I got all the results as expected in such a query.

Snippet of the response:
```
...
[
      "80a71040-3d37-42f3-a86a-ce5405138ce0",
      "Linkoping_University_Physical_Activity",
      "8aad14de-4af5-4559-8979-004877eb6a9c::local.ehrbase.org::1",
      "Heart beat presence",
      {
        "_type": "DV_CODED_TEXT",
        "defining_code": {
          "_type": "CODE_PHRASE",
          "code_string": "at1024",
          "terminology_id": {
            "_type": "TERMINOLOGY_ID",
            "value": "local"
          }
        },
        "value": "Present"
      }
    ],
    [
      "80a71040-3d37-42f3-a86a-ce5405138ce0",
      "Linkoping_University_Physical_Activity",
      "8aad14de-4af5-4559-8979-004877eb6a9c::local.ehrbase.org::1",
      "Pulse Rate",
      {
        "_type": "DV_QUANTITY",
        "magnitude": 21,
        "units": "/min"
      }
    ],
...
```
My question is:

How to get the specific "results" depending the data type of the observation:
* If _type =  "DV_CODED_TEXT" OR "DV_TEXT" ...,  then SELECT the value such as "Present" for "Heart beat presence";
* If _type = "DV_QUANTITY",  then SELECT the "magnitude" such as 21 and "units" such as "/min"...

The following AQL query returns the results of _type "DV_CODED_TEXT" OR "DV_TEXT" ... as expected but returns  the results of _type "DV_QUANTITY" as **null**

```
SELECT e/ehr_id/value, 
	c/archetype_details/template_id/value, 
	c/uid/value,
	obs/data[at0002]/events[at0003]/data[at0001]/items/name/value,
	obs/data[at0002]/events[at0003]/data[at0001]/items/value/value
FROM EHR e CONTAINS COMPOSITION c 
	CONTAINS OBSERVATION obs [openEHR-EHR-OBSERVATION.pulse.v1]
```

The following AQL query applies to  the results of _type "DV_QUANTITY" ONLY:

```
SELECT e/ehr_id/value, 
	c/archetype_details/template_id/value, 
	c/uid/value,
	obs/data[at0002]/events[at0003]/data[at0001]/items[at0004]/name/value,
	obs/data[at0002]/events[at0003]/data[at0001]/items[at0004]/value/magnitude,
	obs/data[at0002]/events[at0003]/data[at0001]/items[at0004]/value/units
FROM EHR e CONTAINS COMPOSITION c 
	CONTAINS OBSERVATION obs [openEHR-EHR-OBSERVATION.pulse.v1]
```

So, how to query observations of various types in compositions in a single AQL query?

Thanks in advance.

---

## Post #2 by @sebastian.iancu

I don't know if this feature is supported.

It seems to me that you want the AQL-engine to do also the job of a client application. If AQL would be able to gather different type of data on the one column, what would be the use-case to consume such a data? is it a generic form/viewer? In terms of "comparing apples with pears", this is like getting a fruit-basket with surprise-fruits...
From my perspective it would be cleaner if you would select the objects (i.e. `obs/data[at0002]/events[at0003]/data[at0001]/items/value`) and then process/handle them in your client-app.

---

## Post #3 by @birger.haarbrandt

I would advise the same. Fetch the element and then process according to type information on the client-side.

---

## Post #4 by @stefanspiska

For a generic viewer you would need to select the whole Element and then build a representation from the json using the  _type attribute.

For a specific use case where you always select  a specific template or archetypes you would generate a AQl query from the template / archetype  knowing the datatype each paths leads to.


See for example https://github.com/medblocks/medblocks-ui 
which uses the flat format  to automatically build input forms from a template. 

similar it would be possible to develop tools which generate and interpret AQL queries from the template.

Such tooling is often included in full enterprise solutions like the vitagroup https://hip.vitagroup.ag/en/

---

## Post #5 by @linforest

Thanks all!  Now I have a much deeper understanding of this.

---


**Canonical:** https://discourse.openehr.org/t/aql-querying-observations-of-various-types-in-compositions/5645
**Original content:** https://discourse.openehr.org/t/aql-querying-observations-of-various-types-in-compositions/5645