> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://docs.emtelligent.com/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://docs.emtelligent.com/_mcp/server.

# Medication Summary

## Synopsis

This query retrieves all medication relations extracted by the NLP API. For each relation, it shows the medication's concept description, and the annotated terms for all arguments of the medication relations: dose, frequency, mode, quantity, indication, duration, route, necessity, modifiers, and associated date(s).

Note, however, that this query does not return medication mentions which do not specify at least one of the components of a medication prescription. To find mentions of medications, you would perform a concept search using the RxNorm ontology.

## Processing Requirements

This query assumes that at least the following `emtellipro-db-client` processing options were enabled:

* `--feature medication-relations, snomed-ontology, rxnorm-ontology`
* `--store-sections-and-sentences`

For this query, Clinical notes were processed with the following document type:

* `--category Clinical`
* `--subcategory generic` (or more specific report types such as Discharge Summary, SOAP Note, CCD as applicable)

## Clinical Context

The NLP API extracts medication posology information such as dose, frequency, quantity, mode, etc related to medication mentions and reports this information as structured data in the NLP API output. Because there are a large number of posology fields, and because of the complexity of how clinicians can record medication posology (e.g. `patient was started on lasix 75 mg po bid then tapered to 25 mg po od`), retrieving this information from the Output Database is necessarily complex.

The query below uses string aggregation functions and multiple LEFT JOINs to combine the multiple possible entries from each medication relation field (e.g. `75 mg` and `25 mg` in the example above) into a list. This query can be very useful for obtaining a very detailed summary of medication mentions in clinical notes.

## SQL query

#### PostgreSQL

