Control Null Values in Duality-View Documents

Choose whether duality-view documents include fields with JSON null values or omit fields mapped to SQL NULL.

A JSON-relational duality view can include fields with JSON null values or omit fields whose underlying SQL values are NULL. You choose this behavior when defining the view. By default, a field mapped to a SQL NULL value is included with JSON null as its value.

Choose How Null Values Are Represented

You can specify NULL ON NULL or ABSENT ON NULL in the JSON { ... } syntax of a duality-view definition. GraphQL definitions provide the equivalent behavior through directive @onnull.

SQL clause	Equivalent GraphQL directive	Behavior
NULL ON NULL	@onnull(absent: false)	Include a field whose SQL value is NULL, with JSON null as its value. This is the default.
ABSENT ON NULL	@onnull(absent: true)	Omit a field whose SQL value is NULL.

Use NULL ON NULL or @onnull(absent: false) when applications require an explicit null value. Use ABSENT ON NULL or @onnull(absent: true) when applications require fields with missing values to be omitted. Fields with non-NULL values are included in either case.

Specify Null Handling in SQL

Place the clause after the complete list of fields and before the closing brace of JSON { ... }. Do not place a comma between the final field and the clause.

The clause applies to the field values of the object being constructed, not just to the last field in the list. For a nested object, specify the clause in the constructor for that object.

If the clause is omitted, NULL ON NULL is used.

Specify Null Handling in GraphQL

Attach directive @onnull to the table field whose selection set defines the JSON object. Place it after the table name and before its selection set.

The directive takes Boolean argument absent:

• @onnull(absent: false) includes null-valued fields.
• @onnull(absent: true) omits null-valued fields.

Write true and false without quotation marks. Omitting the directive has the same effect as specifying @onnull(absent: false).

Apply the directive to the root table field to control fields in the root object. For nested objects, apply it to the corresponding nested table field. The examples with nested result objects below apply @onnull to driver_race_map to control fields in each object of the result array.

Examples of Null Handling in the Root Object

The following examples use table race, defined in Car-Racing Example, Tables. Assume that the table contains a row for the Bahrain Grand Prix with race_id 201 and laps 57. Columns race_date and podium contain SQL NULL.

The views map columns race_date and podium to JSON fields date and podium, respectively. The SQL and GraphQL definitions shown for a given view are alternatives that create the same view. For readability, the documents shown below omit the system-generated _metadata field.

Include NULL Fields in the Root Object

This example creates duality view race_null_on_null_dv. The SQL definition specifies NULL ON NULL after the complete list of fields in the root JSON object.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW race_null_on_null_dv AS
  SELECT JSON {
           '_id'    : r.race_id,
           'name'   : r.name,
           'laps'   : r.laps,
           'date'   : r.race_date,
           'podium' : r.podium
           NULL ON NULL
         }
    FROM race r;

The following GraphQL definition creates the same view. Directive @onnull(absent: false) is attached to the root table field, race.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW race_null_on_null_dv AS
  race @onnull(absent: false)
    {_id    : race_id,
     name   : name,
     laps   : laps,
     date   : race_date,
     podium : podium};

The document for race 201 has the following content:

{
  "_id": 201,
  "name": "Bahrain Grand Prix",
  "laps": 57,
  "date": null,
  "podium": null
}

Fields date and podium are present with JSON null values.

Omitting the SQL clause or the GraphQL directive produces the same document content because including null-valued fields is the default.

Omit NULL Fields from the Root Object

This example creates duality view race_absent_on_null_dv. Its SQL definition differs from the preceding SQL definition only in its null-handling clause.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW race_absent_on_null_dv AS
  SELECT JSON {
           '_id'    : r.race_id,
           'name'   : r.name,
           'laps'   : r.laps,
           'date'   : r.race_date,
           'podium' : r.podium
           ABSENT ON NULL
         }
    FROM race r;

The following GraphQL definition creates the same view. Directive @onnull(absent: true) is attached to the root table field.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW race_absent_on_null_dv AS
  race @onnull(absent: true)
    {_id    : race_id,
     name   : name,
     laps   : laps,
     date   : race_date,
     podium : podium};

The document for race 201 has the following content:

{
  "_id": 201,
  "name": "Bahrain Grand Prix",
  "laps": 57
}

