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
    }
'
);