By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Create table TEST_TBL(
Item varchar2(100),
loc varchar2(100),
ATTRIBUTE BLOB CHECK (attribute IS JSON)
);
BEGIN
INSERT INTO TEST_TBL (item, loc, attribute) VALUES ('ABC','XYZ',UTL_RAW.CAST_TO_RAW('[{"attribute":[{"attrName":"TEST","attrValue":"REC","attrOperator":"EQUAL","attrOverrideParam":"Priority","attrOverrideValue":0}]}]'));
INSERT INTO TEST_TBL (item, loc, attribute) VALUES ('ABC','XYZ',UTL_RAW.CAST_TO_RAW('[{"attribute":[{"attrName":"TEST1","attrValue":"REC1","attrOperator":"EQUAL","attrOverrideParam":"Priority","attrOverrideValue":0}]}]'));
INSERT INTO TEST_TBL (item, loc, attribute) VALUES ('ABC','XYZ',UTL_RAW.CAST_TO_RAW('[{"attribute":[{"attrName":"TEST2","attrValue":"REC2","attrOperator":"EQUAL","attrOverrideParam":"Priority","attrOverrideValue":0}]}]'));
INSERT INTO TEST_TBL (item, loc, attribute) VALUES ('CDE','WER',UTL_RAW.CAST_TO_RAW('[{"attribute":[{"attrName":"TEST5","attrValue":"REC5","attrOperator":"EQUAL","attrOverrideParam":"Priority","attrOverrideValue":0}]}]'));
INSERT INTO TEST_TBL (item, loc, attribute) VALUES ('CDE','WER',UTL_RAW.CAST_TO_RAW('[{"attribute":[{"attrName":"TEST6","attrValue":"REC6","attrOperator":"EQUAL","attrOverrideParam":"Priority","attrOverrideValue":0}]}]'));
COMMIT;
END;
/
1 rows affected
SELECT t.item, t.loc,
JSON_ARRAYAGG(j.attr FORMAT JSON) AS attrs
FROM test_tbl t
CROSS APPLY JSON_TABLE(
t.attribute,
'$[*]'
COLUMNS(
attr CLOB FORMAT JSON PATH '$'
)
) j
GROUP BY item, loc
| ITEM | LOC | ATTRS |
|---|---|---|
| ABC | XYZ | [{"attribute":[{"attrName":"TEST","attrValue":"REC","attrOperator":"EQUAL","attrOverrideParam":"Priority","attrOverrideValue":0}]},{"attribute":[{"attrName":"TEST2","attrValue":"REC2","attrOperator":"EQUAL","attrOverrideParam":"Priority","attrOverrideValue":0}]},{"attribute":[{"attrName":"TEST1","attrValue":"REC1","attrOperator":"EQUAL","attrOverrideParam":"Priority","attrOverrideValue":0}]}] |
| CDE | WER | [{"attribute":[{"attrName":"TEST5","attrValue":"REC5","attrOperator":"EQUAL","attrOverrideParam":"Priority","attrOverrideValue":0}]},{"attribute":[{"attrName":"TEST6","attrValue":"REC6","attrOperator":"EQUAL","attrOverrideParam":"Priority","attrOverrideValue":0}]}] |