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

# Entity-level Tables

In this section, we explore the schema representation of Entities, their attributes, and how they relate to standardized ontologies via Concepts and Entity types. We will present sample queries to show how to retrieve useful information from the entity-level tables. We have also provided some helper queries at the end of the section that list various data labels, such as ontology names and semantic types, that can be used as filter parameters.

After reading this page, you will be able to:

1. Explore the entities extracted by the NLP API
2. Query for entities with specific attributes
3. Search for entities by ontology, concepts, or semantic types
4. Show the annotated terms that were detected by the NLP API's entity recognition engine

## Entity, FoundEntity and AssumedEntity Tables

Entity objects represent terms that are recognized and extracted by the NLP API. These are stored in the **entity** table. An entity can be of two types: `found` and `assumed`. Found entities are those entities that are explicitly mentioned in the report. Found entities are explicit references to the patient, recognized terms, and report sections. Assumed entities are implicit references to the patient, and are used when querying for Experiencer relations (see [Experiencer Relations](/nlp/database/schema/relations/experiencer)).

Information about FoundEntities is stored in the **foundentity** table. This table contains many key attributes such as the report **section** where the entity was found, the **polarity**, and **uncertainty** of the entity.

* The **section_name** column stores the heading of the report section where an entity was found. Knowing the section where an entity occurs provides important context for data analysis. For example, a disorder mentioned in the `PAST MEDICAL HISTORY` has a different connotation than a disorder mentioned in the `DISCHARGE DIAGNOSIS` section. To make data analysis easier, the NLP API normalizes section information by mapping commonly found headings to standardized headings. The value stored in the **section** column depends on the document type processed. For Radiology reports, for example, typical sections are `INDICATION`, `TECHNIQUE`, `FINDING`, `IMPRESSION`, `COMPARISON`, and `ADDENDUM`.
* The **polarity** table column has the value `asserted` or `negated`
* The **uncertainty** table column has the value `certain` or `uncertain`
* The **heading_status** table column that indicates if the entity is inside of or outside of a section heading has the value `inside_heading` or `outside_heading`
* The **text** table column stores the annotated text for the entity
* The **start** table column stores the start offset of the found entity. The start offset and the text is useful for locating an entity in a body of text.

