add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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,
( SELECT tr1
FROM JSON_TABLE(
test_role1, '$[*]'
COLUMNS (
TR1 VARCHAR2(4000 CHAR) FORMAT JSON WITH WRAPPER PATH '$.location[*]'
)
)
) AS tr1,
( SELECT tr2
FROM JSON_TABLE(
test_role2, '$[*]'
COLUMNS (
TR2 VARCHAR2(4000 CHAR) FORMAT JSON WITH WRAPPER PATH '$.location[*]'
)
)
) AS tr2,
( SELECT tr3
FROM JSON_TABLE(
test_role3, '$[*]'
COLUMNS (
TR3 VARCHAR2(4000 CHAR) FORMAT JSON WITH WRAPPER PATH '$.location[*]'
)
)
) AS tr3,
( SELECT tr4
FROM JSON_TABLE(
test_role4, '$[*]'
COLUMNS (
TR4 VARCHAR2(4000 CHAR) FORMAT JSON WITH WRAPPER PATH '$.location[*]'
)
)
) AS tr4
PROCESS_ID DISPLAY_NAME EMPLOYEEID MANAGER TR1 TR2 TR3 TR4
1 Longblade, Skolout 2 Therrilyn Mithrilpike ["Rockwall Villa - RV","Thunderbluff - TB"] null null null
SELECT process_id,
display_name,
employeeID,
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',
tr1 JSON PATH '$.EntitlementJSON."Test Role 1".location',
tr2 JSON PATH '$.EntitlementJSON."Test Role 2".location',
tr3 JSON PATH '$.EntitlementJSON."Test Role 3".location',
tr4 JSON PATH '$.EntitlementJSON."Test Role 4".location'
)
) e
WHERE j.process_id = 1
AND e.employeeID = '2';
PROCESS_ID DISPLAY_NAME EMPLOYEEID MANAGER TR1 TR2 TR3 TR4
1 Longblade, Skolout 2 Therrilyn Mithrilpike ["Rockwall Villa - RV","Thunderbluff - TB"] null null null