GraphQL Table Function

In addition to accessing Oracle AI Database using SQL, starting in 26ai, you can use GraphQL to query 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('
    query {
        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 previously 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('
    query {
        team {
            id: "TEAM_ID"
            name
            points
        }
    }
');

Query Using Fully Qualified Names:

SELECT JSON_SERIALIZE(data PRETTY) AS data FROM GRAPHQL('
    query {
        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('
    query {
        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 typically 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.