@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 us 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('
    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.