Find Hypertension using Hierarchical Concept Search in Coded Entries

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;