Find Vaccines & Dates of Administration

WITH coded_data AS (
SELECT  coded_entries.docid  AS docid,
        coded_entries.filename,
        coded_entries.label,
        coded_entries.class_code,
        coded_entries.parent_ref,
        coded_entries.section_ref,
        coded_entries.display_name,
        coded_entries.text_id,
        coded_entries.sentence,
        coded_times.effective_time,
        coded_entries.code,
        coded_entries.code_system,
        coded_entries.code_system_name,
        coded_times.start_time as start_time,
        coded_times.end_time   as end_time
FROM ( SELECT dm.document_id AS docid, regexp_replace(d.filename, '.*/', '') AS filename,
                dm.value::jsonb ->> 'class_code' as class_code,
                dm.value::jsonb ->> 'label' as label,
                dm.value::jsonb ->> 'parent_reference' as parent_ref,
                dm.value::jsonb ->> 'section' as section_ref,
                dm.value::jsonb ->> 'display_name' as display_name,
                dm.value::jsonb ->> 'text_id' as text_id,
                dm.value::jsonb ->> 'text' as sentence,
                dm.value::jsonb ->> 'code' as code,
                dm.value::jsonb ->> 'code_system' as code_system,
                dm.value::jsonb ->> 'code_system_name' as code_system_name
        FROM document d
            JOIN documentstructuredmetadata dm ON d.id = dm.document_id
) as coded_entries
LEFT JOIN ( SELECT dm.document_id as docid,
                dm.value::jsonb ->> 'label' as label,
                string_agg(time.etime::jsonb ->> 'value', ', ') as effective_time,
                --> also retrieve low-high times for completeness
                string_agg(time.etime::jsonb #>> '{low,value}', ', ') as start_time,
                string_agg(time.etime::jsonb #>> '{high,value}', ', ') as end_time
                FROM documentstructuredmetadata dm
                    JOIN LATERAL (SELECT dm.id, jsonb_array_elements(dm.value::jsonb -> 'effective_time') as etime ) as time ON true
                GROUP BY dm.document_id, label
) as coded_times
ON coded_entries.docid = coded_times.docid AND
    coded_entries.label = coded_times.label
)
SELECT sbadm_event.docid, sbadm_event.filename,
    sbadm_event.section AS section_display_name,
    sbadm_event.text_id,
    cvx_entry.display_name,
    cvx_entry.code_system_name,
    cvx_entry.code_system,
    cvx_entry.code,
    sbadm_event.effective_time,
    sbadm_event.low_time,
    sbadm_event.high_time,
    cvx_entry.sentence
FROM ( SELECT sbadm_cd.docid, sbadm_cd.filename,
            sbadm_cd.label as label,
            sec_cd.display_name as section,
            sbadm_cd.display_name as display_name, --> product
            sbadm_cd.text_id as text_id,
            sbadm_cd.effective_time as effective_time,
            sbadm_cd.end_time as low_time,
            sbadm_cd.start_time as high_time
    FROM coded_data sbadm_cd
    JOIN coded_data sec_cd ON sbadm_cd.section_ref = sec_cd.label
    --> retrieve section info from section entries
    WHERE sbadm_cd.class_code = 'SBADM' --> Substance Administration ACT
) AS sbadm_event
JOIN (
    SELECT cvx_cd.*
    FROM coded_data cvx_cd
    WHERE cvx_cd.code_system_name = 'CVX' AND cvx_cd.parent_ref IS NOT NULL) AS cvx_entry
ON sbadm_event.label = cvx_entry.parent_ref AND --> cvx entries related to sbadm entries
    sbadm_event.docid = cvx_entry.docid
ORDER BY filename;