@ONNULL Directive

The @onnull directive controls whether fields whose values are SQL NULL are omitted from or included in the JSON output.

The directive takes one argument, ABSENT. Set absent to true to omit fields whose values are SQL NULL. Set absent to false to include those fields as JSON null values.

Syntax:

@onnull(absent: true|false)

Note: The examples in this topic use GraphQL table-function queries. The @onnull directive is also supported when creating JSON-relational duality views.

The following statement adds a row with a NULL value to the driver_race_map table for use in the examples:

INSERT INTO driver_race_map
  (driver_race_map_id, race_id, driver_id, position)
VALUES
  (73, 224, 110, NULL);

The following example uses @onnull(absent: false) to include a field whose value is SQL NULL in the response:

SELECT JSON_SERIALIZE(data PRETTY) AS data FROM GRAPHQL('
    query getDriverRaceResultWithNull {
        driver (check: {driver_id: {_eq: 110}}) {
            driverId: driver_id
            driverName: name
            raceResult: driver_race_map @link(to: ["DRIVER_ID"]) @onnull(absent: false) {
                position
            }
        }
    }
') ORDER BY 1;

The output includes the field with a JSON null value:

DATA
--------------------------------------------------------------------------------
{
  "driverId" : 110,
  "driverName" : "Lance Stroll",
  "raceResult" :
  [
    {
      "position" : null
    }
  ]
}

1 row selected.

The following example uses @onnull(absent: true) to omit fields whose values are SQL NULL:

SELECT JSON_SERIALIZE(data PRETTY) AS data FROM GRAPHQL('
    query getDriverRaceResultWithoutNull {
        driver (check: {driver_id: {_eq: 110}}) {
            driverId: driver_id
            driverName: name
            raceResult: driver_race_map @link(to: ["DRIVER_ID"]) @onnull(absent: true) {
                position
            }
        }
    }
') ORDER BY 1;

The output omits the position field from the nested object:

DATA
--------------------------------------------------------------------------------
{
  "driverId" : 110,
  "driverName" : "Lance Stroll",
  "raceResult" :
  [
    {
    }
  ]
}

1 row selected.