QBEs for JSON-Relational Duality Views

In Oracle AI Database, Query-By-Example (QBE) in GraphQL offers a mechanism for specifying filters within your queries by providing example values rather than explicit predicate logic.

QBE enables users to construct selection criteria using familiar field-value pairs, which are then automatically interpreted by the system and translated into the appropriate filtering operations, similar to SQL’s WHERE clause.

Within Oracle’s GraphQL implementation, QBE expressions are provided via the check clause. Each predicate is formed by specifying a field (column alias), a QBE operator (such as _eq, _lt, _like), and a corresponding comparison value. This design allows for the expression of a wide range of conditions, including equality, comparison, pattern matching, and null checks, as well as logical combinations using operators like _and and _or.

Note:

Oracle Database Support for GraphQL Queries offers a comprehensive set of relational, logical, and item method QBE operators. For a complete list of supported GraphQL QBE operators and detailed examples, refer to the GraphQL QBEs in Oracle section of the Oracle Database Support for GraphQL Developer's Guide. The examples provided in this topic focus specifically on scenarios related to JSON-relational duality views.

Here is an example which uses _eq QBE operator within the check clause to ensure that the team's name is equal to "Ferrari":

Example 9-9 Using the GraphQL _eq QBE Operator with Duality View Creation Syntax

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW team_ferrari_dv AS
team (check: {
    name: {_eq: "Ferrari"}
}) @insert @update @delete {
    _id: team_id
    name: name
    points: points 
    driver: driver @insert @update {
        driverId: driver_id
        name: name
        points: points
    }
};

Here is another example to illustrate the use of _gt, the greater than QBE operator with duality view creation syntax:

Example 9-10 Using the GraphQL _gt QBE Operator with Duality View Creation Syntax

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW valid_race_dv AS
race (check: {
    laps: {_gt: 50}
}) @insert @update @delete {
    _id: race_id
    name: name
    laps: laps
    date: race_date
    podium: podium
};

This example would display the documents only for Bahrain and Australian Grand Prix. The document corresponding to Saudi Grand Prix will not be included as it is filtered out by the _gt:50 QBE operator. Since the check option is enabled in the above duality view creation syntax, any following DMLs would strictly adhere to the condition specified by the QBE. For example, inserting the following into the duality view valid_race_dv is valid as the laps field is greater than 55, as specified by the QBE operator in the check clause:

INSERT INTO valid_race_dv VALUES ('
{
    "_id"   : 204,
    "name"   : "Miami Grand Prix",
    "laps"   : 55,
    "date"   : "2022-04-16T00:00:00",
    "podium" : {}
}
');

On the other hand, if you try to update a value for laps that is less than 55:

UPDATE valid_race_dv dv SET data =  JSON('
{
    "_id"   : 204,
    "name"   : "Miami Grand Prix",
    "laps"   : 49,
    "date"   : "2022-04-16T00:00:00",
    "podium" : {}
}
') WHERE dv.data."_id" = 204;

Since the value for laps field did not meet the QBE condition, this DML update operation would end up in an error as displayed:

*
ERROR at line 1:
ORA-42692: Cannot update JSON Relational Duality View 'F1_DV'.'VALID_RACE_DV': 
Error while updating table 'RACE' 
ORA-01402: view WITH CHECK OPTION where-clause violation 

You can use the logical QBE operator _and to combine multiple conditions. The following example creates a duality view to store valid races (laps>=51 and laps<=57):

Example 9-11 Using the GraphQL _and QBE Operator with Duality View Creation Syntax

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW valid_race_dv AS
race (check: {
    _and: [
        {laps: {_gte: 51}},
        {laps: {_lte: 57}}
    ]
}) {
    _id: race_id
    name
    laps
    date: race_date
    podium
};