```sql
WITH prescription AS (
    SELECT DISTINCT(mr.id)                                                                     AS mr_id,
                fe.id                                                                          AS fe_id,
                e.document_id                                                                  AS doc_id,
                first_value(c.description) OVER (PARTITION BY mr.id ORDER BY c.ontology DESC ) AS description,
                first_value(c.ontology) OVER (PARTITION BY mr.id ORDER BY c.ontology DESC)     AS ontology,
                first_value(c.concept_id) OVER (PARTITION BY mr.id ORDER BY c.ontology DESC)   AS concept_id
    FROM medicationrelation mr
            -- get drug name
            JOIN entity e ON mr.drug_entity_id = e.id
            JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
            JOIN foundentityconcept fec on fe.id = fec.found_entity_id
        -- concept search using all available ontologies (not selectively filtering for specific one)
            JOIN concept c on fec.concept_id = c.concept_id and fec.concept_ontology = c.ontology AND
                               c.ontology IN ('snomed', 'rxnorm') -- use medications recognized by either ontologies
    ),
    indication AS (
        SELECT DISTINCT(mri.medication_id)                                      AS id,
                        string_agg(fe.text, ', ')                              AS indications
        FROM medicationrelationindication mri
                JOIN entity e ON mri.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mri.medication_id
    ),
    dose AS (
        SELECT DISTINCT(mrd.medication_id)                                      AS id,
                        string_agg(fe.text, ', ')                              AS dosages
        FROM medicationrelationdosage mrd
                JOIN entity e ON mrd.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrd.medication_id
    ),
    frequency AS (
        SELECT DISTINCT(mrf.medication_id)                                      AS id,
                        string_agg(fe.text, ', ')                              AS frequencies
        FROM medicationrelationfrequency mrf
                JOIN entity e ON mrf.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrf.medication_id
    ),
    quantity AS (
        SELECT DISTINCT(mrq.medication_id)                                        AS id,
                        string_agg(fe.text, ', ')                                AS quantities
        FROM medicationrelationquantity mrq
                JOIN entity e ON mrq.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrq.medication_id
    ),
    mode AS (
        SELECT DISTINCT(mrm.medication_id)                                       AS id,
                        string_agg(fe.text, ', ')                               AS modes
        FROM medicationrelationmode mrm
                JOIN entity e ON mrm.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrm.medication_id
    ),
    duration AS (
        SELECT DISTINCT(mrdur.medication_id)                                       AS id,
                        string_agg(fe.text, ', ')                                 AS durations
        FROM medicationrelationduration mrdur
                JOIN entity e ON mrdur.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrdur.medication_id
    ),
    route AS (
        SELECT DISTINCT(mrr.medication_id)                                              AS id,
                        string_agg(fe.text, ', ')                                       AS routes
        FROM medicationrelationroute mrr
                JOIN entity e ON mrr.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrr.medication_id
    ),
    modifier AS (
        SELECT DISTINCT(mrmod.medication_id)                                            AS id,
                        string_agg(fe.text, ', ')                                      AS modifiers
        FROM medicationrelationmodifier mrmod
                JOIN entity e ON mrmod.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrmod.medication_id
    ),
    necessity AS (
        SELECT DISTINCT(mrn.medication_id)                                              AS id,
                        string_agg(fe.text, ', ')                                      AS as_necessary
        FROM medicationrelationnecessity mrn
                JOIN entity e ON mrn.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrn.medication_id
    ),
    date_time AS (
        SELECT DISTINCT(mrdt.medication_id)                                              AS id,
                        string_agg(fe.text, ', ')                                      AS dates
        FROM medicationrelationdatetime mrdt
                JOIN entity e ON mrdt.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrdt.medication_id
    )
SELECT prescription.mr_id,
    -- enable if documentmetadata is populated
    --        dm.chartdate,
    --        dm.subject_id,
    prescription.description,
    prescription.ontology,
    prescription.concept_id,
    indication.indications,
    dose.dosages,
    frequency.frequencies,
    mode.modes,
    quantity.quantities,
    route.routes,
    modifier.modifiers,
    duration.durations,
    necessity.as_necessary,
    date_time.dates,
    sl.text as sentence
FROM prescription
        LEFT JOIN indication ON prescription.mr_id = indication.id
        LEFT JOIN dose ON prescription.mr_id = dose.id
        LEFT JOIN frequency ON prescription.mr_id = frequency.id
        LEFT JOIN mode ON prescription.mr_id = mode.id
        LEFT JOIN quantity ON prescription.mr_id = quantity.id
        LEFT JOIN route ON prescription.mr_id = route.id
        LEFT JOIN modifier ON prescription.mr_id = modifier.id
        LEFT JOIN duration ON prescription.mr_id = duration.id
        LEFT JOIN necessity ON prescription.mr_id = necessity.id
        LEFT JOIN date_time ON prescription.mr_id = date_time.id
    -- Get sentence text
        JOIN foundentitylocation fel ON fel.found_entity_id = prescription.fe_id
        JOIN location l on fel.location_id = l.id AND l.type_ = 'sentence'
        JOIN sentencelocation sl on l.location_id = sl.id
        JOIN document d ON prescription.doc_id = d.id
        -- enable if documentmetadata is populated
        --     JOIN documentmetadata dm ON d.id = dm.document_id
WHERE dose.dosages IS NOT NULL AND frequency.frequencies IS NOT NULL AND 
    indication.indications IS NOT NULL
ORDER BY d.id, mr_id ASC;
```

#### MS SQL Server

