Experiencer Relations | emtelligent Documentation

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, and Document-level 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.

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:

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.

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:

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:

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. ).

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:

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.

{
   "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.

{
         "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).

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:

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.)

{
   "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:

Family History: 
Breast cancer

{
   "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.

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.

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:

Next, we show examples of querying the experienced entities as follows:

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 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.

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.

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.

   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.

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)|.

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.

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.

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:

First, get the subset of relation ids that pertain to the patient.
Then join with foundentity, foundentityconcept and concept tables to assemble the required columns.
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.
Finally, summarize the results with a count and GROUP BY.

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.

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, and Document-level Tables for a detailed explanation of these tables.

/**
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