> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://docs.emtelligent.com/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://docs.emtelligent.com/_mcp/server.

# Find CCD Document-level Metadata

## Synopsis

This query retrieves these document-level metadata from CDA headers:

* ClinicalDocument
* PatientRole (record target)
* ServiceEvent
* EncompassingEncounter

## Processing Requirements

For this query, Continuity of Care Documents were processed. See processing requirements [Processing Requirements](/nlp/database/cookbook/advanced-queries/ccd/introduction#processing-requirements).

## Background

C-CDA document standard specifies header requirements that provide context for all sections and CDA entries within the document. This includes information about when the document was created, the type of the type of document, the service date range that the document covers, or date of specific encounter (if the C-CDA document documents a specific encounter). In C-CDA header also identifies all participants - the most important of which is the `recordTarget` namely the patient. This information is stored in the various header elements as unique Object Identifiers (OID root and extension), and Effective Times (datetime values).

This query shows how to retrieve metadata for these header components:

* ClinicalDocument
* PatientRole
* ServiceEvent
* EncompassingEncounter

## SQL query

#### PostgreSQL

```sql
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;
```

| filename                  | header_element        | id_root                              | id_extension | effective_time    | start_time        | end_time          |
| ------------------------- | --------------------- | ------------------------------------ | ------------ | ----------------- | ----------------- | ----------------- |
| emerge-patient-1.xml      | ClinicalDocument      | db734647-fc99-424c-a864-7e3cda82e703 | —            | 20140416115439    | —                 | —                 |
| emerge-patient-1.xml      | patientRole           | 2.16.840.1.113883.19.5.99999.2       | 998991       | —                 | —                 | —                 |
| emerge-patient-1.xml      | patientRole           | 2.16.840.1.113883.4.1                | 111-00-2330  | —                 | —                 | —                 |
| emerge-patient-1.xml      | serviceEvent          | —                                    | —            | —                 | 20100331100000    | 20101024100000    |
| getrealhealth-ccd-e1.xml  | ClinicalDocument      | 2.16.840.1.113883.19.5.99999.1       | ToC_10       | 20150622          | —                 | —                 |
| getrealhealth-ccd-e1.xml  | encompassingEncounter | 2.16.840.1.113883.19                 | 9937012      | —                 | 201506221000-0500 | 20150624          |
| getrealhealth-ccd-e1.xml  | patientRole           | 2.16.840.1.113883.4.1                | 117273338    | —                 | —                 | —                 |
| getrealhealth-ccd-e1.xml  | serviceEvent          | —                                    | —            | —                 | 20150622          | 20150624          |
| oncology-alice-newman.xml | ClinicalDocument      | 2.16.840.1.113883.19.5.99999.1       | TT101        | 201506221030-0500 | —                 | —                 |
| oncology-alice-newman.xml | encompassingEncounter | 2.16.840.1.113883.19                 | 9937012      | —                 | 201506221000-0500 | 201506221030-0500 |

## References

* See Python Client SDK Documentation for more information about CCD file support.