@WHERE Directive

Use the @WHERE directive for filtering specific information from a larger dataset.

This directive accepts two arguments:

• The argument SQL where you can provide a SQL expression of the predicates.

• The optional argument CHECK which could be TRUE (default) or FALSE. If CHECK parameter is set, then a view will be created WITH CHECK OPTION.

Note: The argument CHECK is applicable only for duality views and cannot be used with the table function.

Syntax:

@WHERE(sql:"SQL EXPRESSION", check:[TRUE\|FALSE])

An example which selects the drivers who have scored points greater than the average points from the driver table:

SELECT JSON_SERIALIZE(data PRETTY) AS data FROM GRAPHQL('
    query getAboveAverageDrivers {
        driver @where(sql: "points >= (select avg(points) from driver)") {
            id: driver_id
            name
            points
        }
    }
');

This produces output that lists drivers whose points are higher than the average:

DATA
--------------------------------------------------------------------------------
{
  "id" : 101,
  "name" : "Lando Norris",
  "points" : 282
}

{
  "id" : 102,
  "name" : "Oscar Piastri",
  "points" : 384
}

{
  "id" : 103,
  "name" : "Charles Leclerc",
  "points" : 312
}

{
  "id" : 104,
  "name" : "Carlos Sainz Jr.",
  "points" : 340
}

{
  "id" : 105,
  "name" : "Max Verstappen",
  "points" : 456
}

{
  "id" : 106,
  "name" : "Sergio P??rez",
  "points" : 133
}

{
  "id" : 107,
  "name" : "Lewis Hamilton",
  "points" : 240
}

{
  "id" : 108,
  "name" : "George Russell",
  "points" : 228
}


8 rows selected.

Here is another example in which you can select races where Max Verstappen is the winner:

SELECT JSON_SERIALIZE(data PRETTY) AS data FROM GRAPHQL('
    query getMaxVerstappenRaceWins {
        driver_race_map @where(sql: "driver_id = (select driver_id from driver where name = ''Max Verstappen'') and position = 1") {
            race @unnest {
                race: name
            }
            position
        }
    }
');

This query searches where the driver’s name is “Max Verstappen” and returns all the races where that driver has finished in the first place.

DATA
--------------------------------------------------------------------------------
{
  "race" : "Bahrain Grand Prix",
  "position" : 1
}

{
  "race" : "Saudi Arabian Grand Prix",
  "position" : 1
}

{
  "race" : "Japanese Grand Prix",
  "position" : 1
}

{
  "race" : "Chinese Grand Prix",
  "position" : 1
}

{
  "race" : "Emilia Romagna Grand Prix",
  "position" : 1
}

{
  "race" : "Canadian Grand Prix",
  "position" : 1
}

{
  "race" : "Spanish Grand Prix",
  "position" : 1
}

{
  "race" : "São Paulo Grand Prix",
  "position" : 1
}

{
  "race" : "Qatar Grand Prix",
  "position" : 1
}


9 rows selected.