CROSS JOIN JSON_TABLE query issue

Fahed Akhtar

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

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'

          )

                     

    )