Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 238 Big Data Appliance
- 1.9K Data Science
- 450.2K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 436 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
CROSS JOIN JSON_TABLE query issue

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.
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'
)
)