> 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. # Document-level Tables In this section, we explain how the NLP API stores information about report documents and how it stores information about where extracted entities are located within documents. We will also provide sample queries to illustrate how to retrieve this information. After reading this page, you will be able to: 1. Find the report category and subcategory for extracted entities and concepts 2. Search for metadata about source reports such as chart date and source report type 3. Retrieve report text from specific sentences and sections where entities and concepts are detected Before attempting to run the queries in this section, we assume that you have reviewed the schema representation of Entities, Concepts and Entity Types. These fundamentals are explained in the [Entity-level Tables](/nlp/database/schema/core/entity-tables) section. ## Document Tables When the NLP API's entity recognition system extracts an entity, it records the location where the annotated term associated with an entity was found, in addition to the entities' attributes. The Output Database stores sufficient information to recover the context where entities are found at multiple levels of specificity: 1. Document-level: NLP API stores the document attributes, and the report's text in its entirety 2. Section-level: NLP API stores the span of the specific section of a report where an entity was found as character offsets 3. Sentence-level: NLP API stores the span of the specific sentence in a document as character offsets 4. Term-level: The span of the annotated term within the document text as character offsets Each occurrence of a term is mapped to a single instance of an entity. The same disorder term in multiple documents will yield separate entity instances. For example, when the term `diabetes` is mentioned once in ten separate documents, ten entity instances are generated. Likewise, if a disorder term `diabetes` appears twice in a report, two entities are generated. It should be noted, however, that each entity instance will be associated with multiple locations. This is because the NLP API stores a **location** for both the **sentence**, and the **section** in which the **sentence** is found. Hence, entities can have `one-to-many` relations with locations. Each location, however, refers to exactly one document where the entity is found. The ER diagram below shows how found entities relate to documents: ![Join tables model the relation between foundentity and documents](/_fern-files/emtelligent-prod.docs.buildwithfern.com/e85cd2375a6684c68aa180eefe0b72b8f32c7ccae11ac5dc9f3a5636c1cf25da/assets/nlp/foundentity-document.png) The ER diagram above illustrates the following key points: * The **foundentity** table is related to the **document** table via two join tables: **foundentitylocation** and **location**. * The **location** table identifies the **document id** where the entity was found. * The *type\_* column indicates whether the location specified is the location of the `sentence` or the location of the `section` for a given entity. This is discussed in more detail later, see [Location Tables](#location-tables). * The **document** table contains the following useful data: * The **category** and **subcategory** columns store the report category and subcategory parameters provided in the processing request. See the NLP API reference for list of supported processing parameters. * The **text** column contains the text content of a report in its entirety. Note, this column is populated if the `--store-reports` feature was enabled during processing. * The **filename** column contains information about the ingestion of the report, which is usually the file path when a file path was used to submit data. * The **section_label** column contains the argument provided using `--section-label` processing option when this option is used. This optional processing flag instructs the NLP API to extract entities from a specific section of the medical reports that were submitted for processing. For more information about using the `--section-label` refer to the NLP API Python SDK documentation [here](/nlp/python/database-client/options). * The **json_representation** column contains the NLP API JSON output. It is populated if the processing option `--store-json` was enabled during processing. * The **processing_status** column contains `success` or `error`. A row is inserted in this table for each successfully processed document so that the value of this column is always `success`. Note that documents that failed to be processed are **not** stored. However, when the `--store-failed` processing flag is used, documents that failed will have rows inserted in this table with the **processing_status** column set to `error`. Note: `--store-failed` is available in Python SDK, v5.21 and higher. * The **foundentityspan** table does not relate to location or document tables, but is notable in that in addition to storing the annotated text (in the **text** column), this table also stores the character offsets that specify the text span of annotated text within the report text. In the following sections, we provide examples of how to use the information stored in this table. ### Query: Distribution of entities by report category and section This example shows how to find the document category from the **document** table for an annotated term, for example, `aneurysm`. The query involves performing inner joins on the following tables: **foundentity**, **location** and **document**. ```sql SELECT d.category, fe.section_name, count(*) AS frequency FROM foundentity fe -- contains section name for entity JOIN foundentitylocation fel ON fe.id = fel.found_entity_id JOIN location l ON fel.location_id = l.id AND l.type_ = 'sentence' JOIN document d -- contains information about the document's category and subcategory ON l.document_id = d.id WHERE fe.text = 'aneurysm' -- annotated term of interest GROUP BY d.category, fe.section_name ORDER BY 1 LIMIT 5; ``` | category | section_name | frequency | | -------- | ----------------- | --------- | | Clinical | RADIOLOGY/IMAGING | 2 | ### Query: Distribution of concepts by report category This query shows how to relate concepts stored in the **concept** table with document categories stored in the **document** table using multiple joins as shown below. ```sql SELECT DISTINCT d.id as doc_id, date(dm.chartdate) AS report_date, dm.original_category AS report_category, dm.original_subcategory AS report_subcategory, dm.description AS report_description, c.description AS ct_type 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 AND c.ontology='snomed' JOIN foundentitylocation fel ON fe.id = fel.found_entity_id JOIN location l ON fel.location_id = l.id AND l.type_ = 'sentence' JOIN document d ON l.document_id = d.id JOIN documentmetadata dm ON d.id = dm.document_id -- filter for entites that map to these concepts WHERE c.concept_id IN ( '169070004', -- Snomed CT concept `Computed tomography of abdomen (procedure)` '419394006', -- Snomed CT concept `Computed tomography of abdomen and pelvis (procedure)` '241580002', -- Snomed CT concept `Computed tomography of lumbar spine (procedure)` '702521009') -- Snomed CT concept `Computed tomography of lumbar spine and pelvis (procedure)` AND fe.section_name = 'RADIOLOGY/IMAGING' -- limit to entities in one section AND age(dm.chartdate) <= INTERVAL '5 year'; ``` | doc_id | report_date | report_category | report_subcategory | report_description | ct_type | | ------ | ----------- | --------------- | ------------------ | ------------------ | ------------------------------------------ | | 68 | 2022-03-13 | Clinical | Discharge summary | Discharge Summary | Computed tomography of abdomen (procedure) | | 69 | 2022-03-13 | Clinical | Discharge summary | Discharge Summary | Computed tomography of abdomen (procedure) | ## Document Attribute Tables While the **document** table contains several useful attributes, the NLP API also supports the storage of additional document-related metadata in the following tables: * **documentmetadata** table * **processingdetails** table The **documentmetadata** table, can be used to store additional metadata about source documents. This table is populated during database upload step, after the document submission and processing steps ensuring that the metadata remains local. For instructions see [Storing Extended Report Metadata](/nlp/python/database-client/overview#storing-extended-report-metadata). By storing additional metadata such as the chart date, admission id and optionally subject ids, users can perform more meaningful queries on the dataset without having to perform cross-database joins or implement additional ETL steps in their data pipelines. The following ER diagram illustrates the relation between the **document** table and the **documentmetadata** and **processingdetails** tables. ![Tables that store additional per document attributes](/_fern-files/emtelligent-prod.docs.buildwithfern.com/ccbaa236f3ec8f0c1b60d82b010be5f438ba811dcf501f23db19cf2a09346598/assets/nlp/document-attribute-tables.png) The **processingdetails** table stores the following information: > * The **submit_timestamp** column stores the date and time when the report was sent to NLP API for processing. > * The **job_id** column stores the human-readable label which is an optional parameter provided in the processing request. > * The **task_id** column stores the unique identifier for each processing request. All documents submitted in a request will have the same task id. > * The **engine_version** column stores the version number of the NLP API server that completed the processing request. > * The **sdk_version** column stores the client version of the Output Database Client that submitted the processing request. This information can be used by emtelligent to retrieve non-PHI information about a request such as the ID of the requesting account, and metrics for the request such as the status, size and duration of the workload. The information in the **processingdetails** may also be useful for data operations managers who need to retrieve data processing statistics. ### Query: Find information about documents that failed The `emtellipro-db-client` allows users to submit a batch of files in a single processing request. Using Python SDK v5.21 and higher, you can enable the `--store-failed` processing option to store information about the documents in the batch which failed to be processed. When this flag is enabled, each document that failed will have **processing_status** column set to `error` in the **document** table, and corresponding row in the **documentmetadata** table. This behaviour useful for tracking processing errors, allowing you to review or resubmit the failed documents. This query retrieves the **filename**, **chartdate**, and **submit time** for files that failed to be processed by accessing this information from the **document**, **documentmetadata** and **processingdetails** tables respectively. > **Note** > > An empty result is the expected outcome on a healthy corpus: it means no document in the batch failed to process. The table below is empty for that reason. ```sql SELECT regexp_replace(d.filename, '.*/', '') AS filename, dm.chartdate, p.submit_timestamp, d.processing_status FROM document d JOIN processingdetails p on d.processing_details_id = p.id JOIN documentmetadata dm on d.id = dm.document_id WHERE d.processing_status = 'error'; ``` *This query returns no rows against the sample database.* ### Query: List sections by report category This query is useful when your dataset contains different types of reports and you want to find the section headings that are present in all report types. This query has two parts: 1. First, use a common table expression to generate an in-memory table containing unique pairs of report category and section names. 2. Then, aggregate the headings into a comma-delimited list for each report category using the string aggregation function, grouping by report category. ```sql WITH sections_by_report AS (SELECT d.category, fe.section_name FROM foundentity fe JOIN foundentitylocation fel ON fe.id = fel.found_entity_id JOIN location l ON fel.location_id = l.id JOIN document d ON l.document_id = d.id GROUP BY d.category, fe.section_name ORDER BY d.category) SELECT sections_by_report.category, string_agg(sections_by_report.section_name, ', ') AS section_headings FROM sections_by_report GROUP BY sections_by_report.category; ``` | category | section_headings | | ---------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | Cardiology | FINDINGS, TECHNIQUE, INDICATION, INTRO, COMPARISON, 2-D M-MODE, 2-D STUDY, IMPRESSION, FUNCTION | | Clinical | ASSESSMENT AND PLAN, ASSESSMENT, FAMILY HISTORY SECTION, ALLERGIES, DISCHARGE MEDICATIONS SECTION, MEDICATIONS ADMINISTERED SECTION, PRESENTING COMPLAINT, PAST MEDICAL HISTORY, RESULTS SECTION, ECG, MEDICATIONS, ASSESSMENT SECTION, REVIEW OF SYSTEMS SECTION, HISTORY OF PRESENT ILLNESS SECTION, ADMI… | | Gastroenterology | PLAN, ANESTHESIA, INDICATION, PROCEDURE, COMPLICATIONS, MEDICATIONS, ESTIMATED BLOOD LOSS, HISTORY, IMPRESSION, FINDINGS, PROCEDURE NOTE | | Pathology | 2) BIOPSIES OF BODY, BIOPSY OF CERVIX 1 O'CLOCK, SPECIMEN, MICROSCOPIC DESCRIPTION, CLINICAL INFORMATION, DIAGNOSIS, ADDENDUM, COMMENTS, GROSS/MACROSCOPIC DESCRIPTION | | Radiology | COMPARISON, IMPRESSION, INDICATION, FINDINGS, INTERPRETATION, SCAPULA, TECHNIQUE, COMPARISION | | Surgical | MEDICATIONS, COMPLICATIONS, INDICATION, ESTIMATED BLOOD LOSS, OPERATION, FINDINGS, ANESTHESIA, PROCEDURE, CONSENT, PROCEDURE NOTE, POSTOPERATIVE DIAGNOSIS, PREOPERATIVE DIAGNOSIS | ### Query: Find in last 5 years of reports This query is an example of how to use document metadata to find a subset of reports. This query uses the **chartdate** column in the **documentmetadata** table to filter for reports created within the last 5 years. ```sql SELECT DISTINCT d.id as doc_id, date(dm.chartdate) AS report_date, dm.original_category AS report_category, dm.original_subcategory AS report_subcategory, dm.description AS report_description, c.description AS ct_type 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 JOIN foundentitylocation fel ON fe.id = fel.found_entity_id JOIN location l ON fel.location_id = l.id AND l.type_ = 'sentence' JOIN document d ON l.document_id = d.id JOIN documentmetadata dm ON d.id = dm.document_id -- filter for entites that map to these concepts WHERE c.concept_id IN ( '169070004', -- Snomed CT concept `Computed tomography of abdomen (procedure)` '419394006', -- Snomed CT concept `Computed tomography of abdomen and pelvis (procedure)` '241580002', -- Snomed CT concept `Computed tomography of lumbar spine (procedure)` '702521009') -- Snomed CT concept `Computed tomography of lumbar spine and pelvis (procedure)` AND fe.section_name = 'RADIOLOGY/IMAGING' -- limit to entities in one section AND age(dm.chartdate) <= INTERVAL '5 year'; ``` | doc_id | report_date | report_category | report_subcategory | report_description | ct_type | | ------ | ----------- | --------------- | ------------------ | ------------------ | ------------------------------------------ | | 68 | 2022-03-13 | Clinical | Discharge summary | Discharge Summary | Computed tomography of abdomen (procedure) | | 69 | 2022-03-13 | Clinical | Discharge summary | Discharge Summary | Computed tomography of abdomen (procedure) | ## Location Tables The previous sections explained how to access the document-level tables and attributes from the **foundentity** table. This section explains how to retrieve the location of the entities, their character offsets and associated text. In the Output Database, you can find the offsets of sections, sentences and annotated terms. The following are important to note about offsets: * Offsets are specified relative to the first character of the document. * Offset are specified using a zero-based index. * Offsets are extracted as a half-open interval `[start, end)` such that: * The first character of the term is the **start offset** * The last character of the term is **end offset minus one** Offsets are found in these tables: * **foundentityspan**: The **foundentityspan** table stores start and end offsets for the annotated term relative to the start of the document. See [FoundEntitySpan Table](#foundentityspan-table). * **sectionlocationspan**: The **sectionlocationspan** table contains start and end offsets of the section of the report where entities were found. The start and end offsets in the **sectionlocationspan** encompass the section heading and all sentences in the report section. * **sentencelocationspan**: The **sentencelocationspan** table gives the offsets of the first and last character of sentences where entities were found. The following ER diagram illustrates the relation between the **sectionlocationspan** and **sentencelocationspan** tables and the **location** table: ![Relation between location and locationspans for sections and sentences](/_fern-files/emtelligent-prod.docs.buildwithfern.com/2391ddfbd50fdd1e956b7c180e7b1bc521aac5ebcb3fccc445538e042dc57cc2/assets/nlp/location_spans.png) In the ER diagram above, notice the following key points: * The **type\_** column in the **location** table is used to indicate whether the **location_id** refers to the location of a report section or a sentence as follows: * When location **type\_** is `section`, the location id references a row in the **sectionlocation** table * When location **type\_** is `sentence`, the location id references a row in the **sentencelocation** and **sentencelocationspan** table. * The **text** column in the **sentencelocation** table stores the text for a sentence or null. * This column is populated only if the `--store-sections-and-sentences` processing option was enabled during processing. * This column is indexed for full-text searches. In posgreSQL, this column is indexed using GIN (Generalized Inverted Index)-based index. In MySQL, this column is indexed using the FULLTEXT index. * The **sectionlocation** table is used to support report document formats that have nested or multi-level section headings. * The **sectionlocation** table has a **parent_id** column which is a foreign key reference to itself. When the section is a top-level section, the **parent_id** is `null`. * The **sectionlocation.text** column is indexed to support fast full-text searches. > **Warning** > > Because sections comprise of sentences, and entities are found in sentences, each entity has two entries in the **location** table: one refers to the location of the **section**, and the other refers to the location of the **sentence** in which they were found. It is therefore essential that when you perform an inner joins with the **location** table, that you **specify the location type** in addition to the **location_id** in the JOIN on clause. The location type is stored in the **type\_** column and can have a value of `sentence` or `section`. The following queries show how to find text spans for entity and concept occurrences. ### Query: Retrieve sentences for a given Concept Given a SNOMED CT concept for `Spleen` which has a concept ID of `78961009`, how can we find the sentences that mentions this concept? The approach involves finding all entities that are mapped to this concept and looking up the location id for each entity that was extracted. From the location id, you can retrieve the associated sentence in one of two ways: * Using the **sentencelocation** table * Using the **sentencelocationspan** table. First retrieve the start and end offsets for the sentence containing the entity from the **sentencelocationspan** table. Then, use the offsets to retrieve the sentence from the document text column in the **document** table. > **Warning** > > While there are 2 ways to retrieve sentences, we recommend that you retrieve sentence text from the **sentencelocation** table. #### Using the **sentencelocation** table If the `--store-sections-and-sentences` option was enabled during processing, you can retrieve sentences from the **text** column of the **sentencelocation** table as shown: ```sql SELECT fe.section_name, fe.text AS term, sl.text AS sentence 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 JOIN foundentitylocation fel ON fe.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 WHERE c.concept_id = '78961009' -- SNOMED CT concept id for 'Spleen' ORDER BY 1 LIMIT 3; ``` | section_name | term | sentence | | ------------ | ------ | ----------------------------------------------------------------------------------------------------- | | FINDINGS | spleen | The spleen is normal. | | FINDINGS | spleen | Given the lack of contrast, liver, spleen, adrenal glands, and the pancreas are grossly unremarkable. | | FINDINGS | spleen | The liver, gallbladder, pancreas, spleen, adrenal glands, and kidneys are within normal limits. | #### Using the **sentencelocationspan** table First get the start and end offsets for sentences within a document from the **sentencelocationspan** table. Then, use the offsets to retrieve the sentence string using the `substr` function as shown in the following SQL query. Note, because we are performing a JOIN with the **document** table we can also retrieve data from other columns in this table, such as the documents' **category** and **subcategory** values, to provide more context in the query results. ```sql SELECT d.category, d.subcategory, fe.section_name, fe.text, substr(d.text, sls.start + 1, (sls.end - sls.start)) AS sentence 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 JOIN foundentitylocation fel ON fe.id = fel.found_entity_id JOIN location l ON fel.location_id = l.id AND l.type_ = 'sentence' JOIN document d -- document table contains report text ON l.document_id = d.id JOIN sentencelocationspan sls -- sentencelocationspan stores offsets -- IMPORTANT: check that location type is 'sentence' ON l.location_id = sls.sentence_location_id AND l.type_ = 'sentence' WHERE c.concept_id = '78961009' -- SNOMED CT concept 'Splenic structure (body structure)' ORDER BY 1 LIMIT 3; ``` | category | subcategory | section_name | term | sentence | | --------- | ----------------- | --------------------------------- | ------ | ----------------------------------------------------------------------------------------------- | | Clinical | Discharge Summary | PROCEDURES AND TREATMENT PROVIDED | spleen | The liver, pancreas, spleen, adrenal glands and kidneys are within normal limits. | | Radiology | CT | FINDINGS | spleen | The spleen is normal. | | Radiology | CT | FINDINGS | spleen | The liver, gallbladder, pancreas, spleen, adrenal glands, and kidneys are within normal limits. | ### Query: Retrieve a Sentence using Full-text Search The NLP API schema sets up indexes on the **sentencelocation.text** and **sectionlocation.text** columns to allow you to perform fast full-text searches on the contents of these fields. This capability is very useful when you want to explore the extracted data without being constrained by the underlying coding of terms or combination of terms to concepts. With this capability, you can easily find any combination of words in sections and sentences. In this example, we want to find occurrences of `corner fracture`. While the term `fracture` is a SNOMED CT concept, the compound term `corner fracture` is not. To perform a full-text search for `corner fracture`, you may use the pattern-matching operator and wildcards supported on your RDBMS. For example on postgreSQL, you can use `ILIKE`, the case-insensitive pattern-matching operator. However, for much faster query execution, you can leverage the GIN index that is set up on the **sentencelocation.text** and **sectionlocation.text** columns as shown below. ```sql SELECT id AS sentence_id, text FROM sentencelocation as sl WHERE to_tsvector('simple', sl.text) @@ phraseto_tsquery('simple', 'corner fracture'); ``` | sentence_id | text | | ----------- | --------------------------------------------------------------------------------------------------------------------------- | | 23144 | Small C7 superanterior corner fracture extending into the right foramen transversum. | | 68415 | Small linear translucency in the posterosuperior aspect of the C4 vertebral body suggesting a nondisplaced corner fracture. | The query execution time is very fast (less than 2 ms) as shown in the query plan results below, fast enough for interactive applications. Note: this query was profiled using a data set generated from processing approximately 5000 reports. ```sql EXPLAIN ANALYZE SELECT id AS sentence_id, text FROM sentencelocation as sl WHERE to_tsvector('simple', sl.text) @@ phraseto_tsquery('simple', 'corner fracture'); +-------------------------------------------------------------------------------------------------------------------------------------+ |QUERY PLAN | +-------------------------------------------------------------------------------------------------------------------------------------+ |Bitmap Heap Scan on sentencelocation sl (cost=456.01..464.33 rows=2 width=85) (actual time=1.725..1.807 rows=2 loops=1) | | Recheck Cond: (to_tsvector('simple'::regconfig, text) @@ '''corner'' <-> ''fracture'''::tsquery) | | Rows Removed by Index Recheck: 5 | | Heap Blocks: exact=6 | | -> Bitmap Index Scan on ix_sentencelocation_text_gin (cost=0.00..456.01 rows=2 width=0) (actual time=1.655..1.655 rows=7 loops=1)| | Index Cond: (to_tsvector('simple'::regconfig, text) @@ '''corner'' <-> ''fracture'''::tsquery) | |Planning Time: 0.169 ms | |Execution Time: 1.827 ms | +-------------------------------------------------------------------------------------------------------------------------------------+ ``` > **Warning** > > We recommend you use the `simple` instead of `english` text search configuration. The `simple` text search configuration retains stop words in the query expression. It is important not to omit stopwords because some medical concepts might contain informative stop words and some HGNC gene names are stop words. The query plan results for the equivalent querying that uses `ILIKE` operator shows that the query optimizer is unable to use the GIN index. The leading `%` wildcard symbol in the search expression prevents the query optimizer from using the index and results in longer execution times. The execution time in this case is approximately 100x slower than the previous query on the same dataset. ```sql EXPLAIN ANALYZE SELECT id AS sentence_id, text FROM sentencelocation as sl WHERE text ILIKE '%corner fracture%'; +-------------------------------------------------------------------------------------------------------------------+ |QUERY PLAN | +-------------------------------------------------------------------------------------------------------------------+ |Seq Scan on sentencelocation sl (cost=0.00..3034.66 rows=11 width=85) (actual time=38.837..204.792 rows=2 loops=1)| | Filter: (text ~~* '%corner fracture%'::text) | | Rows Removed by Filter: 113076 | |Planning Time: 0.376 ms | |Execution Time: 204.833 ms | +-------------------------------------------------------------------------------------------------------------------+ ``` ### Query: Combining Full-text Search and Assertions about Entities A powerful approach for exploring the structured output extracted by the NLP API is to use full-text search in combination with entity-based search. In the previous example query, we searched for occurrences of `corner fracture`. While `corner fracture` does not map to any SNOMED CT concepts, `fracture` is a SNOMED CT concept and occurrences of `fracture` are therefore extracted as entities in the Output Database. The NLP API extracts the entity attributes, **polarity**, **uncertainty** and **section name**, for every occurrence of `fracture`. These attributes of `fracture` would also apply to the compound term `corner fracture` by inference. This query involves using postgreSQL full-text search functions to find sentence location id for sentences containing the phrase of interest. Given a sentence location id, we then find the **foundentities** associated with the sentence location. As a sentence will typically contain many recognized terms, we must then filter the entities to find only those that represent the term `fracture`. ```sql SELECT sl.id AS sentence_locid, sl.text AS sentence, fe.text AS annotated_term, fe.polarity, fe.uncertainty, fe.section_name FROM sentencelocation sl JOIN location l -- JOIN with location table to look up the location_id ON l.location_id = sl.id AND l.type_ = 'sentence' -- IMPORTANT: check that location type is 'sentence' JOIN foundentitylocation fel -- JOIN with foundentitylocation to look up the foundentity ON l.id = fel.location_id JOIN foundentity fe ON fel.found_entity_id = fe.id WHERE to_tsvector('simple', sl.text) @@ phraseto_tsquery('simple', 'corner fracture') -- or text ILIKE '%corner fracture% (if using older emtelliPro DB schema version and GIN index is not set up); AND fe.text = 'fracture' ORDER BY sl.id LIMIT 2; ``` | sentence_locid | sentence | annotated_term | polarity | uncertainty | section_name | | -------------- | --------------------------------------------------------------------------------------------------------------------------- | -------------- | -------- | ----------- | ------------ | | 23144 | Small C7 superanterior corner fracture extending into the right foramen transversum. | fracture | asserted | certain | IMPRESSION | | 68415 | Small linear translucency in the posterosuperior aspect of the C4 vertebral body suggesting a nondisplaced corner fracture. | fracture | asserted | uncertain | FINDINGS | ### Query: Retrieve section contents Given a SNOMED CT concept for `Spleen` which has a concept ID of `78961009`, how can we find the **section text** that mentions this concept? There are two approaches you can use: * Use the **sectionlocation** table. The **text** column in this table stores the section text. * Use the **sectionlocationspan** table. First retrieve the start and end offsets for the section containing the entity of interest from the **sectionlocationspan** table. Then, use the offsets to retrieve the sentence from the report text stored in the **document.text** column. > **Warning** > > While there are 2 ways to retrieve sections, we recommend that you retrieve section text from the **sectionlocation** table. #### Using the **sectionlocation** table If the `--store-sections-and-sentences` option was enabled during processing, you can retrieve section text from the **text** column of the **sectionlocation** table. The text stored in this column includes the section heading and all the paragraphs that were extracted from report section. > **Note** > > The section headings in the section_text do not exactly match the section names stored in the **section_name** column of the **foundentity** table. For example, you may see a heading like 'Reason for exam' in the raw text but notice that the value stored in the **section_name** of the **foundentity** table is `INDICATION` instead. This is the case because the NLP API normalizes section headings that it recognizes to the equivalent standardized section names. The following query shows how retrieve section text from all sections where the SNOMED CT concept `Splenic structure` was identified: ```sql SELECT fe.section_name, c.description, sl.text AS sentence 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 JOIN foundentitylocation fel ON fe.id = fel.found_entity_id JOIN location l ON fel.location_id = l.id AND l.type_ = 'section' -- Important: check that location type is 'section' JOIN sectionlocation sl ON sl.id = l.location_id WHERE c.concept_id = '78961009' -- SNOMED CT concept 'Splenic structure (body structure)'; ``` | section_name | description | sentence | | ------------ | ---------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | FINDINGS | Splenic structure (body structure) | FINDINGS: There is evidence of diffuse hepatic hypoattenuation compatible with fatty infiltration. In segment IVb of the liver, there is a 2.3 cm lesion which is just focal fat. There is no ductal dilatation. The patient is status post cholecystectomy. The spleen is normal. The pancreas is of norma… | | FINDINGS | Splenic structure (body structure) | FINDINGS: Again identified are small intrarenal stones bilaterally. These are unchanged. There is no hydronephrosis or significant ureteral dilatation. There is no stone along the expected course of the ureters or within the bladder. There is a calcification in the low left pelvis not in line with … | #### Using the **sectionlocationspan** table The following query shows how to use the `substr` function to extract the span of text from the report text using offsets retrieved from the **sectionlocationspan** table. ```sql SELECT substr(d.text, sls.start + 1, (sls.end - sls.start)) AS section_text FROM location l JOIN document d ON l.document_id = d.id -- document table contains report text JOIN sectionlocation s ON l.location_id = s.id AND l.type_ = 'section' -- IMPORTANT: check that location type is 'section' JOIN sectionlocationspan sls ON s.id = sls.section_location_id -- sectionlocationspan table contains offsets WHERE l.type_ = 'section' -- filter for section locations only ORDER BY 1 LIMIT 5; ``` | section_text | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | 1) BIOPSIES OF DUODENUM: - Within normal limits. - NEGATIVE for celiac disease. - Villous height is preserved; no active inflammation or parasites are seen. | | 2) BIOPSIES OF BODY-TYPE AND ANTRAL MUCOSA: - Chronic gastritis. - POSITIVE for H. pylori organisms. - NEGATIVE for intestinal metaplasia or dysplastic change. | | 2-D M-MODE: 1. Left atrial enlargement with left atrial diameter of 4.7 cm. 2. Normal size right and left ventricle. 3. Normal LV systolic function with left ventricular ejection fraction of 51%. 4. Normal LV diastolic function. 5. No pericardial effusion. 6. Normal morphology of aortic valve, mitr… | | 2-D STUDY: 1. Mild aortic stenosis, widely calcified, minimally restricted. 2. Mild left ventricular hypertrophy but normal systolic function. 3. Moderate biatrial enlargement. 4. Normal right ventricle. 5. Normal appearance of the tricuspid and mitral valves. 6. Normal left ventricle and left vent… | | A: 1. Atrial fibrillation. 2. Dizziness. 3. Recent wrist fracture. | ## FoundEntitySpan Table The **foundentityspan** table contains the annotated text for the entity in the **text** column. It also contains the span offsets for annotated terms in the `start` and `end` columns. Offset of a span refers the character offsets relative to the start of the document. For details about offsets see [Location Tables](#location-tables). There is a **one-to-many** relation between the **foundentity** and **foundentityspan** table. This is because an entity may consist of multiple terms that are discontinuous (i.e separated by other words). Consider this sentence: ```text The patient also reports two weeks of occasional dull cranial occipital pain. ``` Two terms `dull` and `pain` are separated by the descriptors `cranial occipital`. But, these three entities and corresponding concepts are extracted: 1. `Dull (qualifier value) | 263744001` 2. `Pain (finding) | 22253000` 3. `Dull pain (finding) | 83644001` The entity for `Dull pain (finding) | 83644001` consists of two annotated terms and therefore has two rows in the **foundentityspan** table. If you wish to retrieve the span offsets for these terms, you must take this into consideration. Note, however, that most entities have only one annotated term, and discontinous terms are a minority. ### Working with Spans You may need to retrieve spans of annotated terms for various purposes such as: \* Bookmarking the location of an entity. If this is the case, it may suffice to simply use the **foundentity** tables `start` and `text` columns. \* Highlight an entity and all its annotated terms. In this case you must use the **foundentityspan** table to find the `start` and `end` offsets of all the associated terms. ### Query: Retrieve span offsets for entities The following query shows how to retrieve span offsets taking into consideration the one-to-many relation between entities and annotated terms. The query uses window functions to aggregate the `start` and `end` offsets of discontinous terms. Two approaches for returning the offsets is provided: \* an array of tuples representing the start and end offsets of the terms \* a combined start and end offset of both terms ```sql WITH collated_terms AS ( SELECT fes.found_entity_id AS found_entity_id, c.description AS concept, -- string_agg(fes.text, ' | ') AS collated_terms, -- for illustrative purpose only fe.text AS annotated_term, count(*) AS term_counts, /* extents of the discontinuous term offsets */ min(fes.start) AS min_offset, max(fes.end) AS max_offset, /* return an array of two-element int[] “tuples” {{start,end}} */ array_agg(ARRAY [fes.start, fes.end] ) AS span_pairs FROM foundentity fe JOIN foundentityspan fes On fe.id = fes.found_entity_id 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 AND c.ontology = 'snomed' GROUP BY fes.found_entity_id, c.description, fe.text ) SELECT * FROM collated_terms; ``` | found_entity_id | concept | annotated_term | term_counts | min_offset | max_offset | span_pairs | | --------------- | -------------------------------------------------- | -------------------------- | ----------- | ---------- | ---------- | ------------------------------- | | 98 | Conjunctiva closed (finding) | conjunctiva closed | 2 | 1386 | 1408 | \[\[1386, 1397], \[1402, 1408]] | | 252 | Acute inflammation (morphologic abnormality) | active inflammation | 2 | 922 | 955 | \[\[922, 928], \[943, 955]] | | 253 | Hiatal hernia (disorder) | esophagus hiatal hernia | 2 | 433 | 570 | \[\[433, 442], \[557, 570]] | | 475 | Normal vital signs (finding) | stable vital signs | 2 | 1329 | 1378 | \[\[1372, 1378], \[1329, 1340]] | | 476 | Topical local anesthetic to oropharynx (procedure) | oropharynx anesthetized | 2 | 1420 | 1447 | \[\[1420, 1430], \[1435, 1447]] | | 746 | Steatosis of liver (disorder) | hepatic fatty infiltration | 2 | 181 | 239 | \[\[181, 188], \[221, 239]] | > **Note** > > The **text** column in the **foundentityspan** table will be `null` if the `text` data extraction feature was not enabled during processing. In general, it is recommended that you always enable the `text` feature when processing reports.