WHERE Clauses in Duality-View Tables

When creating a JSON-relational duality view, you can use simple tests in WHERE clauses to not only join underlying tables but to select (filter) which table rows are used to generate JSON data. This allows fine-grained control of the data to be included in a supported JSON document.

As one use case, you can create multiple duality views whose supported JSON documents contain different data, depending on values in discriminating table columns.

For example, using the same underlying table, ORDERS, of purchase orders you could define duality views open_orders and shipped_orders, with the first view selecting rows with clause WHERE order_status="open" from the table and the second view selecting rows with WHERE order_status="shipped".

But note that columns used in the test of a WHERE clause in a duality view need not be used to populate any fields of the supported JSON documents. For example, the selected purchase-order documents for views open_orders and shipped_orders need not have any fields that use values of column order_status.

Each WHERE clause used in a duality-view definition can optionally contain the keywords WITH CHECK OPTION. These keywords prohibit any changes to the table that would produce rows that are not included by the WHERE clause test. See CREATE VIEW in Oracle AI Database SQL Language Reference.

The WHERE clauses you can use in duality-view definitions must be relatively simple - only the following constructs can be used:

Direct comparison of column values with values of other columns of the same underlying table, or with literal values. For example, height > width, height > 3.14. Only ANSI SQL comparison operators are allowed: =, <>, <, <=, >, >=.
A (non-aggregation) SQL expression using a column value, or a Boolean combination of such expressions. For example, upper(department) = 'SALES', salary < 100 and bonus < 15.
Use of SQL JSON constructs: functions and conditions such as json_value and json_exists, as well as simple dot-notation SQL syntax.

In particular, a WHERE clause in a duality-view definition cannot contain the following (otherwise, an error is raised).

Use of a PL/SQL subprogram.
Comparison with the result of a subquery. For example, t.salary > (SELECT max_sal FROM max_sal_table WHERE jobcode=t.job).
Reference to a column in an outer query block.
Use of a bind variable. For example, salary = :var1.
Use of an aggregation operator. For example, sum(salary) < 100.
Use of multiple-column operations. For example, salary + bonus < 10000.
Use of OR between a join condition and another test, in a subquery. Such use would make the join condition optional. For example, e.deptno=d.deptno OR e.job='MANAGER' — in this case, e.deptno=d.deptno is the join condition. (However, OR can be used this way in the top-level/outermost query.)

Example 3-12 WHERE Clause Use in Duality View Definition (SQL)

This example defines duality view race_dv_medal, which is similar to view race_dv (Example 3-5). It differs in that (1) it uses an additional WHERE-clause test to limit field result to the first three race positions (first, second, and third place) and (2) it includes only races more recent than 2019.

CREATE JSON RELATIONAL DUALITY VIEW race_dv_medal AS
  SELECT JSON {'_id'    : r.race_id,
               'name'   : r.name,
               'laps'   : r.laps WITH NOUPDATE,
               'date'   : r.race_date,
               'podium' : r.podium WITH NOCHECK,
               'result' :
                 [ SELECT JSON {'driverRaceMapId' : drm.driver_race_map_id,
                                'position'        : drm.position,
                                UNNEST
                                  (SELECT JSON {'driverId' : d.driver_id,
                                                'name'     : d.name}
                                     FROM driver d WITH NOINSERT UPDATE NODELETE
                                     WHERE d.driver_id = drm.driver_id)}
                     FROM driver_race_map drm WITH INSERT UPDATE DELETE
                     WHERE drm.race_id = r.race_id
                           AND drm.position <= 3 WITH CHECK OPTION ]}
    FROM race r WITH INSERT UPDATE DELETE
    WHERE r.race_date >= to_date('01-JAN-2020') WITH CHECK OPTION;

Example 3-13 WHERE Clause Use in Duality View Definition (GraphQL)

This example defines duality view race_dv_medal using GraphQL. It is equivalent to creating the view using SQL as in Example 3-12.

The view is similar to view race_dv (Example 3-11). It differs in that (1) it uses an additional WHERE-clause test to limit field result to the first three race positions (first, second, and third place) and (2) it includes only races more recent than 2019.

CREATE JSON RELATIONAL DUALITY VIEW race_dv_medal AS
  race @insert @update @delete
       @where (sql: "race_date >= to_date('01-JAN-2020')")
  {_id    : race_id,
   name   : name,
   laps   : laps @noupdate,
   date   : race_date,
   podium : podium @nocheck,
   result : driver_race_map @insert @update @delete
                            @where (sql: "position <= 3")
     {driverRaceMapId : driver_race_map_id,
      position        : position,
      driver @noupdate @nodelete @noinsert
        @unnest
        {driverId : driver_id,
         name     : name}}};

As an alternative to using the second occurrence of GraphQL directive @where to filter driver_race_map_id rows to include, you can instead use this code:

CREATE JSON RELATIONAL DUALITY VIEW race_dv_medal AS
  race @insert @update @delete
       @where (sql: "race_date >= to_date('01-JAN-2020')")
  {_id    : race_id,
   name   : name,
   laps   : laps @noupdate,
   date   : race_date,
   podium : podium @nocheck,
   result : driver_race_map (check: {position: {_lte: 3}})
              @insert @update @delete
     {driverRaceMapId : driver_race_map_id,
      position        : position,
      driver @noupdate @nodelete @noinsert
        @unnest
        {driverId : driver_id,
         name     : name}}};

That uses Oracle GraphQL query-by-example syntax (QBE), (check ...) an enhancement of standard GraphQL syntax. It can be a useful replacement for many uses of @where for filtering. But the remaining use of @where here is too complex to be expressed using check (because of the use of SQL function to_date). See unresolvable-reference.html#GUID-9720B0FF-091C-4335-8156-905ECB9344C0 for duality view specific examples and GraphQL QBEs in Oracle for all GraphQL QBE operators supported in Oracle AI Database. For simple equality predicates on table columns, GraphQL arguments can be used as an alternative to the @WHERE directive. See unresolvable-reference.html#GUID-7D94D50D-3E63-411E-B6BC-6D677A9C68FB for detailed examples.