Find Problems with Statuses in Coded Entries

WITH coded_data AS ( SELECT 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
),
problem_entries as (
SELECT 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' AND
                    --> limit to Problem List & History of Past Illness sections
                    ( sec_cd.display_name ILIKE '%problem%' OR
                    sec_cd.display_name ILIKE '%illness%' )
                ) as section
WHERE prob_cd.json:code_elt_tag::string = 'code' AND
        prob_cd.json:for_elt_tag::string = 'observation' AND
        --> limit the observation types to the following
        ( prob_cd.display_name ILIKE '%Problem%' OR
        prob_cd.display_name ILIKE '%Complaint%' OR
        prob_cd.display_name ILIKE '%Diagnosis%' OR
        prob_cd.display_name ILIKE '%Condition%')
),
snomed_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') 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 pe.docid,pe.filename,pe.section AS section_display_name,
    pe.text_id,
    ce.display_name as 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,
    pe.display_name as obs_type,
    sve.value       as status_text,
    pe.sentence
FROM problem_entries pe
LEFT JOIN status_value_entries sve ON pe.label = sve.parent_ref
--> also return those entries which have no coded status
JOIN snomed_concept_entries ce ON pe.label = ce.parent_ref
LEFT JOIN icd10_concept_entries ice ON ce.parent_ref = ice.parent_ref
ORDER BY pe.filename, pe.section, pe.text_id;