Find CCD Document-level Metadata

SELECT regexp_replace(d.filename, '.*/', '')            AS filename,
       m.value::jsonb ->> 'for_elt_tag'                 AS header_element,
       ids.elem ->> 'root'                              AS id_root,
       ids.elem ->> 'extension'                         AS id_extension,
       -- also retrieve start and end time, which are sometimes populated
       string_agg(DISTINCT et.elem ->> 'value', ',')        AS effective_time,
       string_agg(DISTINCT et.elem #>> '{low,value}', ',')  AS start_time,
       string_agg(DISTINCT et.elem #>> '{high,value}', ',') AS end_time
FROM document d
    JOIN documentstructuredmetadata m ON d.id = m.document_id
    -- id and effective_time are JSON arrays, so expand them rather than assuming
    -- one entry; LEFT JOIN keeps header elements that carry neither
    LEFT JOIN LATERAL jsonb_array_elements(
        coalesce(m.value::jsonb -> 'id', '[]'::jsonb)) AS ids(elem) ON true
    LEFT JOIN LATERAL jsonb_array_elements(
        coalesce(m.value::jsonb -> 'effective_time', '[]'::jsonb)) AS et(elem) ON true
WHERE m.value::jsonb ->> 'for_elt_tag' IN
      ('ClinicalDocument', 'patientRole', 'serviceEvent', 'encompassingEncounter')
GROUP BY 1, 2, 3, 4
ORDER BY filename, header_element, id_root
LIMIT 10;