📋Steps in this guide1/8

GraphQL in Oracle Database 23ai/26ai

This article describes the GraphQL functionality introduced in Oracle Database 23ai/26ai.

oracle 23configurationintermediate

by OracleDba

16 views

What is GraphQL

Here is a quote from Wikipedia. > "GraphQL is a data query and manipulation language for APIs, that allows a client to specify what data it needs ("declarative data fetching"). A GraphQL server can fetch data from separate sources for a single client query and present the results in a unified graph,[2] so it is not tied to any specific database or storage engine."

Setup

We create and populate some tables to work with.

Code/Command (click line numbers to comment):

drop table if exists emp purge;
drop table if exists dept purge;

create table dept (
  deptno number(2) constraint pk_dept primary key,
  dname varchar2(14),
  loc varchar2(13)
) ;

create table emp (
  empno number(4) constraint pk_emp primary key,
  ename varchar2(10),
  job varchar2(9),
  mgr number(4),
  hiredate date,
  sal number(7,2),
  comm number(7,2),
  deptno number(2) constraint fk_deptno references dept
);

create index emp_dept_fk_i on emp(deptno);

insert into dept values
  (10,'ACCOUNTING','NEW YORK'),
  (20,'RESEARCH','DALLAS'),
  (30,'SALES','CHICAGO'),
  (40,'OPERATIONS','BOSTON');

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20),
       (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30),
       (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30),
       (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20),
       (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30),
       (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,30),
       (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,10),
       (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,20),
       (7839,'KING','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10),
       (7844,'TURNER','SALESMAN',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,30),
       (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,20),
       (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,30),
       (7902,'FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,20),
       (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10);
commit;

GraphQL Table Function

The GraphQL table function allows us to query relational data using the GraphQL syntax. In the following example we query the table, defining the required output using GraphQL. The GraphQL shows the structure of the JSON document that will be returned, and references the columns that will provide each element. We can do something similar with the table. In the following example we return a JSON document containing department information, which includes information about the employees in that department. In the previous example the required join was detected automatically because of the foreign key definition. We can explicitly reference the foreign key column using the directive. The column names must be in uppercase, and we can reference a single column when the names match, or an explicit "to, from" for when the column names differ. This time we return employee information including their department name by unnesting the department data using the directive. We can use arguments as a way of filtering data. In the following example we limit the output to department 10 using an argument. The documentation suggest we can restrict the number of rows displayed using the argument, but at the time of writing this produces an error.

Code/Command (click line numbers to comment):

select * from graphql('
department : dept
{
  departmentNumber: deptno
  departmentName : dname
  location : loc
}');

JSON_OBJECT('DEPARTMENTNUMBER'VALUE"DEPTNO",'DEPARTMENTNAME'VALUE"DNAME",'LOCATI
--------------------------------------------------------------------------------
{"departmentNumber":10,"departmentName":"ACCOUNTING","location":"NEW YORK"}
{"departmentNumber":20,"departmentName":"RESEARCH","location":"DALLAS"}
{"departmentNumber":30,"departmentName":"SALES","location":"CHICAGO"}
{"departmentNumber":40,"departmentName":"OPERATIONS","location":"BOSTON"}

SQL>

select * from graphql('
employees : emp
{
  employeeNumber : empno
  employeeName : ename
  job : job
  salary : sal
  hireDate : hiredate
}');

JSON_OBJECT('EMPLOYEENUMBER'VALUE"EMPNO",'EMPLOYEENAME'VALUE"ENAME",'JOB'VALUE"JOB",'SALARY'VALUE"SAL",'HIREDATE'VALUE"HIREDATE"NULLONNULLEMPTYONNOROWSRETURNINGJSONETAG)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"employeeNumber":7369,"employeeName":"SMITH","job":"CLERK","salary":800,"hireDate":"1980-12-17T00:00:00"}
{"employeeNumber":7499,"employeeName":"ALLEN","job":"SALESMAN","salary":1600,"hireDate":"1981-02-20T00:00:00"}
{"employeeNumber":7521,"employeeName":"WARD","job":"SALESMAN","salary":1250,"hireDate":"1981-02-22T00:00:00"}
{"employeeNumber":7566,"employeeName":"JONES","job":"MANAGER","salary":2975,"hireDate":"1981-04-02T00:00:00"}
{"employeeNumber":7654,"employeeName":"MARTIN","job":"SALESMAN","salary":1250,"hireDate":"1981-09-28T00:00:00"}
{"employeeNumber":7698,"employeeName":"BLAKE","job":"MANAGER","salary":2850,"hireDate":"1981-05-01T00:00:00"}
{"employeeNumber":7782,"employeeName":"CLARK","job":"MANAGER","salary":2450,"hireDate":"1981-06-09T00:00:00"}
{"employeeNumber":7788,"employeeName":"SCOTT","job":"ANALYST","salary":3000,"hireDate":"1987-04-19T00:00:00"}
{"employeeNumber":7839,"employeeName":"KING","job":"PRESIDENT","salary":5000,"hireDate":"1981-11-17T00:00:00"}
{"employeeNumber":7844,"employeeName":"TURNER","job":"SALESMAN","salary":1500,"hireDate":"1981-09-08T00:00:00"}
{"employeeNumber":7876,"employeeName":"ADAMS","job":"CLERK","salary":1100,"hireDate":"1987-05-23T00:00:00"}
{"employeeNumber":7900,"employeeName":"JAMES","job":"CLERK","salary":950,"hireDate":"1981-12-03T00:00:00"}
{"employeeNumber":7902,"employeeName":"FORD","job":"ANALYST","salary":3000,"hireDate":"1981-12-03T00:00:00"}
{"employeeNumber":7934,"employeeName":"MILLER","job":"CLERK","salary":1300,"hireDate":"1982-01-23T00:00:00"}

14 rows selected.

SQL>

select * from graphql('
department : dept
{
  departmentNumber: deptno
  departmentName : dname
  location : loc
  employees : emp
  [{
    employeeNumber : empno
    employeeName : ename
    job : job
    salary : sal
  }]
}');

JSON_OBJECT('DEPARTMENTNUMBER'VALUE"DEPTNO",'DEPARTMENTNAME'VALUE"DNAME",'LOCATION'VALUE"LOC",'EMPLOYEES'VALUE(SELECTJSON_ARRAYAGG(JSON_OBJECT('EMPLOYEENUMBER'VALUE"EMPNO",'EMPLOYEENAME'VALUE"ENAME",
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"departmentNumber":10,"departmentName":"ACCOUNTING","location":"NEW YORK","employees":[{"employeeNumber":7782,"employeeName":"CLARK","job":"MANAGER","salary":2450},{"employeeNumber":7839,"employeeNam
e":"KING","job":"PRESIDENT","salary":5000},{"employeeNumber":7934,"employeeName":"MILLER","job":"CLERK","salary":1300}]}

{"departmentNumber":20,"departmentName":"RESEARCH","location":"DALLAS","employees":[{"employeeNumber":7369,"employeeName":"SMITH","job":"CLERK","salary":800},{"employeeNumber":7566,"employeeName":"JON
ES","job":"MANAGER","salary":2975},{"employeeNumber":7788,"employeeName":"SCOTT","job":"ANALYST","salary":3000},{"employeeNumber":7876,"employeeName":"ADAMS","job":"CLERK","salary":1100},{"employeeNum
ber":7902,"employeeName":"FORD","job":"ANALYST","salary":3000}]}

{"departmentNumber":30,"departmentName":"SALES","location":"CHICAGO","employees":[{"employeeNumber":7499,"employeeName":"ALLEN","job":"SALESMAN","salary":1600},{"employeeNumber":7521,"employeeName":"W
ARD","job":"SALESMAN","salary":1250},{"employeeNumber":7654,"employeeName":"MARTIN","job":"SALESMAN","salary":1250},{"employeeNumber":7698,"employeeName":"BLAKE","job":"MANAGER","salary":2850},{"emplo
yeeNumber":7844,"employeeName":"TURNER","job":"SALESMAN","salary":1500},{"employeeNumber":7900,"employeeName":"JAMES","job":"CLERK","salary":950}]}

{"departmentNumber":40,"departmentName":"OPERATIONS","location":"BOSTON","employees":[]}

SQL>

select * from graphql('
department : dept
{
  departmentNumber: deptno
  departmentName : dname
  location : loc
  employees : emp @link (to: ["DEPTNO"])
  [{
    employeeNumber : empno
    employeeName : ename
    job : job
    salary : sal
  }]
}');


select * from graphql('
department : dept
{
  departmentNumber: deptno
  departmentName : dname
  location : loc
  employees : emp @link (from: ["DEPTNO"], to: ["DEPTNO"])
  [{
    employeeNumber : empno
    employeeName : ename
    job : job
    salary : sal
  }]
}');

select * from graphql('
employees : emp
{
  employeeNumber : empno
  employeeName : ename
  job : job
  salary : sal
  dept @unnest
  {
    departmentName : dname
  }
}');

JSON_OBJECT('EMPLOYEENUMBER'VALUE"EMPNO",'EMPLOYEENAME'VALUE"ENAME",'JOB'VALUE"JOB",'SALARY'VALUE"SAL",UNNEST(SELECTJSON_OBJECT('DEPARTMENTNAME'VALUE"DNAME"NULLONNULLEMPTYONNOROWSRETURNINGJSONETAG)FR
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"employeeNumber":7369,"employeeName":"SMITH","job":"CLERK","salary":800,"departmentName":"RESEARCH"}
{"employeeNumber":7499,"employeeName":"ALLEN","job":"SALESMAN","salary":1600,"departmentName":"SALES"}
{"employeeNumber":7521,"employeeName":"WARD","job":"SALESMAN","salary":1250,"departmentName":"SALES"}
{"employeeNumber":7566,"employeeName":"JONES","job":"MANAGER","salary":2975,"departmentName":"RESEARCH"}
{"employeeNumber":7654,"employeeName":"MARTIN","job":"SALESMAN","salary":1250,"departmentName":"SALES"}
{"employeeNumber":7698,"employeeName":"BLAKE","job":"MANAGER","salary":2850,"departmentName":"SALES"}
{"employeeNumber":7782,"employeeName":"CLARK","job":"MANAGER","salary":2450,"departmentName":"ACCOUNTING"}
{"employeeNumber":7788,"employeeName":"SCOTT","job":"ANALYST","salary":3000,"departmentName":"RESEARCH"}
{"employeeNumber":7839,"employeeName":"KING","job":"PRESIDENT","salary":5000,"departmentName":"ACCOUNTING"}
{"employeeNumber":7844,"employeeName":"TURNER","job":"SALESMAN","salary":1500,"departmentName":"SALES"}
{"employeeNumber":7876,"employeeName":"ADAMS","job":"CLERK","salary":1100,"departmentName":"RESEARCH"}
{"employeeNumber":7900,"employeeName":"JAMES","job":"CLERK","salary":950,"departmentName":"SALES"}
{"employeeNumber":7902,"employeeName":"FORD","job":"ANALYST","salary":3000,"departmentName":"RESEARCH"}
{"employeeNumber":7934,"employeeName":"MILLER","job":"CLERK","salary":1300,"departmentName":"ACCOUNTING"}

14 rows selected.

SQL>

select * from graphql('
department : dept (deptno: 10)
{
  departmentNumber: deptno
  departmentName : dname
  location : loc
}');

DATA
--------------------------------------------------------------------------------
{"departmentNumber":10,"departmentName":"ACCOUNTING","location":"NEW YORK"}

SQL>

select * from graphql('
department : dept (limit: 2)
{
  departmentNumber: deptno
  departmentName : dname
  location : loc
}');
  *
ERROR at line 7:
ORA-00904: "LIMIT": invalid identifier
Help: https://docs.oracle.com/error-help/db/ora-00904/

SQL>

GraphQL Directives

GraphQL Directives give us additional control over queries. We've already seen examples of the and directives above. The directive allows us to filter data, similar to a WHERE clause in SQL. In the following example we use the SQL arguments to filter the data. When using duality views we can optionally use the argument, which defaults to TRUE, to flip the result of the filter. This is ignored when using the table function. The directive allows us to order array elements in the output of a GraphQL query. The directive gives us an alternative way to specify an array of items. As we saw in the previous section, we can do this my manually by including the "[]" characters, or we can use the directive, so both of the following queries are functionally identical. The directive allows us to nest a JSON object into the output. The directive allows us to generate a field from existing data. In the following example we have an element called compensation that is made up to the salary and commission combined. The directive allows us to nest data in the structure that would otherwise be flat. In the following example we nest the salary and commission as a compensation element. The directive allows us to associate an alias with a table, to make references to columns easier where there might otherwise be ambiguous references. In the following example we alias both the and tables, and use their aliases in the column references. There are additional directives ( , ) that are specific for duality views, and can't be used by the GraphQL table function.

Code/Command (click line numbers to comment):

select * from graphql('
department : dept @where(sql: "deptno > 10")
{
  departmentNumber: deptno
  departmentName : dname
  location : loc
}');

DATA
--------------------------------------------------------------------------------
{"departmentNumber":20,"departmentName":"RESEARCH","location":"DALLAS"}
{"departmentNumber":30,"departmentName":"SALES","location":"CHICAGO"}
{"departmentNumber":40,"departmentName":"OPERATIONS","location":"BOSTON"}

SQL>


select * from graphql('
department : dept @where(sql: "deptno > (select min(deptno) from dept)")
{
  departmentNumber: deptno
  departmentName : dname
  location : loc
}');

DATA
--------------------------------------------------------------------------------
{"departmentNumber":20,"departmentName":"RESEARCH","location":"DALLAS"}
{"departmentNumber":30,"departmentName":"SALES","location":"CHICAGO"}
{"departmentNumber":40,"departmentName":"OPERATIONS","location":"BOSTON"}

SQL>

select * from graphql('
department : dept @where(sql: "deptno = 10")
{
  departmentNumber: deptno
  departmentName : dname
  location : loc
  employees : emp @orderby(sql: "ename desc")
  [{
    employeeNumber : empno
    employeeName : ename
    job : job
    salary : sal
  }]
}');

DATA
--------------------------------------------------------------------------------
{"departmentNumber":10,"departmentName":"ACCOUNTING","location":"NEW YORK","empl
oyees":[{"employeeNumber":7934,"employeeName":"MILLER","job":"CLERK","salary":13
00},{"employeeNumber":7839,"employeeName":"KING","job":"PRESIDENT","salary":5000
},{"employeeNumber":7782,"employeeName":"CLARK","job":"MANAGER","salary":2450}]}

SQL>

select * from graphql('
department : dept
{
  departmentNumber: deptno
  departmentName : dname
  location : loc
  employees : emp
  [{
    employeeNumber : empno
    employeeName : ename
    job : job
    salary : sal
  }]
}');


select * from graphql('
department : dept
{
  departmentNumber: deptno
  departmentName : dname
  location : loc
  employees : emp @array
  {
    employeeNumber : empno
    employeeName : ename
    job : job
    salary : sal
  }
}');

select * from graphql('
employees  : emp @where (sql: "rownum = 1")
{
  employeeNumber : empno
  employeeName : ename
  department : dept @object {
    departmentNumber : deptno
    DepartmentName : dname
  }
}');

DATA
--------------------------------------------------------------------------------
{"employeeNumber":7369,"employeeName":"SMITH","department":{"departmentNumber":2
0,"DepartmentName":"RESEARCH"}}

SQL>

select * from graphql('
employees  : emp @where (sql: "rownum = 1")
{
  employeeNumber : empno
  employeeName : ename
  compensation @generated(sql: "sal + nvl(comm,0)")
}');

DATA
--------------------------------------------------------------------------------
{"employeeNumber":7369,"employeeName":"SMITH","compensation":800}

SQL>

select * from graphql('
employees  : emp @where (sql: "rownum = 1")
{
  employeeNumber : empno
  employeeName : ename
  compensation @nest
  {
    salary : sal
    commission : comm
  }
}');

DATA
--------------------------------------------------------------------------------
{"employeeNumber":7369,"employeeName":"SMITH","compensation":{"salary":800,"comm
ission":null}}

SQL>

select * from graphql('
department : dept @alias(as: d)
{
  departmentNumber: d.deptno
  departmentName : d.dname
  location : d.loc
  employees : emp @alias(as: e) @array
  {
    employeeNumber : e.empno
    employeeName : e.ename
    job : e.job
    salary : e.sal
  }
}');

DATA
--------------------------------------------------------------------------------
{"departmentNumber":10,"departmentName":"ACCOUNTING","location":"NEW YORK","empl
oyees":[{"employeeNumber":7782,"employeeName":"CLARK","job":"MANAGER","salary":2
450},{"employeeNumber":7839,"employeeName":"KING","job":"PRESIDENT","salary":500
0},{"employeeNumber":7934,"employeeName":"MILLER","job":"CLERK","salary":1300}]}

{"departmentNumber":20,"departmentName":"RESEARCH","location":"DALLAS","employee
s":[{"employeeNumber":7369,"employeeName":"SMITH","job":"CLERK","salary":800},{"
employeeNumber":7566,"employeeName":"JONES","job":"MANAGER","salary":2975},{"emp
loyeeNumber":7788,"employeeName":"SCOTT","job":"ANALYST","salary":3000},{"employ
eeNumber":7876,"employeeName":"ADAMS","job":"CLERK","salary":1100},{"employeeNum
ber":7902,"employeeName":"FORD","job":"ANALYST","salary":3000}]}

{"departmentNumber":30,"departmentName":"SALES","location":"CHICAGO","employees"
:[{"employeeNumber":7499,"employeeName":"ALLEN","job":"SALESMAN","salary":1600},
{"employeeNumber":7521,"employeeName":"WARD","job":"SALESMAN","salary":1250},{"e
mployeeNumber":7654,"employeeName":"MARTIN","job":"SALESMAN","salary":1250},{"em
ployeeNumber":7698,"employeeName":"BLAKE","job":"MANAGER","salary":2850},{"emplo
yeeNumber":7844,"employeeName":"TURNER","job":"SALESMAN","salary":1500},{"employ
eeNumber":7900,"employeeName":"JAMES","job":"CLERK","salary":950}]}

{"departmentNumber":40,"departmentName":"OPERATIONS","location":"BOSTON","employ
ees":[]}

SQL>

GraphQL Variables

We can pass variables to a GraphQL query using the keyword. In this example we use a literal to limit the department to 10. We can also pass a SQL*Plus or PL/SQL variable.

Code/Command (click line numbers to comment):

select * from graphql('
dept (deptno: $v_deptno)
{
  departmentNumber: deptno
  departmentName : dname
  location : loc
}'
passing 10 AS "v_deptno");

DATA
--------------------------------------------------------------------------------
{"departmentNumber":10,"departmentName":"ACCOUNTING","location":"NEW YORK"}

SQL>

variable v_deptno number
exec :v_deptno := 20;

select * from graphql('
dept (deptno: $v_deptno)
{
  departmentNumber: deptno
  departmentName : dname
  location : loc
}'
passing :v_deptno AS "v_deptno");

DATA
--------------------------------------------------------------------------------
{"departmentNumber":20,"departmentName":"RESEARCH","location":"DALLAS"}

SQL>

GraphQL Filter Specifications: QBE

We've seen basic equality checks to limit data in previous sections, but we can also perform comparisons using the query-by-example (QBE) syntax. Oracle supports relational operators, logical operators and item method operators. You can see examples of these in the documentation here .

GraphQL and JSON-Relational Duality Views

There are two ways to create JSON-relational duality views. - SQL syntax - The view is defined using SQL/JSON function calls in SQL. - GraphQL syntax - The view is defined using GraphQL, which is converted to SQL behind the scenes. Examples of using GraphQL to define JSON-relational duality views can be found here .

GraphQL and Oracle REST Data Services (ORDS)

GraphQL support was added to Oracle REST Data Services (ORDS) version 23.3. This is not related to the 23ai/26ai functionality, but may be worth considering if you are already using ORDS. - Oracle REST Data Services (ORDS) : GraphQL For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!