针对你的需求，我们可以通过LEFT JOIN + 聚合函数来为每条CI记录生成对应的父项和子项数组，无需使用PL/SQL，且能被Apache NiFi的ExecuteSQL处理器识别为java.sql.Types.ARRAY类型。以下分常见数据库给出具体实现：

1. Oracle 数据库方案 #

Oracle支持内置的数组类型（如SYS.ODCINUMBERLIST），可以通过COLLECT函数聚合ID为数组：

SELECT
    c.ci_id AS id,
    c.description,
    -- 生成子项ID数组，无数据时返回NULL
    CASE
        WHEN COUNT(cr_child.child_ci_id) = 0 THEN NULL
        ELSE CAST(COLLECT(cr_child.child_ci_id) AS SYS.ODCINUMBERLIST)
    END AS children,
    -- 生成父项ID数组，无数据时返回NULL
    CASE
        WHEN COUNT(cr_parent.parent_ci_id) = 0 THEN NULL
        ELSE CAST(COLLECT(cr_parent.parent_ci_id) AS SYS.ODCINUMBERLIST)
    END AS parents
FROM CI c
-- 关联子项关系：当前CI作为父项
LEFT JOIN CI_REL cr_child ON c.ci_id = cr_child.parent_ci_id
-- 关联父项关系：当前CI作为子项
LEFT JOIN CI_REL cr_parent ON c.ci_id = cr_parent.child_ci_id
GROUP BY c.ci_id, c.description
ORDER BY c.ci_id;

说明： #

• SYS.ODCINUMBERLIST是Oracle内置的数字数组类型，能直接被java.sql.Types.ARRAY识别
• COLLECT函数将聚合的ID值收集为数组，配合CAST转换为标准数组类型
• 用CASE处理无父/子项的场景，返回NULL（和你的示例输出一致）

2. PostgreSQL 数据库方案 #

PostgreSQL的ARRAY_AGG函数可以直接生成数组，天然支持java.sql.Types.ARRAY：

SELECT
    c.ci_id AS id,
    c.description,
    -- 生成子项ID数组，过滤空值并去重
    ARRAY_AGG(DISTINCT cr_child.child_ci_id) FILTER (WHERE cr_child.child_ci_id IS NOT NULL) AS children,
    -- 生成父项ID数组，过滤空值并去重
    ARRAY_AGG(DISTINCT cr_parent.parent_ci_id) FILTER (WHERE cr_parent.parent_ci_id IS NOT NULL) AS parents
FROM CI c
LEFT JOIN CI_REL cr_child ON c.ci_id = cr_child.parent_ci_id
LEFT JOIN CI_REL cr_parent ON c.ci_id = cr_parent.child_ci_id
GROUP BY c.ci_id, c.description
ORDER BY c.ci_id;

说明： #

• ARRAY_AGG直接聚合非空ID为数组，FILTER子句过滤掉关联产生的NULL值
• DISTINCT避免因为CI_REL中重复记录导致的重复ID
• 无父/子项时，数组会返回NULL，完全匹配你的示例要求

关键注意事项 #

• 确保使用的数据库驱动支持数组类型映射（Oracle的ojdbc、PostgreSQL的pgjdbc都原生支持）
• 如果是MySQL这类无原生SQL数组的数据库，可以用JSON_ARRAYAGG生成JSON数组，但需要额外通过NiFi的ConvertJSONToSQL或UpdateRecord处理器转换为Solr多值字段，不如原生数组直接
• 测试时建议先单独执行SQL，验证返回的数组类型是否符合预期，再集成到NiFi的ExecuteSQL处理器中

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