Medication Summary | emtelligent Documentation

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

MS SQL Server

Snowflake

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;

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.

Entity-level Tables

Medication Relations