Find Entities in CCD Sections which have no Coded data

Synopsis

This query shows how to retrieve the NLP API entities from sections in a C-CDA Document that have no coded entries.

This query finds sections in a C-CDA Document that have no XML entries of interest such as Observations (OBS class), Procedures (PROC class), or Immunization events (SBADM class). It then retrieves disorders and procedure SNOMED entities that are positively asserted from these sections. This is useful when we want to avoid retrieving entities from sections containing information you may have access to from sources that manage or author structured information (e.g. an EHR or other CDA document management systems )

To find such sections, we will need information from 3 sources:

The coded entries found in C-CDA sections,
entity information extracted by the NLP API
A mapping table that maps C-CDA sections to normalized section names (using section Object Identifiers or LOINC codes )

Processing Requirements

For this query, Continuity of Care Documents were processed. See processing requirements Processing Requirements.

Prerequisites

This query uses a mapping table containing mapping of section OIDs to NLP API section names. This table must be added to your database as it is not part of the Output Database. The following shows an excerpt of this mapping table:

Excerpt of Section Mapping Table

To do this, you will need to:

Create a CSV file containing the contents of the mapping of Section OID and LOINC codes to NLP API section names. This table is found in the C-CDA Section Mapping page in the Python Client SDK Documentation set. Contact support@emtelligent.com for assistance.
Upload the CSV file to the database that you are using to a table named sectionmap. We will use this table in the query below.

Background

C-CDA documents contain both coded and narrative non-coded information. Coded information refers to the data that is stored in the \<entry\> elements in each C-CDA section. However some sections contain purely narrative notes and no coded entries. This query shows how to selectively retrieve NLP API NLP extracted information from such sections.

The query consists of 4 steps:

Step 1: For each section, find section Object Identifier (OID) or LOINC code and look up the corresponding the NLP API-normalized section name from the sectionmap table
Step 2: Find the coded entries of interest (typically observations and procedures)
Step 3: Find those sections that have no coded entries
Step 4: Finally, get the NLP API entities from those section using thier normalized section names.

SQL query

PostgreSQL

WITH coded_section_info AS (
    -- Step 1: coded sections, with the normalised section name looked up in the
    --         section map (a table you add; it is not built in)
    SELECT d.id                                              AS doc_id,
           regexp_replace(d.filename, '.*/', '')             AS filename,
           m.value::jsonb ->> 'display_name'                 AS loinc_displayname,
           sm.section_name                                   AS emtellipro_section_name,
           m.value::jsonb ->> 'label'                        AS section_label
      FROM document d
      JOIN documentstructuredmetadata m ON d.id = m.document_id
      LEFT JOIN LATERAL jsonb_array_elements(
               coalesce(m.value::jsonb -> 'template_id', '[]'::jsonb)) AS tid(elem) ON true
      JOIN sectionmap sm
        ON m.value::jsonb ->> 'for_elt_tag' = 'section'
       AND ( (sm.loinc_code = m.value::jsonb ->> 'code'
              AND m.value::jsonb ->> 'code_system_name' ILIKE 'loinc')
             OR tid.elem ->> 'root' = sm.section_oid )
),
coded_obs AS (
    -- Step 2: coded entries of interest -- observations, procedures, immunizations
    SELECT DISTINCT d.id                         AS docid,
                    m.value::jsonb ->> 'section' AS section_label,
                    m.value::jsonb ->> 'code'    AS code
      FROM document d
      JOIN documentstructuredmetadata m ON d.id = m.document_id
     WHERE m.value::jsonb ->> 'code' IS NOT NULL
       AND m.value::jsonb ->> 'class_code' IN ('OBS', 'SBADM', 'PROC')
),
no_obs_sections AS (
    -- Step 3: sections with zero coded entries of interest
    SELECT csi.doc_id, csi.filename, csi.loinc_displayname, csi.emtellipro_section_name
      FROM coded_section_info csi
      LEFT JOIN coded_obs cobs ON cobs.docid = csi.doc_id
                              AND cobs.section_label = csi.section_label
     GROUP BY 1, 2, 3, 4
    HAVING count(cobs.code) = 0
)
-- Step 4: entities emtelliPro found in sections that carry no coded entries
SELECT nos.filename, fe.section_name, fe.polarity, fe.text, c.description
  FROM no_obs_sections nos
  JOIN foundentity fe ON fe.document_id = nos.doc_id
                     AND fe.section_name = nos.emtellipro_section_name
  JOIN foundentityconcept fec ON fec.found_entity_id = fe.id
                             AND fec.concept_ontology = 'snomed'
  JOIN concept c ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology
  JOIN foundentitytype fet ON fet.found_entity_id = fe.id AND fet.ontology = 'snomed'
 WHERE fet.type_name IN ('disorder', 'finding', 'morphologic abnormality', 'procedure')
 ORDER BY nos.filename, fe.section_name, fe.text
 LIMIT 10;

filename	section_name	polarity	text	description
emerge-patient-1.xml	MEDICATIONS SECTION	asserted	Beta Blocker Therapy	Beta adrenergic receptor blocking agent therapy (procedure)
getrealhealth-ccd-e1.xml	ADMISSION DIAGNOSIS SECTION	asserted	Anemia	Anemia (disorder)
getrealhealth-ccd-e1.xml	ASSESSMENT SECTION	asserted	Anemia	Anemia (disorder)
getrealhealth-ccd-e1.xml	ASSESSMENT SECTION	asserted	Anemia	Anemia (disorder)
getrealhealth-ccd-e1.xml	ASSESSMENT SECTION	asserted	Anemia	Anemia (disorder)
getrealhealth-ccd-e1.xml	ASSESSMENT SECTION	asserted	breathlessness	Dyspnea (finding)
getrealhealth-ccd-e1.xml	ASSESSMENT SECTION	asserted	chest pain	Chest pain (finding)
getrealhealth-ccd-e1.xml	ASSESSMENT SECTION	asserted	discharged	Discharged from hospital (finding)
getrealhealth-ccd-e1.xml	ASSESSMENT SECTION	asserted	fever	Fever (finding)
getrealhealth-ccd-e1.xml	ASSESSMENT SECTION	asserted	stable condition	Patient’s condition stable (finding)

References

See Python Client SDK Documentation for more information about CCD file support.