In addition to these attributes, the NLP API recognizes a third type of attribute: the **measurement unit**. This attribute is extracted when the `entity-measurement-unit` feature or the `measurement-relations` feature is enabled during processing. Because of the one-to-many relation between measurement entities and their units, **measurement unit** attributes are stored in its own table: the **foundentitymeasurement** table. [Measurement values](/nlp/database/schema/relations/measurement#measurement-values) and [measurement units](/nlp/database/schema/relations/measurement#measurement-units) attributes are discussed in-depth with respect to [Measurement Relations](/nlp/database/schema/relations/measurement). Note, however, measurement unit attributes can also be extracted independently of measurement relations.

The following ER diagram, shows the relation between the **entity**, and **foundentity** and related tables.

![Relation between entity, foundentity and assumedentity tables](/_fern-files/emtelligent-prod.docs.buildwithfern.com/6b377600dc9755d380d64ecf12b2e6553a3217ba449262728955d1dc8a067a00/assets/nlp/entity_tables.png)

> **Warning**
>
> The **entity_id** column in the **entity** table references the primary key ID of the **foundentity** table. Note that this is not set up as an explicit foreign key constraint.
>
> When performing joins between these tables, it is easy to mistakenly JOIN on the primary key of the entity table instead of the `entity_id` column especially when using auto-complete functionality in some SQL editors. The correct JOIN clause is:
>
> `SELECT * FROM entity JOIN foundentity ON entity.entity_id = foundentity.id AND entity.type_='found';`
>
> It is also important to note that the additional constraint, `entity.type_='found'`, is **required**. It is required because `entity.entity_id` values are NOT unique within the **entity** table; however, the combination of `entity.type_` and `entity.entity_id` is unique.

### Query: Count of entities found in FINDINGS

This query returns the number of entities found in the FINDINGS section that are asserted with no uncertainty using the **foundentity** table.

```sql
SELECT count(*) AS term_count
  FROM foundentity f
 WHERE f.section_name = 'FINDINGS'
   AND f.polarity = 'asserted'
   AND f.uncertainty = 'certain';
```

This query is not very useful because we only know that the term was positively asserted but we do not know what that term is. The next query shows how to extract the annotated term associated with Entity instances.

### Query: Count of distinct terms

This query extracts the annotated term for an entity:

```sql
SELECT f.text AS annotated_term, count(*) AS frequency
  FROM foundentity f
 WHERE f.polarity = 'asserted'
   AND f.uncertainty = 'certain'
 GROUP BY f.text
 ORDER BY 1
 LIMIT 5;
```

| annotated_term | frequency |
| -------------- | --------- |
| #              | 2         |
| 0              | 1         |
| 0.0            | 1         |
| 0.05 %         | 1         |
| 0.1%           | 1         |

While an improvement on the previous query, the data returned by this query is still not useful for data analysis. This is because **many terms can refer to the same concept**. Take, for example, `diabetes` which also commonly appears in medical reports as `diabetes mellitus`, or abbreviated as `DM`. It is therefore useful to map related entities to a single semantic representation within a standardized vocabulary. The NLP API supports multiple biomedical ontologies for this purpose. It maps extracted terms (or entities) to concepts identifiers and semantic types from various ontologies. Understanding the database schema relations between entities, concepts and entity types is the subject of the next section.

## Concepts and Entity Type Tables

In the NLP API, recognized terms are related to concepts and semantic types in multiple ontologies. A **concept** corresponds to the standardized representation of a recognized term in a specific ontology. **Entity types** correspond to the semantic type or classification for a set of related concepts. For example, the various variant forms for `diabetes`, such as `diabetes mellitus` and `DM` are mapped to the concept `diabetes mellitus` of semantic type `disorder` in the SNOMED CT ontology. The ontologies that entities are mapped to are specified during data processing as the NLP API commandline parameters.

Each found entity may be associated with one or more concepts and entity types depending on the number of ontologies selected during document processing. For example, in the JSON output for the entity `diabetes` found in a report processed with the `snomed-ontology` and `radlex-ontology` features enabled, the `diabetes` entity has:

* **Two** concept ids, `Cradlex1` and `Csnomed1`, in its `concept_links` attribute
* Multiple **entity types**, one for each ontology selected at processing, and if available for the ontologies selected, one for the UMLS metathesaurus in its `entity_type` attribute.

```json
{
    "label": "E1",
    "spans": [
        {
            "start": 12,
            "end": 20
        }
    ],
    "section_name": "INDICATION",
    "attributes": {
        "heading_status": "outside_heading",
        "polarity": "asserted",
        "uncertainty": "certain",
        "question_status": "not_question",
        "guidance": "not_guidance",
        "known_ambiguity": "unambiguous"
    },
    "concept_links": [
        "Cradlex1",
        "Csnomed0"
    ],
    "locations": [
        "S2",
        "SEC-1"
    ],
    "entity_type": {
        "radlex": "clinical finding",
        "snomed": "disorder",
        "umls": "Disease or Syndrome"
    },
    "concept_confidences": {
        "Csnomed0": 1
    },
    "text": [
        "Diabetes"
    ]
}
```

In the Output Database schema, the relation between found entities, concepts and entity types, is implemented by the following tables:

* **foundentityconcept**,
* **concept**, and
* **foundentitytype**

The following ER diagram shows the relation between these tables.

![Join tables model many-to-many relation between FoundEntities, Concepts and Entity types](/_fern-files/emtelligent-prod.docs.buildwithfern.com/4ef168fea284572e7a6c7a1f28a2e4162832037f5583c3e8f6c5f844d01e67be/assets/nlp/entity_concept_overview.png)

It is important to note the following:

1. The **foundentity** table and the tables whose names begin with **foundentity** such as **foundentityconcept**, **foundentitytype** and **foundentityspan**, are related using the **found_entity_id** foreign key reference.
2. The **foundentityconcept** table relates the **foundentity** to the **concept** table. The relation between the **foundentityconcept** table and the **concept** table is a composite foreign key reference that is composed of the columns that store the concept ID and the ontology data in the respective tables. This is important when performing a join between these tables.
3. The **foundentitytype** table stores the ontology-specific semantic classification of the entity in the **type_name** column. For example, the SNOMED CT entity type for the term `diabetes` the **type_name** column contains the semantic type of `disorder` and the **ontology** column contains the value `snomed`, whereas for RadLex the **type_name** column contains `clinical finding` and the **ontology** column contains `radlex` .
4. The **foundentityspan** table stores the span offsets of the terms associated with a **foundentity**. For more information see [FoundEntitySpan Table](/nlp/database/schema/core/document-tables#foundentityspan-table).

> **Warning**
>
> The ontologies referenced in the **foundentitytype** table are distinct from the ontologies referenced by the **concept** table. The former refers to the ontology used for semantic type classification of related concepts, and the later refers to the ontology that defines the standardized concept related to an entity.

### Query: Show concept description for entities

This query lists all entity occurrences and attributes for the SNOMED CT concept id for `diabetes` by joining the following tables:

* **foundentity**
* **concept**

The SNOMED CT concept id for `diabetes` is `73211009`. You can look up SNOMED CT concept identifiers using the [SNOMED CT Browser](https://browser.ihtsdotools.org).

> **Warning**
>
> When performing an inner join on the **foundentityconcept** and **concept**, the conditional statement must test for equality on the concept_id and ontology columns as follows: `fec.concept_id = c.concept_id AND fec.concept_ontology = c.ontology`

```sql
SELECT fe.id as foundentity_id,
      c.description AS concept_description, 
      c.ontology AS concept_ontology, 
      fe.polarity, 
      fe.uncertainty,
      fe.section_name, 
      fe.text AS annotated_text
  FROM foundentity fe
          JOIN foundentityconcept fec
          ON fe.id = fec.found_entity_id
          JOIN concept c
          ON fec.concept_id = c.concept_id AND fec.concept_ontology = c.ontology
             -- IMPORTANT: JOIN ON clause compares both concept id and ontology fields of the composite foreign key reference
WHERE c.concept_id = '73211009' -- SNOMED CT concept ID for `diabetes mellitus`
  AND c.ontology = 'snomed'     -- use only SNOMED CT ontology
ORDER BY 1
LIMIT 5;
```

| foundentity_id | concept_description          | concept_ontology | polarity | uncertainty | section_name                      | annotated_text |
| -------------- | ---------------------------- | ---------------- | -------- | ----------- | --------------------------------- | -------------- |
| 6993           | Diabetes mellitus (disorder) | snomed           | asserted | certain     | HISTORY OF PRESENT ILLNESS        | diabetic       |
| 7239           | Diabetes mellitus (disorder) | snomed           | asserted | certain     | PROCEDURES AND TREATMENT PROVIDED | Diabetes       |
| 12216          | Diabetes mellitus (disorder) | snomed           | asserted | certain     | PAST MEDICAL HISTORY              | Diabetes       |
| 12254          | Diabetes mellitus (disorder) | snomed           | asserted | certain     | DISCHARGE DIAGNOSIS               | Diabetes       |
| 13720          | Diabetes mellitus (disorder) | snomed           | asserted | certain     | PLAN                              | diabetes       |

### Query: Find frequency of SNOMED CT concepts of type 'disorder'

The following query uses the **foundentitytype** and **foundentityconcept** join tables to relate a concept to it's semantic type for a given ontology. In this example, we have chosen SNOMED CT, and the semantic type of `disorder`.

Note, you can narrow down the search results to return only disorder concepts that appear in a specific section of a report by adding a logical condition on the **section_name** column of **foundentitytable**.

```sql
SELECT fet.type_name as semantic_type, 
        c.ontology as concept_ontology, 
        c.description as concept_description, 
        count(*) AS frequency
  FROM foundentity fe
          JOIN foundentitytype fet
          ON fe.id = fet.found_entity_id
          JOIN foundentityconcept fec
          ON fe.id = fec.found_entity_id
          JOIN concept c
          ON fec.concept_id = c.concept_id AND fet.ontology = c.ontology
WHERE fet.ontology = 'snomed'    -- filter for SNOMED CT entity types
  AND fet.type_name = 'disorder' -- filter for SNOMED CT semantic type of 'disorder'
--AND fe.section_name = 'FINDINGS'  
GROUP BY fet.type_name, c.ontology, c.description
ORDER BY 1
LIMIT 5
```

| semantic_type | concept_ontology | concept_description                         | frequency |
| ------------- | ---------------- | ------------------------------------------- | --------- |
| disorder      | snomed           | Abdominal aortic aneurysm (disorder)        | 2         |
| disorder      | snomed           | Abdominal aortic atherosclerosis (disorder) | 1         |
| disorder      | snomed           | Abdominal organomegaly (disorder)           | 1         |
| disorder      | snomed           | Abnormal uterine bleeding (disorder)        | 2         |
| disorder      | snomed           | Abscess (disorder)                          | 5         |

### Query: Find section names

This query returns a list of all normalized section names in the database.

```sql
SELECT DISTINCT section_name FROM foundentity
```

| section_name                            |
| --------------------------------------- |
| 2) BIOPSIES OF BODY                     |
| 2-D M-MODE                              |
| 2-D STUDY                               |
| ADDENDUM                                |
| ADMISSION DIAGNOSIS SECTION             |
| ADVANCE DIRECTIVES SECTION              |
| ALLERGIES                               |
| ALLERGIES AND INTOLERANCES SECTION      |
| ANESTHESIA                              |
| ASSESSMENT                              |
| ASSESSMENT AND PLAN                     |
| ASSESSMENT SECTION                      |
| BIOPSY OF CERVIX 1 O'CLOCK              |
| CLINICAL INFORMATION                    |
| CODE STATUS                             |
| COMMENTS                                |
| COMPARISION                             |
| COMPARISON                              |
| COMPLICATIONS                           |
| CONDITION ON DISCHARGE                  |
| CONSENT                                 |
| DIAGNOSIS                               |
| DISCHARGE DIAGNOSIS                     |
| DISCHARGE MEDICATIONS SECTION           |
| ECG                                     |
| ENCOUNTERS SECTION                      |
| ESTIMATED BLOOD LOSS                    |
| FAMILY HISTORY SECTION                  |
| FINDINGS                                |
| FUNCTION                                |
| FUNCTIONAL STATUS SECTION               |
| GOALS SECTION                           |
| GROSS/MACROSCOPIC DESCRIPTION           |
| HEALTH CONCERNS SECTION                 |
| HISTORY                                 |
| HISTORY OF PRESENT ILLNESS              |
| HISTORY OF PRESENT ILLNESS SECTION      |
| HOSPITAL DISCHARGE INSTRUCTIONS SECTION |
| IMMUNIZATIONS SECTION                   |
| IMPRESSION                              |
| INDICATION                              |
| INSTRUCTIONS SECTION                    |
| INTERPRETATION                          |
| INTRO                                   |
| MEDICAL EQUIPMENT SECTION               |
| MEDICATIONS                             |
| MEDICATIONS ADMINISTERED SECTION        |
| MEDICATIONS SECTION                     |
| MENTAL STATUS SECTION                   |
| MICROSCOPIC DESCRIPTION                 |

Note that NLP API recognizes many more sections than shown above. It also extracts section names for sections and subsections of medical reports. The level of the section can be retrieved from the **sectionlocation** table. More information is about this is found in the [Document-level Tables](/nlp/database/schema/core/document-tables) page.

## Additional Resources

### Ontology References

Here are some useful links for looking up ontology-specific concept identifiers:

* To look up or browse SNOMED CT concepts: [https://browser.ihtsdotools.org](https://browser.ihtsdotools.org)
* To browse RadLex concepts: [http://www.radlex.org](http://www.radlex.org)
* To browse some of the UMLS ontologies: [https://uts.nlm.nih.gov/metathesaurus.html](https://uts.nlm.nih.gov/metathesaurus.html)

### Utility SQL Queries

While planning a query, you will often find that you need to understand how annotated terms are mapped to concepts and entity types before selecting a subset to analyze. Below are some data exploration queries that may be helpful.

```sql
/**
  Useful exploratory queries for available concepts and types
*/

-- List of available ontologies for semantic types
SELECT DISTINCT fet.ontology
  FROM foundentitytype fet;

-- List of available ontologies for concepts
SELECT DISTINCT c.ontology
  FROM concept c;

-- List frequency of annotated terms
SELECT text, count(*) AS frequency
  FROM foundentity
 GROUP BY text
 ORDER BY frequency DESC;

-- List of available semantic types for ontology
SELECT DISTINCT fet.type_name
  FROM foundentitytype fet
 WHERE fet.ontology = 'snomed';

-- List concept descriptions for a specific ontology
SELECT c.ontology, c.description
  FROM concept c
 WHERE c.ontology = 'snomed'               -- change to ontology of interest
 GROUP BY c.ontology, c.description;

-- List frequency of entity types for a specific ontology
SELECT fet.ontology, fet.type_name, count(*) AS total
  FROM foundentitytype fet
 WHERE fet.ontology = 'radlex'             -- change to ontology of interest
 GROUP BY fet.ontology, fet.type_name
 ORDER BY total DESC;


-- Browse annotated terms of snomed type: procedure, disorder or finding
SELECT fet.type_name, fe.text as annotated_text, count(*) AS frequency
  FROM foundentity fe
          JOIN foundentitytype fet
          ON fe.id = fet.found_entity_id
 WHERE fet.ontology = 'snomed' 
   AND fet.type_name IN ('disorder', 'finding', 'procedure')
 GROUP BY fet.type_name, fes.text
 ORDER BY type_name DESC;

-- Browse concepts by semantic type
SELECT fet.ontology AS type_ontology, fet.type_name, c.ontology AS concept_ontology, c.description, count(*) AS total
  FROM concept c
          JOIN foundentityconcept fec
          ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology
          JOIN foundentitytype fet
          ON fet.found_entity_id = fec.found_entity_id
WHERE c.ontology = 'snomed'             -- any source ontology for Concepts interest
  AND fet.ontology = 'umls'             -- any ontology for the semantic type categorization of interest
GROUP BY fet.ontology, fet.type_name, c.ontology, c.description
ORDER BY total DESC;
```