Joining a Json attribute with Oracle Database column to get matching rows using JSON_TABLE - Oracle 19C

Question

This is on Oracle 19c Database.

I have a need to develop a DB view containing a traditional relational table and a json_table and only get filtered row based on a column from this relational table matching with one key from json table. How do I achieve this?

Below is an example of the code. Here only those rows from j_purchaseorder should return which have matching PONumber In Json table.

CREATE VIEW j_purchaseorder_detail_view
  AS SELECT jt.*
       FROM j_purchaseorder po,
            json_table(po.po_document, '$'
              COLUMNS (
                po_number        NUMBER(10)         PATH '$.PONumber',
                reference        VARCHAR2(30 CHAR)  PATH '$.Reference',
                requestor        VARCHAR2(128 CHAR) PATH '$.Requestor',
                userid           VARCHAR2(10 CHAR)  PATH '$.User',
                costcenter       VARCHAR2(16)       PATH '$.CostCenter',
                ship_to_name     VARCHAR2(20 CHAR)
                                 PATH '$.ShippingInstructions.name',
                ship_to_street   VARCHAR2(32 CHAR)
                                 PATH '$.ShippingInstructions.Address.street',
                ship_to_city     VARCHAR2(32 CHAR)
                                 PATH '$.ShippingInstructions.Address.city',
                ship_to_county   VARCHAR2(32 CHAR)
                                 PATH '$.ShippingInstructions.Address.county',
                ship_to_postcode VARCHAR2(10 CHAR)
                                 PATH '$.ShippingInstructions.Address.postcode',
                ship_to_state    VARCHAR2(2 CHAR)
                                 PATH '$.ShippingInstructions.Address.state',
                ship_to_zip      VARCHAR2(8 CHAR)
                                 PATH '$.ShippingInstructions.Address.zipCode',
                ship_to_country  VARCHAR2(32 CHAR)
                                 PATH '$.ShippingInstructions.Address.country',
                ship_to_phone    VARCHAR2(24 CHAR)
                                 PATH '$.ShippingInstructions.Phone[0].number',
                NESTED PATH '$.LineItems[*]'
                  COLUMNS (
                    itemno      NUMBER(38)         PATH '$.ItemNumber', 
                    description VARCHAR2(256 CHAR) PATH '$.Part.Description', 
                    upc_code    NUMBER             PATH '$.Part.UPCCode', 
                    quantity    NUMBER(12,4)       PATH '$.Quantity', 
                    unitprice   NUMBER(14,2)       PATH '$.Part.UnitPrice'))) jt;

Please advise. Thank you Darsh

Answer 1

Working on the premise of

Here only those rows from j_purchaseorder should return which have matching PONumber In Json table

I am assuming you have a column in J_PURCHASEORDER that you want to match to jt.po_number ? Let's call that column PURCHASE_ORDER_NUMBER.

In that case, is not this just a standard join ? eg

CREATE VIEW j_purchaseorder_detail_view
  AS SELECT jt.*
       FROM j_purchaseorder po,
       ...
       ...
  WHERE jt.PO_NUMBER = po.PURCHASE_ORDER_NUMBER