Oracle JSON演算子を使用した問合せの例

この項に示す問合せの例では、Oracle JSON演算子を使用して、前の項に示したものと同様の問合せをOracle正規表現関数を使用して実行する方法を示します。

SELECT  firstname, lastname,  j.address.street,  j.address.city,  
    j.address.state, j.vehicleinfo.model FROM rmvTable j;

SELECT JSON_QUERY(vehicleinfo, '$[0]' WITH CONDITIONAL WRAPPER) 
    FROM rmvTable;

SELECT firstname, lastname, address,
    JSON_QUERY(vehicleinfo, '$[0]' WITH CONDITIONAL WRAPPER) 
    AS "Primary Vehicle is made by GM" 
    FROM rmvTable WHERE 
    JSON_QUERY(vehicleinfo, '$[0].make' WITH CONDITIONAL WRAPPER) 
    LIKE '%GM%';

SELECT  firstname, lastname, address,
    JSON_QUERY(vehicleinfo, '$[0]' WITH CONDITIONAL WRAPPER) 
    AS "Primary Vehicle is a Camaro"
    FROM rmvTable WHERE 
    JSON_QUERY(vehicleinfo, '$[0].model' WITH CONDITIONAL WRAPPER) 
    LIKE '%Camaro%';

SELECT  firstname, lastname, address,
    JSON_QUERY(vehicleinfo, '$[0].model' WITH CONDITIONAL WRAPPER) 
    AS "Primary Vehicle Model",
    JSON_QUERY(vehicleinfo, '$[0].value' WITH CONDITIONAL WRAPPER) 
    AS "Primary Vehicle Value",
    JSON_QUERY(vehicleinfo, '$[0].tax' WITH CONDITIONAL WRAPPER) 
    AS "Tax Owed",
    JSON_QUERY(vehicleinfo, '$[0].paid' WITH CONDITIONAL WRAPPER) 
    AS "Tax Paid" 
    FROM rmvTable WHERE 
    JSON_QUERY(vehicleinfo, '$[0].make' WITH CONDITIONAL WRAPPER) 
    LIKE '%GM%';