```sql
WITH prescription AS (
    SELECT DISTINCT(mr.id)                                                                     AS mr_id,
                fe.id                                                                          AS fe_id,
                e.document_id                                                                  AS doc_id,
                FIRST_VALUE(c.description) OVER (PARTITION BY mr.id ORDER BY c.ontology DESC ) AS description,
                FIRST_VALUE(c.ontology) OVER (PARTITION BY mr.id ORDER BY c.ontology DESC)     AS ontology,
                FIRST_VALUE(c.concept_id) OVER (PARTITION BY mr.id ORDER BY c.ontology DESC)   AS concept_id
    FROM medicationrelation mr
            -- get drug name
            JOIN entity e ON mr.drug_entity_id = e.id
            JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
            JOIN foundentityconcept fec on fe.id = fec.found_entity_id
        -- concept search using all available ontologies (not selectively filtering for specific one)
            JOIN concept c on fec.concept_id = c.concept_id and fec.concept_ontology = c.ontology AND
                              c.ontology IN ('snomed', 'rxnorm') -- use medications recognized by either ontologies
    ),
    indication AS (
        SELECT DISTINCT(mri.medication_id) AS id,
                STRING_AGG(fe.text, ', ') AS indications
        FROM medicationrelationindication mri
                JOIN entity e ON mri.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mri.medication_id
    ),
    dose AS (
        SELECT DISTINCT(mrd.medication_id) AS id,
                STRING_AGG(fe.text, ', ') AS dosages
        FROM medicationrelationdosage mrd
                JOIN entity e ON mrd.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrd.medication_id
    ),
    frequency AS (
        SELECT DISTINCT(mrf.medication_id)                                      AS id,
                        string_agg(fe.text, ', ')                              AS frequencies
        FROM medicationrelationfrequency mrf
                JOIN entity e ON mrf.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrf.medication_id
    ),
    quantity AS (
        SELECT DISTINCT(mrq.medication_id) AS id,
                STRING_AGG(fe.text, ', ') AS quantities
        FROM medicationrelationquantity mrq
                JOIN entity e ON mrq.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrq.medication_id
    ),
    mode AS (
        SELECT DISTINCT(mrm.medication_id) AS id,
                STRING_AGG(fe.text, ', ') AS modes
        FROM medicationrelationmode mrm
                JOIN entity e ON mrm.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrm.medication_id
    ),
    duration AS (
        SELECT DISTINCT(mrdur.medication_id) AS id,
                STRING_AGG(fe.text, ', ') AS durations
        FROM medicationrelationduration mrdur
                JOIN entity e ON mrdur.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrdur.medication_id
    ),
    route AS (
        SELECT DISTINCT(mrr.medication_id) AS id,
                STRING_AGG (fe.text, ', ') AS routes
        FROM medicationrelationroute mrr
                JOIN entity e ON mrr.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrr.medication_id
    ),
    modifier AS (
        SELECT DISTINCT(mrmod.medication_id) AS id,
                STRING_AGG(fe.text, ', ')   AS modifiers
        FROM medicationrelationmodifier mrmod
                JOIN entity e ON mrmod.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrmod.medication_id
    ),
    necessity AS (
        SELECT DISTINCT(mrn.medication_id) AS id,
                STRING_AGG(fe.text, ', ') AS as_necessary
        FROM medicationrelationnecessity mrn
                JOIN entity e ON mrn.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrn.medication_id
    ),
    date_time AS (
    SELECT DISTINCT(mrdt.medication_id) AS id,
                STRING_AGG(fe.text, ', ') AS dates
    FROM medicationrelationdatetime mrdt
            JOIN entity e ON mrdt.entity_id = e.id
    GROUP BY mrdt.medication_id
    )
SELECT prescription.mr_id,
    -- enable if documentmetadata is populated
    --        dm.chartdate,
    --        dm.subject_id,
    prescription.description,
    prescription.ontology,
    prescription.concept_id,
    indication.indications,
    dose.dosages,
    frequency.frequencies,
    mode.modes,
    quantity.quantities,
    route.routes,
    modifier.modifiers,
    duration.durations,
    necessity.as_necessary,
    date_time.dates,
    sl.text as sentence
FROM prescription
        LEFT JOIN indication ON prescription.mr_id = indication.id
        LEFT JOIN dose ON prescription.mr_id = dose.id
        LEFT JOIN frequency ON prescription.mr_id = frequency.id
        LEFT JOIN mode ON prescription.mr_id = mode.id
        LEFT JOIN quantity ON prescription.mr_id = quantity.id
        LEFT JOIN route ON prescription.mr_id = route.id
        LEFT JOIN modifier ON prescription.mr_id = modifier.id
        LEFT JOIN duration ON prescription.mr_id = duration.id
        LEFT JOIN necessity ON prescription.mr_id = necessity.id
        LEFT JOIN date_time ON prescription.mr_id = date_time.id
    -- Get sentence text
        JOIN foundentitylocation fel ON fel.found_entity_id = prescription.fe_id
        JOIN location l on fel.location_id = l.id AND l.type_ = 'sentence'
        JOIN sentencelocation sl on l.location_id = sl.id
        JOIN document d ON prescription.doc_id = d.id
        -- enable if documentmetadata is populated
        --     JOIN documentmetadata dm ON d.id = dm.document_id
WHERE dose.dosages IS NOT NULL AND frequency.frequencies IS NOT NULL AND 
    indication.indications IS NOT NULL
ORDER BY d.id, mr_id ASC;
```

