I have a json data in clob columns which contains multiple transactions over single day. I need to convert the column data into rows, below query is work only for single transaction but for multiple transactions query is not showing any result. Json data is attached.
json data.txt (9.48 KB)SELECT BUSINESS_DATE,
BRANCH_REF,
BRANCH_NAME,
TRAN_REF_NO,
TRAN_TYPE,
TRAN_STATUS,
SUPPLIER_ID,
SUPPLIER_CODE,
TO_DATE(CREATE_DATE,'RRRR-MM-DD HH24:MI:SS') CREATE_DATE,
TO_DATE(UPDATE_DATE,'RRRR-MM-DD HH24:MI:SS') UPDATE_DATE,
TO_DATE(POST_DATE,'RRRR-MM-DD HH24:MI:SS') POST_DATE,
ITEM_ID,
SKU,
ITEM_COST,
TRAN_COST,
QTY
FROM JSON_DOCUMENTS T
CROSS JOIN
JSON_TABLE(
t.data.data,
'$[*]'
COLUMNS
tran\_type NUMBER PATH '$.type',
tran\_status NUMBER PATH '$.status',
business\_date DATE PATH '$.business\_date',
tran\_ref\_no VARCHAR2(2000) PATH '$.reference',
remarks VARCHAR2(2000) PATH '$.notes',
create\_date VARCHAR2(2000) PATH '$.created\_at',
update\_date VARCHAR2(2000) PATH '$.updated\_at',
post\_date VARCHAR2(2000) PATH '$.posted\_at',
branch\_ref VARCHAR2(2000) PATH '$.branch.reference',
branch\_name VARCHAR2(2000) PATH '$.branch.name',
supplier\_id VARCHAR2(2000) PATH '$.supplier.id',
supplier\_code VARCHAR2(2000) PATH '$.supplier.code',
nvoice\_no VARCHAR2(2000) PATH '$.invoice\_number',
invoice\_date DATE PATH '$.invoice\_date',
NESTED PATH '$.items\[\*\]'
COLUMNS (
item\_id VARCHAR2(2000) PATH '$.id',
sku VARCHAR2(2000) PATH '$.sku',
item\_cost NUMBER PATH '$.cost',
tran\_cost NUMBER PATH '$.pivot.cost',
qty NUMBER PATH '$.pivot.quantity'
)
)