Oracle中按Item与Loc分组拼接BLOB类型Attribute字段的方法

输入表结构与数据 #

Create table TEST_TBL(Item varchar2(100),loc varchar2(100), ATTRIBUTE BLOB);

INSERT INTO TEST_TBL VALUES('ABC','XYZ',UTL_RAW.CAST_TO_RAW('[{"attribute":[{"attrName":"TEST","attrValue":"REC","attrOperator":"EQUAL","attrOverrideParam":"Priority","attrOverrideValue":0}]}]'));
INSERT INTO TEST_TBL VALUES('ABC','XYZ',UTL_RAW.CAST_TO_RAW('[{"attribute":[{"attrName":"TEST1","attrValue":"REC1","attrOperator":"EQUAL","attrOverrideParam":"Priority","attrOverrideValue":0}]}]'));
INSERT INTO TEST_TBL VALUES('ABC','XYZ',UTL_RAW.CAST_TO_RAW('[{"attribute":[{"attrName":"TEST2","attrValue":"REC2","attrOperator":"EQUAL","attrOverrideParam":"Priority","attrOverrideValue":0}]}]'));
INSERT INTO TEST_TBL VALUES('CDE','WER',UTL_RAW.CAST_TO_RAW('[{"attribute":[{"attrName":"TEST5","attrValue":"REC5","attrOperator":"EQUAL","attrOverrideParam":"Priority","attrOverrideValue":0}]}]'));
INSERT INTO TEST_TBL VALUES('CDE','WER',UTL_RAW.CAST_TO_RAW('[{"attribute":[{"attrName":"TEST6","attrValue":"REC6","attrOperator":"EQUAL","attrOverrideParam":"Priority","attrOverrideValue":0}]}]'));
COMMIT;

需求说明 #

当Item和Loc的组合相同时，需将对应的ATTRIBUTE字段（存储为BLOB的JSON数据）中的attribute数组合并，生成包含所有对应属性项的目标JSON结构后转回BLOB类型。

预期输出 #

INSERT INTO TEST_TBL VALUES('ABC','XYZ',UTL_RAW.CAST_TO_RAW('[{"attribute":[{"attrName":"TEST","attrValue":"REC","attrOperator":"EQUAL","attrOverrideParam":"Priority","attrOverrideValue":0}],[{"attrName":"TEST1","attrValue":"REC1","attrOperator":"EQUAL","attrOverrideParam":"Priority","attrOverrideValue":0}],[{"attrName":"TEST2","attrValue":"REC2","attrOperator":"EQUAL","attrOverrideParam":"Priority","attrOverrideValue":0}]}]'));
INSERT INTO TEST_TBL VALUES('CDE','WER',UTL_RAW.CAST_TO_RAW('[{"attribute":[{"attrName":"TEST5","attrValue":"REC5","attrOperator":"EQUAL","attrOverrideParam":"Priority","attrOverrideValue":0}],[{"attrName":"TEST6","attrValue":"REC6","attrOperator":"EQUAL","attrOverrideParam":"Priority","attrOverrideValue":0}]}]'));

实现方法 #

具体SQL代码 #

WITH attr_data AS (
    SELECT 
        item,
        loc,
        -- 将BLOB转字符串后提取单个属性对象
        JSON_VALUE(UTL_RAW.CAST_TO_VARCHAR2(attribute), '$[0].attribute[0]' RETURNING VARCHAR2(4000)) AS attr_obj
    FROM TEST_TBL
),
aggregated_attr AS (
    SELECT 
        item,
        loc,
        -- 按Item+Loc分组聚合所有属性对象
        LISTAGG(attr_obj, ',') WITHIN GROUP (ORDER BY attr_obj) AS combined_attrs
    FROM attr_data
    GROUP BY item, loc
)
SELECT 
    'INSERT INTO TEST_TBL VALUES(''' || item || ''',''' || loc || ''',UTL_RAW.CAST_TO_RAW(''[{"attribute":[' || combined_attrs || ']}]''));' AS insert_stmt
FROM aggregated_attr;

代码说明 #

1. CTE attr_data：将BLOB类型的ATTRIBUTE转换为字符串，通过JSON_VALUE提取每条记录中attribute数组内的单个属性对象；
2. CTE aggregated_attr：使用LISTAGG函数按Item+Loc组合聚合所有属性对象，用逗号分隔；
3. 最终查询：拼接成符合预期的INSERT语句，将聚合后的属性对象包裹到目标JSON结构中，再通过UTL_RAW.CAST_TO_RAW转回BLOB类型。

注意事项 #

• 若属性对象总长度超过VARCHAR2的4000字符限制，需改用CLOB类型处理：将UTL_RAW.CAST_TO_VARCHAR2替换为UTL_RAW.CAST_TO_CLOB，并使用XMLAGG替代LISTAGG实现大文本聚合；
• 该方案依赖Oracle原生JSON函数，需使用Oracle 12c及以上版本。

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