> 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 Relations In this section, we explain how structured data for medications relations is represented in the NLP API JSON output and in the Output Database schema. We also provide sample queries to illustrate the key aspects of the data schema. After reading this page, we hope you will: 1. Understand what medication relations are. 2. Understand what attributes are extracted. 3. Know how to find medications and their attributes. The content in this section builds on the concepts and data models explained in earlier sections (see [Entity-level Tables](/nlp/database/schema/core/entity-tables), and [Document-level Tables](/nlp/database/schema/core/document-tables)). ## What are Medication Relations? Patient records often contain information about medications. In addition to the drug name, reports may also note the medication's dispensing information and patient instructions. When the NLP API encounters a reference to a medication it recognizes, it is able to extract many of the prescription-related details as attributes. In the NLP API, a medication relation comprises of a drug name and its associated information. Consider the following statement: ```text Percocet 5/325 one to two tablets by mouth every three hours for one month as needed for pain started on 11-11-2020. ``` From the above, the NLP API extracts a medication relation for the recognized drug name, *Percocet* as follows: ![Measurement relation required and optional arguments](/_fern-files/emtelligent-prod.docs.buildwithfern.com/adf61505a988267f98b7072784f1bcc3f1cb680579c947422640242cf5977cb7/assets/nlp/medicationrelation_anno.png) As shown above, the NLP API extracts eight different attributes related to medications. The following describes each attribute and provides some examples of terms that are recognized. Please note that the examples of recognized terms is not exhaustive. The medication relation attributes extracted by the NLP API are: * **Drug**: A substance that has been prescribed to, or taken by the patient. To identify medication relations, the NLP API uses medications defined in the RxNorm and Medcin ontologies. However, because it also performs post-coordination of concepts across ontologies, the equivalent drug concept may also be returned for the SNOMED ontology when it also exists in SNOMED CT. * **Indication**: Reason given for prescription. * **Dosage**: Size or level of a dose of a particular drug, which can be unitless. Examples of dose information are: `10/100`, `81 mG`, `10mL`, `sliding scale`, `Twenty (20) units`, `20 milliequivalents`, `20mEq`, `81'`, `1 g`, `2%`, `2ml/ml`, `5 mcg/kg/min`. * **Route**: The path by which the drug is introduced into the body. Examples are: `IV`, `by mouth`, `p.o.`, `subcutaneous`, `drip`, `G-tube`, `Dobhoff Tube`, `sublingual`, `topically`, `sl`, `intramuscular`, `buccal`, `sc`, `hsq`, `NG`, `inhaled`. * **Quantity**: The amount of drug in prescribed dosage to be taken by the patient, often without units and preceding a route. Examples are: `Two (2)`, `1`, `one to two`. * **Mode**: The form the drug comes in. Examples are: `caps`, `cream`, `capsules`, `gtt`, `inhaler`, `MDI`, `puffs`, `solution`, `tablets`, `sustained release`, `ointment`, `suspension`. * **Frequency**: How often the drug is to be taken by the patient. Examples are: `daily`, `tid`, `b.i.d.`, `once daily with meals`, `twice a day for the next 8 days`, `q4H`, `q.24 hours`, `q. four to six hours`. * **Necessity**: If the drug is required or can be taken if needed. Examples are: `PRN`, `p.r.n.`, `as needed`, `p.r.n. as needed`. * **Modifier**: If the statement also contains a modification to the prescribed regimen, it will be identified as a modifier. Examples are: `discontinued`, `stopped`, `taper`. * **Duration**: How long the person is exposed to the drug. For example in the sentence `Lasix 20 mg qd for 5 days.`, **5 days** is extracted as the duration. Duration in this context is not to be confused with "duration of action", the pharmacokinetics of a drug. * **Date_time**: This argument extracts *any* concrete, specific date and/or time expression that may be present. This may be the date *prescribed*, date *started*, date *stopped*, date *ordered* or the *expiration* date of a medication. Other examples of sentences where dates are extracted include: ```text ibuprofen 400 mg tablet prn 01/24/1900 lisinopril 10 mg oral tablet, = 1 cap(s), PO, Once Daily, 10 cap(s), 09/22/2008, 10/02/2008, Ordered ``` > **Warning** > > Medication relations are typically used to extract posology information sections of the reports that provide information about patient's medications or medications administered. You can limit the results returned by filtering on the **foundentity** table, `section_name` attribute for a given drug mention. ## JSON Data Model The JSON below shows the NLP API's JSON result output for a medication relation extracted from this statement: ```text Percocet 5/325 one to two tablets by mouth every three hours for one month as needed for pain started on 11-11-2020. ``` ```json { "relations": { "medications": [ { "label": "RMD0", "attributes": { "confidence": null }, "args": { "drug": { "ref": "E2", "text": [ "Percocet" ] }, "dosage": [ { "ref": "E10", "text": [ "5/325" ] } ], "route": [ { "ref": "E12", "text": [ "by mouth" ] } ], "quantity": [ { "ref": "E11", "text": [ "one to two" ] } ], "mode": [ { "ref": "E5", "text": [ "tablets" ] } ], "frequency": [ { "ref": "E7", "text": [ "every three hours" ] } ], "duration": [ { "ref": "E13", "text": [ "one month" ] } ], "necessity": [ { "ref": "E14", "text": [ "as needed" ] } ], "modifier": [ { "ref": "E15", "text": [ "started" ] } ], "indication": [ { "ref": "E1", "text": [ "pain" ] } ], "date_time": [ { "ref": "E0", "text": [ "11-11-2020" ] } ] }, "concept_links": [] } ] } } ``` From the JSON output above, we see that: 1. The `medications` array is an element of the `relations` object. Each element of this array is a single object representing a medication relation instance. 2. A medication relation instance has several named arguments: the `drug` and the recognized attributes: `indication`, `dosage`, `frequency`, `quantity`, `route`, `mode`, `necessity`, `duration`, `modifier` and `date_time`. 3. Note that the `drug` argument is **required**. Each medication relation corresponds to exactly one drug. 4. All attributes are arrays of entity references. For example if 2 dates are present, both entities are extracted and returned as an array. 5. Other attributes are optional. For example, if the report does not indicate how frequently a medication should be used, the `frequency` attribute is an empty array. ## Medication Relation Tables In the Output Database schema, medication relations are stored in the **medicationrelation** table. Its attributes are stored in correspondingly-named tables as shown in the following ER diagram. ![Entity-Relation diagram showing medicationrelation and its associated tables.](/_fern-files/emtelligent-prod.docs.buildwithfern.com/71f9bbf7ac500bffaa3fff104c38afab350965874deb0bf96fe9017a07d79bf5/assets/nlp/medicationrelation_schema.png) A few key points should be noted about this schema: 1. Each medication relation relates to **exactly one** drug. Each drug mention is represented by an **entity** in the Output Database schema, and has a corresponding row in the **entity** and **foundentity** tables respectively. 2. In addition to the entity attributes stored in the **foundentity** table, such as **section_name**, many other attributes about an entity can be found from performing SQL joins with entity-related tables. For more information see [Entity-level Tables](/nlp/database/schema/core/entity-tables). 3. The attributes extracted for medication relations are stored in tables with corresponding names. For example, `dosage` is stored in the **medicationrelationdosage** table and `frequency` is stored in the **medicationrelationfrequency** table. Information about a medication relation's attributes can be retrieved by performing an inner join on the primary key column of the **medicationrelation** table and the **medication_id** foreign key of the corresponding attribute table. 4. Notice that a medication relation may have **zero or more** instances of an attribute. This may be important to remember when you want to write a query that extracts all the extracted attributes. An additional aggregation step will be required to aggregate the terms or concepts for each attribute type. ## Sample Queries Medication relations are stored in a highly normalized way, where each attribute is a separate table, and subsequent joins with entity-related tables is required to retrieve all the information required. Performing a large number of table joins for a large dataset may be impractical for low latency data retrieval use cases. The following sample queries are provided for instructional purposes only and may only be appropriate for initial data exploration and some analytic use cases. In this section, we present SQL queries that illustrate how to retrieve information about medication relations for initial data exploration. The SQL queries will be discussed in order as follows: * [Query: Show annotated text for recognized drug names](#query-show-annotated-text-for-recognized-drug-names) * [Query: List RxNorm concepts for medications and annotated drug terms](#query-list-rxnorm-concepts-for-medications-and-annotated-drug-terms) * [Query: Retrieve dosages for a specific medication](#query-retrieve-dosages-for-a-specific-medication) ### Query: Show annotated text for recognized drug names This query retrieves and counts the annotated terms that NLP API has identified as drug names. To find information about measurement relations we start at the **medicationrelations** table. 1. In the **medicationrelation** table, we look up the **drug_entity_id** column. 2. Use the **drug_entity_id** to perform an inner join with the **entity** table. 3. Perform an inner join with the **foundentity** table to retrieve the annotated text for the drug entity using appropriate columns as join conditions as shown in the query below. See ER diagram in [Medication Relation Tables](#medication-relation-tables) section above. ```sql SELECT drug_fe.text, count(*) FROM medicationrelation JOIN entity drug_e ON medicationrelation.drug_entity_id = drug_e.id -- Important: Use the drug_entity_id column to join with entity table JOIN foundentity drug_fe ON drug_e.entity_id = drug_fe.id AND drug_e.type_ = 'found' GROUP BY drug_fe.text LIMIT 5; ``` The annotated drug term is not useful for data analysis because of the high-degree of variation in drug names. In the next query, we show how to list medications and their frequencies by their associated concepts in an established ontology such as **RxNorm**. | text | count | | ------------- | ----- | | Acetaminophen | 3 | | albuterol | 1 | | Aleve | 1 | | Allopurinol | 2 | | amiodarone | 2 | ### Query: List RxNorm concepts for medications and annotated drug terms In this query we want to generate a comma-delimited list of drug terms that map to a specific RxNorm concept for a recognized medication. This involves aggregating multiple instances of annotated drug terms using a window function in a subquery or CTE. First we create an in-memory result set using a CTE (common table expression). In the CTE we perform the following steps: 1. Starting with the **medicationrelation** table, perform a series of inner joins to extract the annotated drug term from the **foundentity** table. 2. Then, perform an inner join with the **foundentityconcept** and **concept** table to look up the RxNorm **concept id** and **concept description** from the **concept** table. 3. Use the **GROUP BY** clause to get a count of drug terms extracted. 4. Aggregate all drug terms that relate to the same RxNorm concept into a comma-delimited string. We do this using the `string_agg` window function, partitioning on the **concept_id** values retrieved in step two. On the output of the CTE, we perform a final aggregation pass to add up the counted terms in order to find the total number of occurrencies by RxNorm concept. ```sql SELECT * FROM (WITH concept_term_mappings AS (SELECT drug_fec.concept_id, drug_c.description AS concept, drug_fe.text AS annotated_text, count(*) AS count_by_term, string_agg(drug_fe.text, ', ') OVER (PARTITION BY drug_fec.concept_id) AS terms_list FROM medicationrelation -- Joins to retrieve the drug term from foundentity JOIN entity drug_e ON medicationrelation.drug_entity_id = drug_e.id AND drug_e.type_ = 'found' JOIN foundentity drug_fe ON drug_e.entity_id = drug_fe.id -- Joins to retrieve concept_id and concept description from concept table JOIN foundentityconcept drug_fec ON drug_fe.id = drug_fec.found_entity_id JOIN concept drug_c ON drug_fec.concept_id = drug_c.concept_id AND drug_fec.concept_ontology = drug_c.ontology WHERE drug_c.ontology = 'rxnorm'-- You can also use 'medcin' GROUP BY drug_fec.concept_id, drug_c.description, drug_fe.text) SELECT concept_id, concept, terms_list, sum(count_by_term) OVER (PARTITION BY concept_id) AS count_by_concept FROM concept_term_mappings) AS results GROUP BY results.concept_id, results.concept, results.terms_list, results.count_by_concept ORDER BY results.count_by_concept DESC LIMIT 5; ``` | concept_id | concept | terms_list | count_by_concept | | ---------- | ------------------------------------------------- | ------------------------ | ---------------- | | 1191 | aspirin (Pharmacologic Substance) | ASA, Aspirin | 10 | | 253182 | insulin, regular, human (Pharmacologic Substance) | insulin, Regular insulin | 9 | | 67108 | enoxaparin (Pharmacologic Substance) | Enoxaparin | 9 | | 6918 | metoprolol (Pharmacologic Substance) | metoprolol, Metoprolol | 9 | | 202433 | Tylenol (Pharmacologic Substance) | tylenol, Tylenol | 8 | ### Query: Retrieve dosages for a specific medication In this query, we show how to find the dosage information for a specific drug. For our dataset, we have chosen the corticosteriod `Prednisone`. The general approach is as follows: 1. As in previous sample queries, we start with the **medicationrelation** table. 2. We then perform an inner join with the **medicationrelationdosage** table on the primary ID key of the **medicationrelation** table and the **medication_id** foreign key in the **medicationrelationdosage** table. 3. Using the **entity_id** from the **medicationrelationdosage** table, we perform an inner join with the **entity** table. With an inner join with **foundentity** we retrieve the annotated term from the **text** column of the **foundentity** table. Please refer to the ER diagram in the [Medication Relation Tables](#medication-relation-tables) section to ensure you join on the correct columns. However, there is a problem with this approach. The database schema allows for more than one dosage to be associated with each medication relation. Although it may be rare in practice for multiple dosages to be mentioned in relation to a drug, it is possible. To be thorough, we should aggregate the dosage terms using a CTE as shown in the following SQL query. ```sql WITH dose_terms AS (SELECT medicationrelation.id AS medication_relation_id, drug_c.concept_id AS drug_concept_id, drug_c.description AS drug_concept, dose_fe.text AS dosage_terms FROM medicationrelation JOIN medicationrelationdosage dose ON dose.medication_id = medicationrelation.id -- Get drug concept description JOIN entity drug_e ON drug_e.id = medicationrelation.drug_entity_id AND drug_e.type_='found' JOIN foundentity drug_fe ON drug_fe.id = drug_e.entity_id JOIN foundentityconcept drug_fec ON drug_fec.found_entity_id = drug_fe.id JOIN concept drug_c ON drug_c.concept_id = drug_fec.concept_id AND drug_fec.concept_ontology = drug_c.ontology -- Get drug dose annotated text JOIN entity dose_e ON dose_e.id = dose.entity_id AND dose_e.type_='found' JOIN foundentity dose_fe ON dose_fe.id = dose_e.entity_id WHERE drug_c.concept_id = '8640' -- RxNorm concept ID for Prednisone AND drug_c.ontology = 'rxnorm') SELECT dose_terms.medication_relation_id, dose_terms.drug_concept_id, dose_terms.drug_concept, string_agg (dose_terms.dosage_terms, ' ') as dose_list FROM dose_terms GROUP BY dose_terms.medication_relation_id, dose_terms.drug_concept_id, dose_terms.drug_concept LIMIT 5; ``` | medication_relation_id | drug_concept_id | drug_concept | dose_list | | ---------------------- | --------------- | ------------------------------------ | ----------- | | 36 | 8640 | prednisone (Pharmacologic Substance) | 30 mg | | 102 | 8640 | prednisone (Pharmacologic Substance) | 50mg | | 236 | 8640 | prednisone (Pharmacologic Substance) | 60 mg | | 237 | 8640 | prednisone (Pharmacologic Substance) | 20 mg 40 mg | | 238 | 8640 | prednisone (Pharmacologic Substance) | 20 mg | ### Querying Medication Relations in Document Metadata The sample queries above show how to retrieve entity attributes. In addition to entity attributes, you can retrieve information about medication relations in documents. If your data set was generated so that the source document attributes are stored in the **documentmetadata** table during processing, you may also be able to query for medication relations with respect to the report's accession number, admission IDs and patient subject IDs if these were stored in the **documentmetadata** during processing. To learn more about querying document-related data, see [Document-level Tables](/nlp/database/schema/core/document-tables).