Find coded Physical Quantities in CCDs

WITH coded_data AS (
SELECT DISTINCT d.id as docid, regexp_replace(d.filename, '.*/', '') AS filename,
                dm.value::jsonb ->> 'class_code' as class_code,
                dm.value::jsonb ->> 'text' as sentence,
                dm.value::jsonb               as json
FROM document d
        JOIN documentstructuredmetadata dm ON d.id = dm.document_id
),
testresults AS (
SELECT res_cd.docid,res_cd.filename             as filename,
    sec_cd.json ->> 'display_name'              as section,
    res_cd.json ->> 'display_name'              as display_name,
    res_cd.json ->> 'text_id'                   as text_id,
    res_cd.json ->> 'label'                     as label,
    res_cd.json ->> 'code_system_name'          as code_system_name,
    res_cd.json ->> 'code_system'               as code_system,
    res_cd.json ->> 'code'                      as code,
    res_cd.json #>> '{value,value}'             as value,
    res_cd.json #>> '{value,unit}'              as unit,
    res_cd.json #>> '{value,type}'              as value_type,
    res_cd.sentence,
    res_cd.json
FROM coded_data res_cd
LEFT JOIN coded_data sec_cd ON
        --> Get section display_name from section entries
    res_cd.json ->> 'section' = sec_cd.json ->> 'label' AND
        sec_cd.json ->> 'for_elt_tag' = 'section'
WHERE res_cd.json #>> '{value,type}'='PQ' AND res_cd.json ->> 'code_system_name' = 'LOINC'),
interpretations AS (
    --> Get interpretationCode display_name from interpretation entries
    SELECT json ->> 'display_name' as display_name,
        json ->> 'text_id' as text_id,
        json ->> 'parent_reference' as parent_reference,
        json ->> 'code_system_name' as code_system_name,
        json ->> 'code_system' as code_system,
        json ->> 'code' as code,
        json
FROM coded_data
WHERE json ->> 'code_elt_tag' = 'interpretationCode'
)
SELECT tr.docid, tr.filename, tr.section AS section_display_name, tr.text_id,
    tr.display_name, tr.code_system_name, tr.code_system, tr.code,
    tr.value,tr.unit, tr.value_type,
    interp.code AS interpret_code,
    interp.code_system AS interpret_code_system,
    tr.sentence
FROM testresults tr
LEFT JOIN interpretations interp ON tr.label = interp.parent_reference
ORDER BY tr.docid, tr.section, tr.text_id;