Oracle 19C中用JSON_TABLE关联JSON属性与数据库列获取匹配行

要实现仅保留j_purchaseorder表中PONumber列与JSON文档内PONumber键值匹配的行，只需在原视图语句中添加关联过滤条件即可，具体修改如下：

修改后的视图创建代码 #

CREATE VIEW j_purchaseorder_detail_view
  AS SELECT jt.*
       FROM j_purchaseorder po
       INNER JOIN 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
       ON po.PONumber = jt.po_number;

关键修改说明 #

• 将原语句中的隐式交叉连接改为显式INNER JOIN，并通过ON po.PONumber = jt.po_number指定匹配条件，确保仅返回关系表PONumber与JSON文档中提取的po_number一致的行。
• 也可保留原交叉连接写法，在语句末尾添加WHERE po.PONumber = jt.po_number过滤条件，实现效果完全相同。

内容的提问来源于stack exchange，提问作者virtual transfer