#### Snowflake

```sql
WITH distinct_concepts AS (
    SELECT distinct * FROM concept c
    ),
    prescription AS (
    SELECT DISTINCT(mr.id)                                                                        AS mr_id,
                fe.id                                                                          AS fe_id,
                e.document_id                                                                  AS doc_id,
                first_value(c.description) OVER (PARTITION BY mr.id ORDER BY c.ontology DESC ) AS description,
                first_value(c.ontology) OVER (PARTITION BY mr.id ORDER BY c.ontology DESC)     AS ontology,
                first_value(c.concept_id) OVER (PARTITION BY mr.id ORDER BY c.ontology DESC)   AS concept_id
    FROM medicationrelation mr
            -- get drug name
            JOIN entity e on mr.drug_entity_id = e.id
            JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
            JOIN foundentityconcept fec on fe.id = fec.found_entity_id
            -- concept search using all available ontologies (not selectively filtering for specific one)
            JOIN distinct_concepts c on fec.concept_id = c.concept_id and fec.concept_ontology = c.ontology AND
                            c.ontology IN ('snomed', 'rxnorm') -- use medications recognized by either ontologies
    ),
    indication AS (
        SELECT DISTINCT(mri.medication_id)       AS id,
                        listagg(fe.text, ', ') AS indications
        FROM medicationrelationindication mri
                JOIN entity e ON mri.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mri.medication_id
    ),
    dose AS (
        SELECT DISTINCT(mrd.medication_id)         AS id,
                        listagg(fe.text, ', ') AS dosages
        FROM medicationrelationdosage mrd
                JOIN entity e ON mrd.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrd.medication_id
    ),
    frequency AS (
        SELECT DISTINCT(mrf.medication_id)                                      AS id,
                        listagg(fe.text, ', ')                              AS frequencies
        FROM medicationrelationfrequency mrf
                JOIN entity e ON mrf.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrf.medication_id
    ),
    mode AS (
        SELECT DISTINCT(mrm.medication_id)         AS id,
                        listagg(fe.text, ', ')   AS modes
        FROM medicationrelationmode mrm
                JOIN entity e ON mrm.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrm.medication_id
    ),
    quantity AS (
        SELECT DISTINCT(mrq.medication_id)         AS id,
                        listagg(fe.text, ', ')   AS quantities
        FROM medicationrelationquantity mrq
                JOIN entity e ON mrq.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrq.medication_id
    ),
    duration AS (
        SELECT DISTINCT(mrdur.medication_id)        AS id,
                        listagg(fe.text, ', ')    AS durations
        FROM medicationrelationduration mrdur
                JOIN entity e ON mrdur.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrdur.medication_id
    ),
    route AS (
        SELECT DISTINCT(mrr.medication_id)          AS id,
                        listagg(fe.text, ', ')    AS routes
        FROM medicationrelationroute mrr
                JOIN entity e ON mrr.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrr.medication_id
    ),
    modifier AS (
        SELECT DISTINCT(mrmod.medication_id)        AS id,
                        listagg(fe.text, ', ')    AS modifiers
        FROM medicationrelationmodifier mrmod
                JOIN entity e ON mrmod.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrmod.medication_id
    ),
    necessity AS (
        SELECT DISTINCT(mrn.medication_id)          AS id,
                        listagg(fe.text, ', ')    AS as_necessary
        FROM medicationrelationnecessity mrn
                JOIN entity e ON mrn.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrn.medication_id
    ),
    date_time AS (
    SELECT DISTINCT(mrdt.medication_id)          AS id,
                listagg(fe.text, ', ')    AS dates
    FROM medicationrelationdatetime mrdt
            JOIN entity e ON mrdt.entity_id = e.id
            JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
    GROUP BY mrdt.medication_id
    )
SELECT prescription.mr_id,
    -- enable if documentmetadata is populated
    --        dm.chartdate,
    --        dm.subject_id,
    prescription.description,
    prescription.ontology,
    prescription.concept_id,
    indication.indications,
    dose.dosages,
    frequency.frequencies,
    mode.modes,
    quantity.quantities,
    route.routes,
    modifier.modifiers,
    duration.durations,
    necessity.as_necessary,
    date_time.dates,
    sl.text as sentence
FROM prescription
        LEFT JOIN indication ON prescription.mr_id = indication.id
        LEFT JOIN dose ON prescription.mr_id = dose.id
        LEFT JOIN frequency ON prescription.mr_id = frequency.id
        LEFT JOIN mode ON prescription.mr_id = mode.id
        LEFT JOIN quantity ON prescription.mr_id = quantity.id
        LEFT JOIN route ON prescription.mr_id = route.id
        LEFT JOIN modifier ON prescription.mr_id = modifier.id
        LEFT JOIN duration ON prescription.mr_id = duration.id
        LEFT JOIN necessity ON prescription.mr_id = necessity.id
        LEFT JOIN date_time ON prescription.mr_id = date_time.id
    -- Get sentence text
        JOIN foundentitylocation fel ON fel.found_entity_id = prescription.fe_id
        JOIN location l on fel.location_id = l.id AND l.type_ = 'sentence'
        JOIN sentencelocation sl on l.location_id = sl.id
        JOIN document d ON prescription.doc_id = d.id
        -- enable if documentmetadata is populated
        --     JOIN documentmetadata dm ON d.id = dm.document_id
WHERE dose.dosages IS NOT NULL AND frequency.frequencies IS NOT NULL AND
    indication.indications IS NOT NULL
ORDER BY d.id, mr_id ASC;
```

