Skip to Main Content

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

CROSS JOIN JSON_TABLE query issue

Fahed AkhtarJun 6 2022

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

Comments

Processing

Post Details

Added on Jun 6 2022
0 comments
453 views