> 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. # Experiencer Relations In this section, we describe what experiencer relations are and explain its database schema representation. We also provide sample queries to illustrate the key aspects of the data schema. At the end of the section, we provide utility queries to help you explore the annotated terms and surrounding text for experiencer relations. After reading this section, you will know how to: * Retrieve concepts or entity types, such as a specific disease concept or a category of concepts (e.g. procedure), that was experienced by the patient, or the patient's relations (e.g. his mother). * Search the concepts and entities that were mentioned in the context of the patient's family history. * Narrow down the concepts or entities as needed. 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 Experiencer Relations? Experiencer relations relate an **experiencer** to an **experienced** term where the **experiencer** is the subject that is reported as having experienced a disorder, procedure or other applicable entity type (the **experienced** term). The **experiencer** is typically a \`person\`: the patient, or the patient's relation (such as his sister). Moreover, the **experiencer** can also refer to the patient's `family history` - either explicitly mentioned or implied by the section heading. Moreover, when the experiencer is the patient, the NLP API distinguishes between `explicit` and `implied` references. Thus, the **experiencer** in a relation can be one of the following: * **Implied - patient** In medical reports, most concepts are assumed to be about the patient, unless there is an explicit reference to the patient or someone else. For example, in the following sentences the assumed experiencer is the the patient. ```text INDICATIONS: Abdominal pain. There is no evidence of appendicitis. FINAL PATHOLOGIC DIAGNOSIS: papillary thyroid carcinoma. ``` > Because the understood assumption in medical documents is that the document in a patient’s chart contains data primarily about that patient, the most numerous type of experiencer relations are those where the subject is the `implied patient`. * **Explicit - patient** Examples of sentences where there is an explicit mention of the patient are: ```text The patient notes a 3-week history of knee pain. The patient has coronary heart disease. ``` > In the sentence "The patient notes a 3-week history of knee pain" the concept 'knee pain' would have a found experiencer of 'patient'. * **Implied - family history** Some medical reports have a "FAMILY HISTORY:" section, a list of diseases that do not identify the specific family member who had the diseases (example shown below). Concepts identified in these sections are assumed to belong to a patient's family members due to the section of the report in which they are found. ```text FAMILY HISTORY: coronary artery disease No breast cancer ``` * **Explicit - family history** A clinician may record the patient's family history in narrative form. For example: ```text No family history of clotting disorder. ``` > In this sentence, NLP API identifies the following: \* The text "family history" as the **experiencer** and maps it to `snomed` concept ID `57177007|Family history with explicit context (situation)|` \* the "clotting disorder" term as the **experienced** entity and maps it to `64779008|Blood coagulation disorder (disorder)|` * **Explicit - family relation** There are sentences which explicitly refer to the members of the patient's family. For example: ```text Her grandmother had gout and diabetes. Her mother had breast cancer. ``` > In the sentence "Her grandmother had gout and diabetes", the concepts 'gout' and 'diabetes' would have a found experiencer of 'grandmother'. It should be mentioned that how `the patient`, `persons` related to the patient, and the notion of `family history` is handled in different ontologies varies. This has practical implications when constructing SQL queries. In general, however, SNOMED CT provides good support for concepts representing persons (the patient, and patient's relations such as mother, sister, etc. ). > **Warning** > > **When using experiencer relations, use the SNOMED CT ontology.** > > Compound concepts such as `family history of congestive heart failure` exist in some ontologies; for example SNOMED has the concept `Family history of congestive heart failure (situation)| 433305001`. When the NLP API encounters such terms, it extracts an experiencer relation between the base form of the SNOMED concept, `congestive heart failure`, and `family history of`, but not the compound concept `Family history of congestive heart failure`. This is deliberate. By extracting the term congestive heart failure as `Congestive heart failure (disorder) | 42343007` the NLP API allows users to retrieve all `disorder` mentions by querying for the `disorder` SNOMED semantic tag. By contrast the `situation` semantic tag covers a diverse range of concepts making it a less useful query parameter. Likewise, some ontologies contain compound concepts like `no family history of drug abuse`; however, the NLP API avoids using the negated compound concepts, and instead stores the negated temporal context as the polarity attribute of the experiencer relation. ## JSON Data Model We have established that experiencers can be implied or explicit. In this section we will consider how this is represented in the JSON output: ### Explicit Experiencers #### The Patient First, consider the following sentence: ```text The patient has abdominal pain ``` This sentence will produce the JSON output below, where the **experienced** and the **experiencer** arguments represent the related entities `E1` and `E0`. Notice that the experiencer is an entity with label `E0`. The confidence score for the experiencer relation here is `1` since it is explicit. ```json { "label": "REX0", "attributes": { "confidence": 1, "polarity": "asserted" }, "args": { "experiencer": { "ref": "E0", "text": [ "patient" ] }, "experienced": { "ref": "E1", "text": [ "abdominal pain" ] } }, "concept_links": [] } ``` The the next JSON excerpt shows the JSON output for the patient entity `E0`. ```json { "label": "E0", "spans": [ { "start": 4, "end": 11 } ], "section_name": "INTRO", "attributes": { "heading_status": "outside_heading", "polarity": "asserted", "uncertainty": "certain", "question_status": "not_question", "guidance": "not_guidance", "known_ambiguity": "unambiguous" }, "concept_links": [ "Csnomed0", "Cumls_nci0", "Cradlex0" ], "locations": [ "S1", "SEC-0" ], "entity_type": { "snomed": "person", "umls": "Patient or Disabled Group", "radlex": "anatomical entity", "umls_nci": "Patient or Disabled Group" }, "concept_confidences": { "Csnomed0": 1 }, "text": [ "patient" ] } ``` Notice that the patient entity `E1` is mapped to several concepts and entity types depending on the ontologies selected for processing. For example, the concept label `Csnomed0` refers to the SNOMED CT patient concept ID `116154003` with the fully-qualified description of `patient (person)`. > **Warning** > > The cardinality of the relation between entities, concepts and entity types is important to be aware of when querying the Output Database. Each entity has a many-to-many relation with concepts and entity types. ### Implied Experiencers #### The Patient Consider the following phrase: ```text INDICATION: Diabetes ``` The patient is implied. The JSON output generated is different than that shown in the previous section. Notice that the implied patient reference is captured by the label reference `Epatient`. This special entity label identifies the implied patient in the JSON output. Notice that the confidence score here is `null`, since the experiencer relation is implied. In the database, the implied patient versus explicit patient is indicated in the **entity** table's **type\_** column. This distinction is important for data retrieval. This will be discussed in detail in the next section (see [Experiencer Relation Tables](#experiencer-relation-tables).) ```json { "label": "REX0", "attributes": { "confidence": null, "polarity": "asserted" }, "args": { "experiencer": { "ref": "Epatient", "text": [ "patient" ] }, "experienced": { "ref": "E0", "text": [ "Diabetes" ] } }, "concept_links": [] } ``` #### The Patient's Family History For implied family history references, the JSON output for the experiencer relation, will contain a value of `efamily` for the `ref` key, instead of `Epatient` as shown below. For the phrase: ```text Family History: Breast cancer ``` ```json { "label": "REX0", "attributes": { "confidence": null, "polarity": "asserted" }, "args": { "experiencer": { "ref": "Efamily", "text": [ "family" ] }, "experienced": { "ref": "E1", "text": [ "Breast cancer" ] } }, "concept_links": [] } ``` ## Experiencer Relation Tables The following ER diagram shows the **experiencerrelation** table and how it relates to **document** and **entity** tables in the Output Database schema. ![Entity-Relation diagram showing experiencerrelation, document and entity tables.](/_fern-files/emtelligent-prod.docs.buildwithfern.com/c3ce2411fea42139f9f5c4d9a580b8487c69b5c652c9ab66e470b5163e1fe9dd/assets/nlp/experiencer_schema.png) A few key points should be noted: * The **type\_** and **relation_id** columns in the **relation** table give the type of relation and the reference to a relation-specific table respectively. For experiencer relations, the **type\_** column must have a value of `experiencer`, and the **relation_id** is the reference to the primary key of the **experiencerrelation** table. * Each row in the **experiencerrelation** table represents an experiencer relation instance, and contains the following information for this relation type, namely the: > * **polarity** attributed > * **experiencer entity id** > * **experienced entity id** * The **relation** table **document_id** column is a foreign key reference to the **document** table. An inner join on this key can be performed to locate the documents where relations are found. * The **experiencer_id** and **experienced_id** columns are foreign key references to the **entity** table. The **entity** table is an entry point from which to gather stored in other entity-related tables such as the **foundentity**, **concept**, **foundentitytype** tables. For more information, see [Entity-level Tables](/nlp/database/schema/core/entity-tables). ### Key Attributes Notice that experiencer relations have **polarity**. For example, in the statement `The patient has no family history of breast cancer`, the absence of breast cancer in the patient's family history is captured in the polarity attribute of the **experiencer relation**. It is important to consider the polarity attribute when querying experiencer relations about the patient's family history. ### Found vs. Assumed Entities The distinction between `found` and `assumed` entities applies only to references to patients as follows: > * `found` entities are extracted from explicit mentions of the patient, and map to concepts and entity types in the **concept** and **foundentitytype** tables respectively. > * `assumed` entities are extracted from implicit references to the patient, and do not have concept or entity type representation in the database, but are represented as rows in the **assumedentity** table. This distinction between `found` and `assumed` is important when querying experiencer relations. To retrieve the complete set of all patient relations, you must take this into account. To determine if an entity is `found` or `assumed`, you can look up the value stored in the **type\_** column of the **entity** table: * When the value of the **type\_** column of the **entity** table is `found`, the **entity_id** column refers to the primary key of the **foundentity** table. * When the value of **type\_** is `assumed`, the value of **entity_id** refers to the primary key of the **assumedentity** table. ## Sample Queries Because relations have multiple entities and attributes, querying involves multiple join operations with specific join conditions. A good approach is to start by exploring the data with simple queries that can be used, in subqueries or common table expressions (CTE), to build more elaborate queries. We begin by querying the **experiencer entities** as follows: * [Query: Count of found vs. assumed experiencers](#query-count-of-found-vs-assumed-experiencers) * [Query: Count of found experiencers by concept](#query-count-of-found-experiencers-by-concept) * [Query: List relation where experiencers are patients (implied and explicit)](#query-list-relation-where-experiencers-are-patients-implied-and-explicit) * [Query: List relations where experiencers are relatives of patients](#query-list-relations-where-experiencers-are-relatives-of-patients) * [Query: List relations where experiencers refer to patient family history](#query-list-relations-where-experiencers-refer-to-patient-family-history) Next, we show examples of querying the **experienced entities** as follows: * [Query: List concepts for experienced entities](#query-list-concepts-for-experienced-entities) * [Query: Distribution of experienced concepts by semantic type](#query-distribution-of-experienced-concepts-by-semantic-type) Finally, we combine elements of previous queries to retrieve data for experiencer and experienced entities: * [Query: Count of relations where patients have diabetes or CAD](#query-count-of-relations-where-patients-have-diabetes-or-cad) ### Query: Count of found vs. assumed experiencers This query reinforces a key point which is that experiencer entities can be implied (`assumed`) or explicit (`found`). Let's find the relative distribution of experiencer relations by found vs. assumed experiencers. This query involves an inner join between the **experiencerrelation** table and the **entity** table as shown below. By grouping on the **entity.type\_** we get the count of relation instances of `found` vs. `assumed` experiencers. As expected the number of assumed experiencer relations far outnumber the found experiencer relations. ```sql SELECT e.type_ AS experiencer_type, count(*) FROM experiencerrelation er JOIN entity e ON er.experiencer_id = e.id GROUP BY e.type_; ``` | experiencer_type | count | | ---------------- | ----- | | assumed | 6504 | | found | 530 | ### Query: Count of found experiencers by concept Next, we want to know how many experiencer relations were extracted, and who the experiencers were. As mentioned above, an experiencer can be a patient, a person other than the patient, or reference to the patient's family history. The query below performs an inner join between the **entity**, **foundentity**, **foundentityconcept** and **concept** tables to look up the SNOMED CT concepts for each experiencer entity, and then aggregates the results by the concepts found. For more information about these tables see [Entity-level Tables](/nlp/database/schema/core/entity-tables). ```sql SELECT e.type_ AS experiencer_type, c.description, c.concept_id, c.ontology, count(*) AS count FROM experiencerrelation er JOIN entity e ON er.experiencer_id = e.id -- NOTE: find experience(r) entities JOIN foundentity fe ON fe.id = e.entity_id -- IMPORTANT: use e.entity_id not e.id AND e.type_ = 'found' -- IMPORTANT: this condition is required. JOIN foundentityconcept fec ON fe.id = fec.found_entity_id JOIN concept c ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology WHERE c.ontology = 'snomed' GROUP BY e.type_, c.description, c.concept_id, c.ontology ORDER BY 1 LIMIT 5; ``` | experiencer_type | description | concept_id | ontology | count | | ---------------- | ---------------- | ---------- | -------- | ----- | | found | Patient (person) | 116154003 | snomed | 447 | | found | Person (person) | 125676002 | snomed | 25 | | found | Wife (person) | 127850001 | snomed | 4 | | found | Woman (person) | 224526002 | snomed | 1 | | found | Sister (person) | 27733009 | snomed | 4 | ### Query: List relation where experiencers are patients (implied and explicit) The query below finds the relation ids for relations where the experiencer entity is a patient, either found or assumed. The result is a union of two CTEs as follows: * The first CTE finds explicit patient references by querying for entities associated with the SNOMED CT concept ID for `Patient (person)`. Concept IDs are stored in the **concept** table. * The second CTE finds implicit patient references by querying for entities of type `assumed` in the **entity** table's **type\_** column, and a **value** of `patient` in the **assumedentity** table. This query returns the subset of relation IDs. ```sql WITH explicitpatients AS (SELECT er.id AS relation_id, c.description as person FROM experiencerrelation er JOIN entity e ON er.experiencer_id = e.id -- NOTE: find experience(r) entities JOIN foundentity fe ON fe.id = e.entity_id -- IMPORTANT: e.entity_id not e.id AND e.type_ = 'found' -- IMPORTANT: this condition is required. JOIN foundentityconcept fec ON fe.id = fec.found_entity_id JOIN concept c ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology WHERE c.ontology = 'snomed' AND c.concept_id = '116154003'), implicitpatients AS (SELECT er.id AS relation_id, ae.value as person FROM entity e JOIN experiencerrelation er ON er.experiencer_id = e.id -- NOTE: experience(r) entities AND e.type_ = 'assumed' JOIN assumedentity ae ON ae.id = e.entity_id AND ae.value = 'patient') -- NOTE: must specify value as 'patient' (NOT 'family') SELECT * FROM implicitpatients UNION SELECT * FROM explicitpatients LIMIT 5; ``` | relation_id | person | | ----------- | ---------------- | | 1 | Patient (person) | | 2 | Patient (person) | | 3 | Patient (person) | | 4 | patient | | 5 | patient | ### Query: List relations where experiencers are relatives of patients The following query searches for relations where the experiencer entity maps to the SNOMED CT concept for `Mother (person)`, `Father (person)`, `Parent (person)`, and returns a set of relation IDs from the **experiencerrelation** table. ```sql SELECT er.id AS relation_id, fe.text AS experiencer FROM experiencerrelation er JOIN entity e ON er.experiencer_id = e.id -- NOTE: find experience(r) entities JOIN foundentity fe ON fe.id = e.entity_id -- IMPORTANT: e.entity_id not e.id AND e.type_ = 'found' -- IMPORTANT: this condition is required. JOIN foundentityconcept fec ON fe.id = fec.found_entity_id JOIN concept c ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology WHERE c.ontology = 'snomed' AND c.concept_id IN ('72705000', '66839005', '40683002') -- SNOMED CT concepts id 'Mother (person)', 'Father (person)', 'Parent (person') respectively ORDER BY 1 LIMIT 5; ``` | relation_id | experiencer | | ----------- | ----------- | | 2287 | Father | ### Query: List relations where experiencers refer to patient family history This query finds implicit and explicit references to the patient's family history, by querying for entities of type `assumed`, and whose **value** in the **assumedentity** table is `family` to extract implied references and querying for entities of type `found` that are mapped to the SNOMED CT concept ID `57177007|Family history with explicit context (situation)|`. ```sql WITH implied_experiencers AS (SELECT er.id AS relation_id, ae.value as experiencer, e.type_ AS mention_type FROM entity e JOIN experiencerrelation er ON er.experiencer_id = e.id -- NOTE: experience(r) entities AND e.type_ = 'assumed' JOIN assumedentity ae ON ae.id = e.entity_id AND ae.value = 'family'), explicit_experiencers AS (SELECT er.id AS relation_id, fe.text AS experiencer, e.type_ AS mention_type FROM experiencerrelation er JOIN entity e ON er.experiencer_id = e.id -- NOTE: find experience(r) entities JOIN foundentity fe ON fe.id = e.entity_id -- IMPORTANT: use e.entity_id not e.id AND e.type_ = 'found' -- IMPORTANT: this condition is required. JOIN foundentityconcept fec ON fe.id = fec.found_entity_id JOIN concept c ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology WHERE c.ontology = 'snomed' AND c.concept_id = '57177007') -- the concept ID for family history with explicit context SELECT * FROM explicit_experiencers UNION SELECT * FROM implied_experiencers; ``` | relation_id | experiencer | mention_type | | ----------- | -------------------------- | ------------ | | 6215 | family history of | found | | 6560 | Reported family history of | found | ### Query: List concepts for experienced entities Previous query examples explored the experiencer entities. Here, we want to survey the experienced entities in the dataset, based on the concepts that they map to in the SNOMED CT ontology. ```sql SELECT fe.text AS annotated_term, c.concept_id as concept_ID, c.description, count(*) AS count FROM experiencerrelation er JOIN entity e ON er.experienced_id = e.id -- NOTE: join on experience(d)_id to find the experienced entities JOIN foundentity fe ON fe.id = e.entity_id AND e.type_='found' JOIN foundentityconcept fec ON fe.id = fec.found_entity_id JOIN concept c ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology WHERE c.ontology = 'snomed' GROUP BY fe.text, c.concept_id, c.description ORDER BY count DESC LIMIT 5; ``` | annotated_term | concept_id | description | count | | -------------- | ---------- | -------------------------- | ----- | | FINDINGS | 404684003 | Clinical finding (finding) | 63 | | PROCEDURE | 71388002 | Procedure (procedure) | 41 | | removed | 118292001 | Removal (procedure) | 33 | | procedure | 71388002 | Procedure (procedure) | 28 | | findings | 404684003 | Clinical finding (finding) | 27 | ### Query: Distribution of experienced concepts by semantic type The NLP API allows you to analyze occurrences of entities at multiple levels of granularity. This SQL query shows how to find the frequencies of different types of concepts. Here we use semantic types defined in the SNOMED CT ontology. Semantic type information is stored in the **foundentitytype** table. To access the semantic types for an entity, we will join the **entity**, **foundentity** and **foundentitytype** tables as shown below. For more information about these tables see [Entity-level Tables](/nlp/database/schema/core/entity-tables). ```sql SELECT fet.type_name, count(*) AS count FROM experiencerrelation er JOIN entity e ON er.experienced_id = e.id JOIN foundentity fe ON fe.id = e.entity_id AND e.type_='found' -- NOTE: experience(d) entities JOIN foundentitytype fet ON fe.id = fet.found_entity_id WHERE fet.ontology = 'snomed' GROUP BY fet.type_name ORDER BY 1 LIMIT 5; ``` | type_name | count | | ----------------------- | ----- | | body structure | 1419 | | disorder | 1289 | | finding | 1387 | | morphologic abnormality | 438 | | observable entity | 582 | ### Query: Count of relations where patients have diabetes or CAD Finally, we combine the queries presented previously to count the number of references to diabetes or coronary artery disease (CAD) experienced by the patient. This query involves a number of steps: 1. First, get the subset of relation ids that pertain to the patient. 2. Then join with **foundentity**, **foundentityconcept** and **concept** tables to assemble the required columns. 3. Filter the results for the SNOMED CT concepts for diabetes (`73211009`) or CAD (`53741008`) and ensure that the **polarity** of the relation is `asserted` in the WHERE clause. 4. Finally, summarize the results with a count and GROUP BY. ```sql WITH patient_ers AS (WITH explicitpatients_ers AS (SELECT er.id AS expid FROM experiencerrelation er JOIN entity e ON er.experiencer_id = e.id -- NOTE: find experience(r) entities JOIN foundentity fe ON fe.id = e.entity_id -- IMPORTANT: e.entity_id not e.id AND e.type_ = 'found' -- IMPORTANT: this condition is required. JOIN foundentityconcept fec ON fe.id = fec.found_entity_id JOIN concept c ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology WHERE c.ontology = 'snomed' AND c.concept_id = '116154003'), implicitpatients_ers AS (SELECT er.id AS expid FROM entity e JOIN experiencerrelation er ON er.experiencer_id = e.id -- NOTE: experience(r) entities AND e.type_ = 'assumed' JOIN assumedentity ae ON ae.id = e.entity_id AND ae.value = 'patient') SELECT * FROM implicitpatients_ers UNION SELECT * FROM explicitpatients_ers ORDER BY expid) SELECT er.polarity AS polarity, c.description, count(patient_ers.expid) FROM patient_ers JOIN experiencerrelation er ON patient_ers.expid = er.id JOIN entity e ON er.experienced_id = e.id JOIN foundentity fe ON fe.id = e.entity_id AND e.type_='found' -- NOTE: experience(d) entities JOIN foundentityconcept fec ON fe.id = fec.found_entity_id JOIN concept c ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology WHERE c.ontology = 'snomed' AND c.concept_id IN ('73211009', '53741008') AND er.polarity = 'asserted' GROUP BY er.polarity, c.description; ``` | polarity | description | count | | -------- | ------------------------------------ | ----- | | asserted | Coronary arteriosclerosis (disorder) | 12 | | asserted | Diabetes mellitus (disorder) | 7 | This query can be easily modified to analyze the experienced entities for other experiencer subjects. For example, replace the `explicitpatients_ers` and `implicitpatient` CTEs above with a query that returns relation IDs for the patient's family history. See [Query: List relations where experiencers refer to patient family history](#query-list-relations-where-experiencers-refer-to-patient-family-history). ## Utility Queries It is sometimes useful to be able to survey the extracted terms and review the context in which they occur, and the concepts they may to. The SQL queries provided here show how to retrieve this information for experiencer relations by querying the entity-level and document level tables. See [Entity-level Tables](/nlp/database/schema/core/entity-tables), and [Document-level Tables](/nlp/database/schema/core/document-tables) for a detailed explanation of these tables. ```sql /** Explore text elements and concepts in experiencer relations for all `found` experiencer entities */ -- Show sentence, and corresponding annotated terms and concepts for experiencer and experienced entities SELECT er.polarity, experiencer_fe.text AS experiencer_term, experiencer_c.description AS experiencer_concept, experiencer_c.concept_id AS experiencer_concept_id, experienced_fe.text AS experienced_text, experienced_c.description AS experienced_concept, experienced_c.concept_id AS experienced_concept_id, sl.text AS sentence FROM experiencerrelation er JOIN entity experiencer_e ON experiencer_e.id = er.experiencer_id JOIN foundentity experiencer_fe ON experiencer_fe.id = experiencer_e.entity_id AND experiencer_e.type_ = 'found' -- tables required to get experience(r) concept from concept table JOIN foundentityconcept experiencer_fec ON experiencer_fe.id = experiencer_fec.found_entity_id JOIN concept experiencer_c ON experiencer_fec.concept_id = experiencer_c.concept_id AND experiencer_fec.concept_ontology = experiencer_c.ontology AND experiencer_c.ontology = 'snomed' JOIN entity experienced_e ON experienced_e.id = er.experienced_id JOIN foundentity experienced_fe ON experienced_fe.id = experienced_e.entity_id AND experienced_e.type_= 'found' JOIN foundentityconcept experienced_fec ON experienced_fe.id = experienced_fec.found_entity_id JOIN concept experienced_c ON experienced_fec.concept_id = experienced_c.concept_id AND experienced_fec.concept_ontology = experienced_c.ontology AND experienced_c.ontology = 'snomed' -- tables required to get sentence text from sentencelocationspan and document table JOIN foundentitylocation fel ON experiencer_fe.id = fel.found_entity_id JOIN location l ON fel.location_id = l.id AND (l.type_ = 'sentence') -- Important: check that location type is 'sentence' JOIN sentencelocation sl ON sl.id = l.location_id; /** Count of assumed entity types - patient vs family */ SELECT value, count(*) FROM assumedentity GROUP BY value; +-------+-----+ |value |count| +-------+-----+ |patient|5 | |family |3 | +-------+-----+ /** Explore text elements and concepts in experiencer relations for `assumed` experiencer entities is the patient's family */ SELECT er.polarity, experiencer_ae.value, experienced_c.description AS experienced_concept, experienced_c.concept_id AS experienced_concept_id, sl.text AS sentence FROM experiencerrelation er -- tables required to get experience(r) term from assumedentity table JOIN entity experiencer_e ON experiencer_e.id = er.experiencer_id JOIN assumedentity experiencer_ae ON experiencer_ae.id = experiencer_e.entity_id AND experiencer_e.type_ = 'assumed' -- tables required to get experience(d) concepts from the concept table JOIN entity experienced_e ON experienced_e.id = er.experienced_id JOIN foundentity experienced_fe ON experienced_fe.id = experienced_e.entity_id AND experienced_e.type_= 'found' -- experience(d)_id not experience(r)_id JOIN foundentityconcept experienced_fec ON experienced_fec.found_entity_id = experienced_fe.id JOIN concept experienced_c ON experienced_c.concept_id = experienced_fec.concept_id AND experienced_c.ontology = experienced_fec.concept_ontology -- tables required to get sentence text from sentencelocationspan and document table JOIN foundentitylocation fel ON experienced_e.entity_id = fel.found_entity_id JOIN location l ON fel.location_id = l.id AND (l.type_ = 'sentence') JOIN sentencelocation sl ON sl.id = l.location_id AND experiencer_ae.value = 'family' AND experienced_c.ontology = 'snomed'; /** Retrieve text from report sections where found experiencer entities are found */ -- Find section text and name where experience(d) entities related to concept 'diabetes' is found SELECT distinct(d.id, fe.section_name), e.type_ AS experienced_entity_type, c.description, sls.start AS sectionStart, sls.end AS sectionEnd, sl.text AS section_text FROM experiencerrelation er JOIN entity e ON er.experienced_id = e.id JOIN foundentity fe ON fe.id = e.entity_id AND e.type_='found' -- NOTE: required JOIN foundentityconcept fec ON fe.id = fec.found_entity_id JOIN concept c ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology JOIN foundentitylocation fel ON fe.id = fel.found_entity_id JOIN foundentitylocation fel ON fe.id = fel.found_entity_id JOIN location l ON fel.location_id = l.id AND l.type_ = 'section' -- IMPORTANT: must filter for 'section' NOT 'sentence' JOIN sectionlocation sl ON l.location_id = sl.id -- IMPORTANT: joining on fel.location_id = sl.id be incorrect WHERE c.ontology = 'snomed' AND c.concept_id = '73211009'; -- SNOMED CT id for diabetes = '73211009' -- Note: It is possible for multiple relations where 'diabetes' is mentioned in the same document and section. Therefore distinct clause is needed ```