如何从Oracle表生成三级嵌套JSON payload？两种方案遇阻求助

方法1：SELECT语句生成JSON的重复Header问题 #

问题原因 #

直接关联表头表（xxrr_hdr_stg）和行表（xxrr_line_stg）时，会返回与行记录数一致的表头结果，最终生成多个重复Header的JSON对象，而非包含所有行的单个嵌套JSON。

解决方法 #

使用JSON_ARRAYAGG聚合行数据，以表头表为主查询，通过子查询或关联聚合所有行记录，确保仅生成一个完整的JSON结构。

修改后的SQL代码：

SELECT JSON_OBJECT(
    KEY 'InvoiceNumber'   VALUE h.document_id,
    KEY 'InvoiceCurrency' VALUE 'USD',
    KEY 'InvoiceAmount'   VALUE h.amount,
    KEY 'InvoiceDate'     VALUE TO_CHAR(h.trx_date, 'YYYY-MM-DD'), -- 统一日期格式
    KEY 'BusinessUnit'    VALUE 'ABCD Corp',
    KEY 'Supplier'        VALUE 'NASA',
    KEY 'SupplierSite'    VALUE 'PHYSICAL',
    KEY 'InvoiceGroup'    VALUE 'MoonLander',
    KEY 'Description'     VALUE 'Some Description',
    KEY 'invoiceLines' VALUE JSON_ARRAYAGG(
        JSON_OBJECT(
            KEY 'LineNumber' VALUE t.line_id,
            KEY 'LineAmount' VALUE t.line_value,
            KEY 'invoiceDistributions' VALUE JSON_ARRAY(
                JSON_OBJECT(
                    KEY 'DistributionLineNumber' VALUE t.line_id,
                    KEY 'DistributionLineType'   VALUE 'Item',
                    KEY 'DistributionAmount'     VALUE t.line_value
                )
            )
        ) ORDER BY t.line_id -- 按行号排序
    ) FORMAT JSON
) JSON_VALUE
INTO aCLOB
FROM XXRR_HDR_STG h
LEFT JOIN XXRR_LINE_STG t ON t.document_id = h.document_id
WHERE h.document_id = 543210
GROUP BY h.document_id, h.amount, h.trx_date;

方法2：PL/SQL JSON对象数组的语法与结构错误 #

问题分析 #

1. JSON语法错误：拼接JSON字符串时末尾多余逗号（如"LineAmount": "'|| j.line_value|| '",），导致JSON格式无效；
2. 结构错误：将invoiceDistributions作为顶级字段，而非嵌套在对应的invoiceLines对象中；且所有明细行被放入同一个数组，未与父行关联。

解决方法 #

• 移除拼接字符串末尾的多余逗号；
• 为每一行创建独立的明细数组，嵌套到该行的JSON对象中；
• 调整循环逻辑，确保行与明细的层级关联。

修改后的PL/SQL代码：

DECLARE
    l_json       json_object_t := json_object_t();
    l_children   json_array_t := json_array_t();
    envelope     CLOB;
BEGIN
    FOR i IN (SELECT document_id, amount, trx_date FROM xxrr_hdr_stg WHERE document_id = 543210) LOOP
        -- 填充表头字段
        l_json.put('InvoiceNumber', i.document_id);
        l_json.put('InvoiceCurrency', 'USD');
        l_json.put('InvoiceAmount', i.amount);
        l_json.put('InvoiceDate', TO_CHAR(i.trx_date, 'YYYY-MM-DD'));
        l_json.put('BusinessUnit', 'ABCD Corp');
        l_json.put('Supplier', 'NASA');
        l_json.put('SupplierSite', 'PHYSICAL');
        l_json.put('InvoiceGroup', 'RR');
        l_json.put('Description', 'Some Descr');

        -- 处理每行数据，嵌套对应明细
        FOR j IN (SELECT line_id, line_value FROM xxrr_line_stg WHERE document_id = i.document_id ORDER BY line_id) LOOP
            -- 创建当前行的明细数组
            l_grandchild json_array_t := json_array_t();
            l_grandchild.append(json_object_t(
                '{
                    "DistributionLineNumber": "' || j.line_id || '",
                    "DistributionLineType": "Item",
                    "DistributionAmount": "' || j.line_value || '",
                    "DistributionCombination": "254.000.000.2111010.000.0.0"
                }'
            ));

            -- 创建当前行的JSON对象，嵌套明细数组
            l_line_obj json_object_t := json_object_t();
            l_line_obj.put('LineNumber', j.line_id);
            l_line_obj.put('LineAmount', j.line_value);
            l_line_obj.put('invoiceDistributions', l_grandchild);

            -- 将行对象加入行数组
            l_children.append(l_line_obj);
        END LOOP;

        -- 将行数组加入主JSON对象
        l_json.put('invoiceLines', l_children);
    END LOOP;

    -- 生成最终CLOB
    envelope := l_json.to_clob();
END;
/

额外优化建议 #

• 数值类型处理：如果line_value是数值类型，直接传入数值而非拼接字符串，避免JSON中出现字符串格式的数值；
• 空值兼容：使用LEFT JOIN确保无行记录时仍能生成包含空数组的合法JSON；
• 代码可读性：避免手动拼接JSON字符串，优先使用json_object_t的put方法构建对象，减少语法错误风险。

内容的提问来源于stack exchange，提问作者virtual transfer