| mr_id | description                                                      | ontology | concept_id | indications         | dosages  | frequencies | modes  | quantities | routes   | modifiers | durations | as_necessary | dates | sentence                                                      |
| ----- | ---------------------------------------------------------------- | -------- | ---------- | ------------------- | -------- | ----------- | ------ | ---------- | -------- | --------- | --------- | ------------ | ----- | ------------------------------------------------------------- |
| 18    | Zopiclone (substance)                                            | snomed   | 395929002  | sleep               | 7.5mg    | QHS         | —      | —          | PO       | —         | —         | PRN          | —     | Zopiclone 7.5mg PO QHS PRN sleep                              |
| 30    | Zopiclone (substance)                                            | snomed   | 395929002  | sleep               | 7.5mg    | QHS         | —      | —          | PO       | —         | —         | PRN          | —     | Zopiclone 7.5mg PO QHS PRN sleep                              |
| 93    | Salbutamol (substance)                                           | snomed   | 372897005  | shortness of breath | 5mg      | q4h, q1h    | NEB    | —          | —        | —         | —         | PRN          | —     | Salbutamol 5mg via NEB q4h and q1h PRN shortness of breath    |
| 98    | Dimenhydrinate (substance)                                       | snomed   | 387469006  | nausea              | 25-50mg  | q4-6h       | —      | —          | PO/IV/SC | —         | —         | PRN          | —     | Gravol 25-50mg PO/IV/SC q4-6h PRN nausea                      |
| 117   | Product containing oxycodone and paracetamol (medicinal product) | snomed   | 772959003  | pain                | 5/325 mg | q.4-6h.     | tablet | one        | p.o.     | —         | —         | as needed    | —     | Percocet 5/325 mg one tablet p.o. q.4-6h. as needed for pain. |

## Related links

> * [Entity-level Tables](/nlp/database/schema/core/entity-tables)
> * [Medication Relations](/nlp/database/schema/relations/medication)