Example Queries Using Oracle JSON Operators

The example queries presented in this section demonstrate the use of Oracle JSON operators to execute queries similar to those presented in the previous section using Oracle regular expression functions.

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%';