GraphQL Table Function

In addition to accessing the Oracle Database using SQL, starting in 26ai, you can use GraphQL to query the Oracle AI Database tables and get the result in form of JSON objects.

The GraphQL table function acts as a significant addition to RDBMS as it provides the user an alternative to SQL for querying the database tables. Input to this function is a string representing the GraphQL query and the output is a single column called 'DATA' of data type JSON.

Syntax:

select * from graphql('<graphql query>')

Consider an example where you would like to retrieve the details of all the teams and the points that they have scored. You can use the GraphQL table function to query the database for the exact details that you need.

SELECT JSON_SERIALIZE(data PRETTY) AS data FROM GRAPHQL('
    team {
        id: team_id
        name
        points
    }
'
);

This query has a specific structure where you request the server only to return the team_id, name and points of all the teams. And the output would have 10 entries corresponding to the 10 teams which was created in Example 1-2

DATA                                                                           
--------------------------------------------------------------------------------
{                                                                              
  "id" : 301,                                                                  
  "name" : "McLaren Mercedes",                                                 
  "points" : 666                                                               
}                                                                              
                                                                                 
{                                                                              
  "id" : 302,                                                                  
  "name" : "Ferrari",                                                          
  "points" : 652                                                               
}                                                                              
                                                                                 
{                                                                              
  "id" : 303,                                                                  
  "name" : "Red Bull Racing Honda RBPT",                                       
  "points" : 589                                                               
}                                                                              
                                                                                 
{                                                                              
  "id" : 304,                                                                  
  "name" : "Mercedes",                                                         
  "points" : 468                                                               
}                                                                              
                                                                                 
{                                                                              
  "id" : 305,                                                                  
  "name" : "Aston Martin Aramco Mercedes",                                     
  "points" : 94                                                                
}                                                                              
                                                                                 
{                                                                              
  "id" : 306,                                                                  
  "name" : "Alpine Renault",                                                   
  "points" : 65                                                                
}                                                                              
                                                                                 
{                                                                              
  "id" : 307,                                                                  
  "name" : "Haas Ferrari",                                                     
  "points" : 58                                                                
}                                                                              
                                                                                 
{                                                                              
  "id" : 308,                                                                  
  "name" : "RB Honda RBPT",                                                    
  "points" : 46                                                                
}                                                                              
                                                                                 
{                                                                              
  "id" : 309,                                                                  
  "name" : "Williams Mercedes",                                                
  "points" : 17                                                                
}                                                                              
                                                                                 
{                                                                              
  "id" : 310,                                                                  
  "name" : "Kick Sauber Ferrari",                                              
  "points" : 4                                                                 
}                                                                              
                                                                                 
 
10 rows selected.

The GraphQL table function also supports quoted identifiers and fully qualified names. The following sample queries are equivalent to the query specified above and would produce the same output containing id, name and points corresponding to the 10 teams defined in the car racing dataset.

Query Using Quoted Identifiers:

SELECT JSON_SERIALIZE(data PRETTY) AS data FROM GRAPHQL('
    team {
        id: "TEAM_ID"
        name
        points
    }
'
);

Query Using Fully Qualified Names:

SELECT JSON_SERIALIZE(data PRETTY) AS data FROM GRAPHQL('
    team {
        id: team_id
        team.name
        points: team.points
    }
'
);

Starting in 23.26.1, commas (,) in the GraphQL text passed to the GRAPHQL() table function are treated as whitespace. This means commas are optional and repeatable. You can omit them entirely or use multiple commas, and the query semantics remain unchanged.

The following query demonstrates that commas can be omitted or repeated.

SELECT JSON_SERIALIZE(data PRETTY) AS data FROM GRAPHQL('
    team  {
        teamId: team_id,,,
        teamName: name
        drivers: driver {
            driverId: driver_id
            driverName,: name
        }
    },,,
');

Even though the query includes extra commas (and a comma where a field name is normally written without one), the query is still valid and returns the same results as a properly formatted query.

{
  "teamId" : 301,
  "teamName" : "McLaren Mercedes",
  "drivers" :
  [
    {
      "driverId" : 101,
      "driverName" : "Lando Norris"
    },
    {
      "driverId" : 102,
      "driverName" : "Oscar Piastri"
    }
  ]
}
{
  "teamId" : 302,
  "teamName" : "Ferrari",
  "drivers" :
  [
    {
      "driverId" : 103,
      "driverName" : "Charles Leclerc"
    },
    {
      "driverId" : 104,
      "driverName" : "Carlos Sainz Jr."
    }
  ]
}
...

Note:

Oracle enforces a maximum nesting depth for GraphQL evaluation. By default, the maximum nesting depth is 10. The @NEST directive does not affect the nesting level.