> 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 coded Physical Quantities in CCDs

## Synopsis

This query retrieves coded physical quantity (PQ) values and unit, and if present the interpretation code. PQ values are typically found in Lab Results and Vital Signs sections of CCD documents.

## 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).

## Clinical Context

CCDs often contain coded measurement observations coded using LOINC and their results. The results of these measurements are dimensioned quantities that are encoded using the PQ data type (in FHIR RIM v3 data model). The PQ data type includes the measurement value and its unit. The interpretation of the PQ values is sometimes also provide as shown in the following image.

![Lab result coded entry](/_fern-img/e20686a9eedbec10a7155910bd46436a74653e9b4ca16ea91e557e385ead64e3.webp)

## SQL query

#### PostgreSQL

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

#### Snowflake

```sql
WITH coded_data AS (
SELECT DISTINCT d.id as docid, regexp_replace(d.filename, '.*/', '') AS filename,
                PARSE_JSON(dm.value):class_code::string as class_code,
                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
),
testresults AS (
SELECT res_cd.docid,res_cd.filename                as filename,
    sec_cd.json:display_name::string                 as section,
    res_cd.json:display_name::string            as display_name,
    res_cd.json:text_id::string                 as text_id,
    res_cd.json:label::string                   as label,
    res_cd.json:code_system_name::string        as code_system_name,
    res_cd.json:code_system::string             as code_system,
    res_cd.json:code::string                    as code,
    PARSE_JSON(res_cd.json:value):value::string as value,
    PARSE_JSON(res_cd.json:value):unit::string  as unit,
    PARSE_JSON(res_cd.json:value):type::string  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::string = sec_cd.json:label::string AND
        sec_cd.json:for_elt_tag::string = 'section'
WHERE value_type='PQ' AND code_system_name = 'LOINC'),
interpretations AS (
    --> Get interpretationCode display_name from interpretation entries
    SELECT json:display_name::string as display_name,
        json:text_id::string as text_id,
        json:parent_reference::string as parent_reference,
        json:code_system_name::string as code_system_name,
        json:code_system::string as code_system,
        json:code::string as code,
        json
from coded_data
WHERE json:code_elt_tag::string = '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;
```

| docid | filename             | section_display_name | text_id | display_name          | code_system_name | code_system           | code    | value | unit  | value_type | interpret_code | interpret_code_system  | sentence |
| ----- | -------------------- | -------------------- | ------- | --------------------- | ---------------- | --------------------- | ------- | ----- | ----- | ---------- | -------------- | ---------------------- | -------- |
| 138   | emerge-patient-1.xml | Results              | —       | HDLc SerPl-sCnc       | LOINC            | 2.16.840.1.113883.6.1 | 14646-4 | 61    | mg/dL | PQ         | N              | 2.16.840.1.113883.5.83 | —        |
| 138   | emerge-patient-1.xml | Results              | —       | LDLc SerPl-mCnc       | LOINC            | 2.16.840.1.113883.6.1 | 2089-1  | 106.6 | mg/dL | PQ         | N              | 2.16.840.1.113883.5.83 | —        |
| 138   | emerge-patient-1.xml | Results              | —       | Trigl SerPl Calc-mCnc | LOINC            | 2.16.840.1.113883.6.1 | 12951-0 | 189   | mg/dL | PQ         | N              | 2.16.840.1.113883.5.83 | —        |
| 138   | emerge-patient-1.xml | Results              | —       | Hgb A1c MFr Bld       | LOINC            | 2.16.840.1.113883.6.1 | 4548-4  | 6.7   | %     | PQ         | N              | 2.16.840.1.113883.5.83 | —        |
| 138   | emerge-patient-1.xml | Results              | —       | HDLc SerPl-sCnc       | LOINC            | 2.16.840.1.113883.6.1 | 14646-4 | 63    | mg/dL | PQ         | N              | 2.16.840.1.113883.5.83 | —        |
| 138   | emerge-patient-1.xml | Results              | —       | Cholest SerPl-mCnc    | LOINC            | 2.16.840.1.113883.6.1 | 2093-3  | 251   | mg/dL | PQ         | N              | 2.16.840.1.113883.5.83 | —        |