By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TB_COP_BUSS_OBJ_TXN (
OBJECT_PRI_KEY_1 VARCHAR2(20) PRIMARY KEY,
OBJECT_DATA CLOB CHECK (OBJECT_DATA IS JSON)
);
INSERT INTO TB_COP_BUSS_OBJ_TXN (
OBJECT_PRI_KEY_1,
OBJECT_DATA
) VALUES (
'XXXXXX',
'{"AOF":{"LEAD_DATA":{"DIRECTOR":[1,2,3]}}}'
);
1 rows affected
SELECT JSON_QUERY(OBJECT_DATA,'$.AOF.LEAD_DATA."DIRECTOR"[*]')
from TB_COP_BUSS_OBJ_TXN FD
WHERE OBJECT_PRI_KEY_1 = 'XXXXXX'
| JSON_QUERY(OBJECT_DATA,'$.AOF.LEAD_DATA."DIRECTOR"[*]') |
|---|
| null |
SELECT JSON_QUERY(OBJECT_DATA,'$.AOF.LEAD_DATA."DIRECTOR"')
from TB_COP_BUSS_OBJ_TXN FD
WHERE OBJECT_PRI_KEY_1 = 'XXXXXX'
| JSON_QUERY(OBJECT_DATA,'$.AOF.LEAD_DATA."DIRECTOR"') |
|---|
| [1,2,3] |
SELECT value
from TB_COP_BUSS_OBJ_TXN FD
CROSS APPLY JSON_TABLE(
fd.object_data,
'$.AOF.LEAD_DATA."DIRECTOR"[*]'
COLUMNS (
value NUMBER PATH '$'
)
)
WHERE OBJECT_PRI_KEY_1 = 'XXXXXX'
| VALUE |
|---|
| 1 |
| 2 |
| 3 |