Fields date and podium are omitted because their underlying SQL values are NULL.

The omitted fields remain part of the view definition and are included when their underlying values are no longer NULL.

Examples of Null Handling in Nested Result Objects

For the following examples, assume that the date of race 201 is recorded as 20 March 2022, while column podium contains SQL NULL.

Also assume that driver 106 exists and that the only row for race 201 in table driver_race_map has driver_race_map_id 1, driver_id 106, and SQL NULL for position.

The following views include a nested result array in each race document. Each array element corresponds to a matching row of driver_race_map. The nullable position field is inside a result object.

Include a NULL Field in a Nested Result Object

This example creates duality view race_result_null_dv. The SQL definition specifies NULL ON NULL in the JSON object constructed for each driver_race_map row.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW race_result_null_dv AS
  SELECT JSON {
           '_id'    : r.race_id,
           'name'   : r.name,
           'laps'   : r.laps,
           'date'   : r.race_date,
           'podium' : r.podium,
           'result' :
             [ SELECT JSON {
                        'driverRaceMapId' : drm.driver_race_map_id,
                        'driverId'        : drm.driver_id,
                        'position'        : drm.position
                        NULL ON NULL
                      }
                 FROM driver_race_map drm
                WHERE drm.race_id = r.race_id ]
           NULL ON NULL
         }
    FROM race r;

The following GraphQL definition creates the same view. The directive on driver_race_map controls null-valued fields in each object in array result.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW race_result_null_dv AS
  race @onnull(absent: false)
    {_id    : race_id,
     name   : name,
     laps   : laps,
     date   : race_date,
     podium : podium,
     result : driver_race_map @onnull(absent: false)
       [ {driverRaceMapId : driver_race_map_id,
          driverId        : driver_id,
          position        : position} ]};

The document for race 201 has the following content:

{
  "_id": 201,
  "name": "Bahrain Grand Prix",
  "laps": 57,
  "date": "2022-03-20T00:00:00",
  "podium": null,
  "result": [
    {
      "driverRaceMapId": 1,
      "driverId": 106,
      "position": null
    }
  ]
}

The result object contains position with JSON null as its value. The root field date contains the race date. Field podium is present with JSON null as its value because the root object uses NULL ON NULL.

Omit a NULL Field from a Nested Result Object

This example creates duality view race_result_absent_dv. The root JSON object retains NULL ON NULL, while the nested result-object constructor uses ABSENT ON NULL.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW race_result_absent_dv AS
  SELECT JSON {
           '_id'    : r.race_id,
           'name'   : r.name,
           'laps'   : r.laps,
           'date'   : r.race_date,
           'podium' : r.podium,
           'result' :
             [ SELECT JSON {
                        'driverRaceMapId' : drm.driver_race_map_id,
                        'driverId'        : drm.driver_id,
                        'position'        : drm.position
                        ABSENT ON NULL
                      }
                 FROM driver_race_map drm
                WHERE drm.race_id = r.race_id ]
           NULL ON NULL
         }
    FROM race r;

The following GraphQL definition creates the same view. It keeps @onnull(absent: false) on race and places @onnull(absent: true) on the nested driver_race_map field.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW race_result_absent_dv AS
  race @onnull(absent: false)
    {_id    : race_id,
     name   : name,
     laps   : laps,
     date   : race_date,
     podium : podium,
     result : driver_race_map @onnull(absent: true)
       [ {driverRaceMapId : driver_race_map_id,
          driverId        : driver_id,
          position        : position} ]};

The document for race 201 has the following content:

{
  "_id": 201,
  "name": "Bahrain Grand Prix",
  "laps": 57,
  "date": "2022-03-20T00:00:00",
  "podium": null,
  "result": [
    {
      "driverRaceMapId": 1,
      "driverId": 106
    }
  ]
}

The position field is omitted because its underlying SQL value is NULL.

The result object remains in the array. The root field date contains the race date, and podium remains present with JSON null because the root object uses NULL ON NULL. The null-handling setting on driver_race_map controls the fields of the nested result object.

These clauses and directives control the representation of SQL NULL values in generated documents. They do not change the values stored in the underlying tables.

Note: Null handling acts on SQL NULL input values. It does not recursively remove JSON null values already stored inside a non-NULL JSON document.