By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TEST_JSON
( PROCESS_ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
JSON_DATA CLOB CONSTRAINT check_json CHECK (JSON_DATA IS JSON)
)
LOB (JSON_DATA) STORE AS SECUREFILE (
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
NOCACHE
LOGGING);
-- TABLE ALTERS
ALTER TABLE TEST_JSON
ADD CONSTRAINT TEST_JSON_PK
PRIMARY KEY ( PROCESS_ID ) USING INDEX
ENABLE;
INSERT INTO TEST_JSON (JSON_DATA)
VALUES ('[
{
"displayName": "Whitbuckle, Dalongrirlum",
"employeeID": "1",
"EntitlementJSON": {
"Test Role 1": {
"dodaac": {},
"fundCode": {},
"glRepair": {},
"location": {},
"cognos": {},
"jv": {}
},
"Test Role 2": {
"dodaac": {},
"fundCode": {},
"glRepair": {},
"location": {},
"cognos": {},
"jv": {}
}
},
"manager": "Urgaehilde Rubyforged",
"company": "Bloodguard Industrie"
},
{
"displayName": "Koboldbelly, Sitgrolin",
"employeeID": "4",
"EntitlementJSON": {},
"manager": "Kogrubera Orcborn",
"company": "Bloodguard Industrie"
},
{
"displayName": "Longblade, Skolout",
"employeeID": "2",
1 rows affected
select process_id,
display_name,
employeeID,
manager,
listagg(TR1) within group (order by process_id, display_name, employeeID, manager) Role_TR1,
listagg(TR2) within group (order by process_id, display_name, employeeID, manager) Role_TR2,
listagg(TR3) within group (order by process_id, display_name, employeeID, manager) Role_TR3,
listagg(TR4) within group (order by process_id, display_name, employeeID, manager) Role_TR4
from (select j.process_id,
jt.display_Name,
jt.employeeID,
jt.manager,
TR1,
TR2,
TR3,
TR4
from test_json j
cross apply JSON_TABLE(j.JSON_DATA, '$[*]'
COLUMNS (display_Name VARCHAR2(200 CHAR) PATH '$.displayName',
employeeID VARCHAR2(20 CHAR) PATH '$.employeeID',
manager VARCHAR2(200 CHAR) PATH '$.manager',
nested path '$.EntitlementJSON."Test Role 1"' columns
(TR1 VARCHAR2(4000 CHAR) FORMAT JSON WITH WRAPPER PATH '$.location[*]'),
nested path '$.EntitlementJSON."Test Role 2"' columns
(TR2 VARCHAR2(4000 CHAR) FORMAT JSON WITH WRAPPER PATH '$.location[*]'),
nested path '$.EntitlementJSON."Test Role 3"' columns
(TR3 VARCHAR2(4000 CHAR) FORMAT JSON WITH WRAPPER PATH '$.location[*]'),
nested path '$.EntitlementJSON."Test Role 4"' columns
(TR4 VARCHAR2(4000 CHAR) FORMAT JSON WITH WRAPPER PATH '$.location[*]')
)) jt
where process_id = 1)
--and jt.employeeID = '')
group by process_id, employeeID, display_name, manager;
| PROCESS_ID | DISPLAY_NAME | EMPLOYEEID | MANAGER | ROLE_TR1 | ROLE_TR2 | ROLE_TR3 | ROLE_TR4 |
|---|---|---|---|---|---|---|---|
| 1 | Whitbuckle, Dalongrirlum | 1 | Urgaehilde Rubyforged | [{}] | [{}] | null | null |
| 1 | Longblade, Skolout | 2 | Therrilyn Mithrilpike | null | null | null | null |
| 1 | Warmcoat, Alfomdum | 3 | Therrilyn Mithrilpike | null | null | ["ALL"] | null |
| 1 | Koboldbelly, Sitgrolin | 4 | Kogrubera Orcborn | null | null | null | null |