Example Queries Using Oracle Regular Expression Functions

The example queries presented here demonstrate the use of Oracle regular expression functions to query the Oracle NoSQL Database rmvTable.

To display the firstname, lastname, address, and vehicleinfo array from each row of the Oracle NoSQL Database rmvTable in which the value of the lastname column begins with the letter 'H', execute the query,

SELECT firstname,lastname,address,vehicleinfo FROM rmvTable WHERE 
    REGEXP_LIKE (lastname, '^[H].*');

Recall that the vehicleinfo field of the Oracle NoSQL Database rmvTable is an array of Oracle NoSQL Database RECORD types that are mapped to the Oracle Database STRING type in which each element of a given RECORD is represented as name-value pairs when mapped to the Oracle Database data model; for example, '"make":"Chrysler"', '"color":"red"', '"paid":true', etc.

Suppose then, that you wish to list the name and address of each person in the database whose first or "primary" vehicle matches certain criteria. Additionally, suppose that rather than listing each element in the vehicleinfo array, you instead whish to list only the vehicle information related to the vehicle that matched the desired criteria. That is, you do not wish to list information about any other vehicles associated with a given owner.

For example, you might want to list all owners whose primary vehicle is made by GM, or all owners who own a Camaro. Or maybe you want to list all owners who have not yet registered their primary vehicle. If we assume that information about each owner's primary vehicle is stored in the first element of the vehicleinfo array, then the queries below use Oracle regular expression functions to match on the sort of criteria just described. Specifically,

To find all owners with a primary vehicle made by GM:

SELECT firstname,lastname,address,
    REGEXP_SUBSTR(
        VEHICLEINFO, '\{([[:alnum:]":,\.]+){1,3}\}',1,1) 
    "Primary make: GM"
    FROM rmvTable WHERE 
    REGEXP_LIKE (
        REGEXP_SUBSTR(
            VEHICLEINFO,'\{([[:alnum:]":,\.]+){1,3}\}',1,1), 
                   '"make":"GM"');

To find all owners whose primary vehicle is a Camaro:

SELECT firstname,lastname,address,
    REGEXP_SUBSTR(
        VEHICLEINFO, '\{([[:alnum:]":,\.]+){1,3}\}',1,1)
    "Primary model: Camaro"
    FROM rmvTable WHERE 
    REGEXP_LIKE (
        REGEXP_SUBSTR(
            VEHICLEINFO, '\{([[:alnum:]":,\.]+){1,3}\}',1,1), 
                '"model":"Camaro"');

To find all owners whose primary vehicle has not been registered:

SELECT firstname,lastname,address,
    REGEXP_SUBSTR(
        VEHICLEINFO, '\{([[:alnum:]":,\.]+){1,3}\}',1,1) 
    "Primary fee NOT paid" 
    FROM rmvTable WHERE 
    REGEXP_LIKE (
        REGEXP_SUBSTR(
            VEHICLEINFO, '\{([[:alnum:]":,\.]+){1,3}\}',1,1), 
                '"paid":false');

To find all owners whose second vehicle is a truck:

SELECT firstname,lastname,address,
    REGEXP_SUBSTR(
        VEHICLEINFO, '\{([[:alnum:]":,\.]+){1,3}\}',1,2)
    "Second vehicle type: truck"
    FROM rmvTable WHERE 
    REGEXP_LIKE (
        REGEXP_SUBSTR(
            VEHICLEINFO, '\{([[:alnum:]":,\.]+){1,3}\}',1,2), 
                '"type":"truck"');