GraphQL Variables

Variables keep the queries generic and reusable. Instead of hard coding the a value in the query, using a variable would allow you to pass the value separately.

Support for GraphQL variable in Oracle AI Database is provided by:

Defining the variable in the GraphQL table function using a '$' sign. For example, race (name: $var) in a GraphQL query would imply that the race name would be passed as a variable during the execution.
Defining the bind variable: The keyword passing is used to assign values to the defined GraphQL variables.

Consider a scenario where you would like to obtain the information about a specific race. While you can also do this by using GraphQL arguments, using variables makes the query more generic, eliminating the need to hardcode the input query.

First the bind variable is defined and the EXEC SELECT statement is executed to use it with passing clause in the GraphQL query.

VAR race_name_bind VARCHAR2;
EXEC SELECT 'Miami Grand Prix' INTO :race_name_bind;

Then the value of the variable, in this case, Miami Grand Prix is passed to the variable var in the following example:

Example 3-15 Passing One Variable in Oracle Supported GraphQL Query


-- Get the result for the race specified by the bind variable :race_name_bind
SELECT JSON_SERIALIZE(data PRETTY) AS data FROM GRAPHQL('
    race (name: $var) {
        id: race_id
        name
        date: race_date
        podium
    }
' PASSING :race_name_bind AS "var");

Executing this example would produce the requested details of the race where the name of the race is Miami Grand Prix.

DATA                                                                           
--------------------------------------------------------------------------------
{                                                                              
  "id" : 206,                                                                  
  "name" : "Miami Grand Prix",                                                 
  "date" : "2024-05-05T00:00:00",                                              
  "podium" :                                                                   
  {                                                                            
    "winner" :                                                                 
    {                                                                          
      "name" : "Lando Norris",                                                 
      "time" : "1:31:45"                                                       
    },                                                                         
    "firstRunnerUp" :                                                          
    {                                                                          
      "name" : "Max Verstappen",                                               
      "time" : "1:32:02"                                                       
    },                                                                         
    "secondRunnerUp" :                                                         
    {                                                                          
      "name" : "Carlos Sainz Jr.",                                             
      "time" : "1:32:16"                                                       
    }                                                                          
  }                                                                            
}                                                                              
                                                                                 
 
1 row selected.

Here is another example where multiple variables are combined using the _or QBE operator. The example would chose the variable $raceName or the $raceDate to filter the output depending on which variable is passed during the execution. Consider the scenario where date is defined and passed as the binding variable:

 
VAR race_date_bind VARCHAR2;
EXEC SELECT '2024-07-07' INTO :race_date_bind;

Example 3-16 Combining Multiple Variables using the _or QBE Operator

SELECT JSON_SERIALIZE(data PRETTY) AS data FROM GRAPHQL('
    race (
        check: {
            _or: [
                {name: $raceName},
                {date: $raceDate}
            ]
        }
    ) {
        id: race_id
        name
        date: race_date
        podium
    }
' PASSING :race_name_bind AS "raceName",
          to_date(:race_date_bind, 'YYYY-MM-DD') AS "raceDate");

The code would pass the specified date and produce the requested details of the race corresponding to the specified date.

DATA                                                                           
--------------------------------------------------------------------------------
{                                                                              
  "id" : 206,                                                                  
  "name" : "Miami Grand Prix",                             
  "date" : "2024-05-05T00:00:00",                                                                        
...................
...................              
}                                                                              
                                                                                 
{                                                                              
  "id" : 212,                                                                  
  "name" : "British Grand Prix",                                               
  "date" : "2024-07-07T00:00:00",                                              
  "podium" :                                                                   
  {                                                                            
    "winner" :                                                                 
    {                                                                          
      "name" : "Lewis Hamilton",                                               
      "time" : "1:32:20"                                                       
    },                                                                         
    "firstRunnerUp" :                                                          
    {                                                                          
      "name" : "Lando Norris",                                                 
      "time" : "1:32:39"                                                       
    },                                                                         
    "secondRunnerUp" :                                                         
    {                                                                          
      "name" : "George Russell",                                               
      "time" : "1:32:52"                                                       
    }                                                                          
  }                                                                            
}                                                                              
                                                                                 
 
2 rows selected.