@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 beTRUE
(default) orFALSE
. IfCHECK
parameter us set, then a view will be createdWITH CHECK OPTION
.
Note:
The argumentCHECK
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('
driver @where(sql: "points >= (select avg(points) from driver)") {
id: driver_id
name
points
}
');
This would produce an output which lists the above average
drivers:
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('
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 he 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.