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

# Find Hypertension using Hierarchical Concept Search in Coded Entries

## Synopsis

This query searches the coded-entries for occurrences of problem entries that map to `38341003 | Hypertensive disorder, systemic arterial (disorder) |` or the subtypes of hypertension in the SNOMED hierarchy. It also uses the internal relationships within coded-entries, to look up the statuses of the disorder as recorded in the structured elements of the CCD, and returns only those hypertension mentions for which status is given.

## Processing Requirements

For this query, Continuity of Care Documents were processed. See processing requirements [Processing Requirements](/nlp/database/cookbook/advanced-queries/ccd/introduction#processing-requirements).

Additionally, this query also requires the snomed relationships table. This table can be imported into the Output Database following the instructions provided [here](/nlp/database/schema/extensions/snomed-hierarchies).

## Clinical Context

In this case study, mentions of hypertension are present in the `Problem List` section, and have associated statuses (e.g. `Active`, `Resolved` etc. ) which are coded and present in the structured data.

This query uses the query pattern shown in the [Find Problems with Statuses in Coded Entries](/nlp/database/cookbook/advanced-queries/ccd/problem-status) query, and adds a hierarchical concept search for the SNOMED CT concept `38341003 | Hypertensive disorder, systemic arterial (disorder) |`.

Essential hypertension (SNOMED CT `59621000`) is a subtype of `38341003`, so the hierarchical search finds it without it being named explicitly — it is the concept returned in the results below.

## SQL query

#### Snowflake

```sql
WITH RECURSIVE
subtypeids (sourceId) AS (
     -- This recursive CTE returns rows containing subtype SCTIDs
     SELECT r.sourceId
     FROM sct_relationship r
     WHERE r.destinationId = '38341003'               -- concept for 'Hypertension (disorder)'
     AND r.typeId = '116680003'                     -- relationship is a 'is a' relationship
     AND active = 1                               -- relationship is active
     UNION ALL
     SELECT r.sourceId
     FROM sct_relationship r
               INNER JOIN subtypeids ON r.destinationId = subtypeids.sourceid
     WHERE r.active = 1                             -- relationship is active
     AND r.typeId = '116680003' -- relationship is a 'is a' relationship)
),
sctids (sourceId) AS (
     SELECT '38341003' as sourceId -- add parent concept to get the final set of ids to find
     UNION ALL
     SELECT DISTINCT sourceId
     FROM subtypeids
),
coded_data AS ( SELECT dm.id, document_id as docid, regexp_replace(d.filename, '.*/', '') as filename,
               PARSE_JSON(dm.value):label::string as label,
               PARSE_JSON(dm.value):parent_reference::string as parent_ref,
               PARSE_JSON(dm.value):section::string as section_ref,
               PARSE_JSON(dm.value):display_name::string as display_name,
               PARSE_JSON(dm.value):text_id::string as text_id,
               PARSE_JSON(dm.value):text::string as sentence,
               PARSE_JSON(dm.value) as json
          FROM document d
               JOIN documentstructuredmetadata dm ON d.id = dm.document_id
),
coded_entries as (
SELECT prob_cd.id as json_id, prob_cd.docid, prob_cd.filename,
     section.display_name as section,
     prob_cd.label, prob_cd.parent_ref, prob_cd.text_id, prob_cd.display_name,
     prob_cd.sentence
FROM coded_data prob_cd
LEFT JOIN LATERAL (SELECT sec_cd.display_name
                    FROM coded_data sec_cd
                    WHERE prob_cd.section_ref = sec_cd.label AND
                         sec_cd.json:for_elt_tag::string = 'section') as section
WHERE prob_cd.json:for_elt_tag::string = 'observation'
),
snomed_concept_entries as (
SELECT concept_cd.parent_ref                    as parent_ref,
     concept_cd.display_name                  as display_name,
     concept_cd.text_id,
     concept_cd.json:code::string             as code,
     concept_cd.json:code_system::string      as code_system,
     concept_cd.json:code_system_name::string as code_system_name
FROM coded_data concept_cd
WHERE concept_cd.json:code_elt_tag::string IN ('value')
AND concept_cd.json:code_system::string IN ('2.16.840.1.113883.6.96',
                                             '2.16.840.1.113883.6.5')
),
icd10_concept_entries as (
SELECT  DISTINCT concept_cd.parent_ref as parent_ref,
     concept_cd.display_name as display_name,
     concept_cd.json:code::string as code,
     concept_cd.json:code_system::string as code_system,
     concept_cd.json:code_system_name::string as code_system_name
FROM coded_data concept_cd
WHERE concept_cd.json:code_elt_tag::string IN ('value', 'translation') AND
concept_cd.json:code_system::string = '2.16.840.1.113883.6.90'
),
status_value_entries as (
SELECT status_cd.parent_ref,
     status_cd.display_name as status_label, status_value.display_name as value
FROM coded_data status_cd
JOIN LATERAL (SELECT val_cd.display_name FROM coded_data val_cd
               WHERE status_cd.label = val_cd.parent_ref AND
                    val_cd.json:code_elt_tag::string = 'value') as status_value
WHERE status_cd.json:code_elt_tag::string = 'code' AND
     status_cd.display_name ILIKE 'status'
)
SELECT poe.docid,poe.filename,
     poe.section AS section_display_name,
     ce.text_id,
     ce.display_name,
     ce.code_system_name as snomed_system_name,
     ce.code_system as snomed_code_system,
     ce.code as snomed_code,
     ice.code_system_name as icd10_system_name,
     ice.code_system as icd10_code_system,
     ice.code as icd10_code,
     poe.display_name as obs_type,
     sve.value as status_text,
     poe.sentence
FROM coded_entries poe
     LEFT JOIN snomed_concept_entries ce ON poe.label = ce.parent_ref
     LEFT JOIN icd10_concept_entries ice ON ce.parent_ref = ice.parent_ref
     JOIN sctids ON ce.code = sctids.sourceId --> only hypertension snomed entries
     LEFT JOIN status_value_entries sve ON poe.label = sve.parent_ref --> include hypertension mentions with no status
ORDER BY poe.filename, poe.section;
```

#### PostgreSQL

```sql
WITH RECURSIVE
subtypeids (sourceId) AS (
     -- This recursive CTE returns rows containing subtype SCTIDs
     SELECT r.sourceId
     FROM sct_relationship r
     WHERE r.destinationId = '38341003'               -- concept for 'Hypertension (disorder)'
          AND r.typeId = '116680003'                     -- relationship is a 'is a' relationship
          AND active = 1                               -- relationship is active
     UNION ALL
     SELECT r.sourceId
     FROM sct_relationship r
               INNER JOIN subtypeids ON r.destinationId = subtypeids.sourceid
     WHERE r.active = 1                             -- relationship is active
          AND r.typeId = '116680003' -- relationship is a 'is a' relationship)
),
sctids (sourceId) AS (
     SELECT '38341003' as sourceId -- add parent concept to get the final set of ids to find
     UNION ALL
     SELECT DISTINCT CAST(sourceId as varchar(255))
     FROM subtypeids
),
coded_data AS ( SELECT dm.id, document_id as docid, regexp_replace(d.filename, '.*/', '') as filename,
               dm.value::jsonb ->> 'label' as label,
               dm.value::jsonb ->> 'parent_reference' as parent_ref,
               dm.value::jsonb ->> 'for_elt_tag' as for_elt_tag,
               dm.value::jsonb ->> 'code_elt_tag' as code_elt_tag,
               dm.value::jsonb ->> 'section' as section_ref,
               dm.value::jsonb ->> 'display_name' as display_name,
               dm.value::jsonb ->> 'code' as code,
               dm.value::jsonb ->> 'code_system' as code_system,
               dm.value::jsonb ->> 'code_system_name' as code_system_name,
               dm.value::jsonb ->> 'text_id' as text_id,
               dm.value::jsonb ->> 'text' as sentence
          FROM document d
               JOIN documentstructuredmetadata dm ON d.id = dm.document_id
),
obs_entries as (
     SELECT prob_cd.id as json_id, prob_cd.docid, prob_cd.filename,
          section.display_name as section, prob_cd.section_ref,
          prob_cd.label, prob_cd.parent_ref, prob_cd.text_id, prob_cd.display_name,
          prob_cd.sentence
     FROM coded_data prob_cd
     LEFT JOIN LATERAL (SELECT sec_cd.display_name
                         FROM coded_data sec_cd
                         WHERE prob_cd.section_ref = sec_cd.label AND
                              sec_cd.for_elt_tag = 'section') section ON true
     WHERE prob_cd.for_elt_tag = 'observation'
),
concept_entries as (
     SELECT concept_cd.parent_ref                    as parent_ref,
          concept_cd.display_name                  as display_name,
          concept_cd.text_id,
          concept_cd.code                          as code,
          concept_cd.code_system                   as code_system,
          concept_cd.code_system_name              as code_system_name
     FROM coded_data concept_cd
     WHERE concept_cd.code_elt_tag IN ('value')
     AND concept_cd.code_system IN ('2.16.840.1.113883.6.96',
                                        '2.16.840.1.113883.6.5')
     AND concept_cd.code IN (SELECT * FROM sctids) -- Only hypertension-related concepts
),
status_value_entries as (
     SELECT status_cd.parent_ref, status_cd.section_ref,
          status_cd.display_name as status_label, status_value.display_name as value
     FROM coded_data status_cd
     JOIN LATERAL (SELECT val_cd.display_name
                    FROM coded_data val_cd
                    WHERE status_cd.label = val_cd.parent_ref AND
                         val_cd.code_elt_tag = 'value')  status_value ON true
     WHERE status_cd.code_elt_tag = 'code' AND
          status_cd.display_name ILIKE 'status'
)
SELECT poe.docid,poe.filename,
     poe.section AS section_display_name,
     ce.text_id, ce.display_name,
     ce.code_system_name, ce.code_system, ce.code,
     poe.display_name as obs_type,
     sve.value as status_text,
     poe.sentence
FROM obs_entries poe
JOIN concept_entries ce ON poe.label = ce.parent_ref
JOIN status_value_entries sve ON poe.label = sve.parent_ref --> include hypertension mentions with no status
ORDER BY poe.filename, poe.section;
```

| docid | filename             | section_display_name | text_id | display_name           | code_system_name | code_system            | code     | obs_type  | status_text | sentence |
| ----- | -------------------- | -------------------- | ------- | ---------------------- | ---------------- | ---------------------- | -------- | --------- | ----------- | -------- |
| 138   | emerge-patient-1.xml | PROBLEM LIST         | —       | Essential hypertension | SNOMED-CT        | 2.16.840.1.113883.6.96 | 59621000 | Diagnosis | Active      | —        |

## Next steps

In addition to retrieving status descriptors from coded-entries, you may also be interested in searching for other status qualifiers present in the sentence fragments, and comments, that may not be coded by the document generator. In addition, to whether the problem is `Active` or `Resolved`, you may be interested in how well the condition is being managed. An example of how to use SNOMED concept search together with the NLP API qualifier relations to do this, see [Hypertension Control](/nlp/database/cookbook/relation-queries/qualifier/hypertension-control).

## Related links

* [Using SNOMED CT Concept Hierarchies](/nlp/database/schema/extensions/snomed-hierarchies)
* [Introduction](/nlp/database/cookbook/advanced-queries/ccd/introduction)