GraphQL in Oracle REST Data Services
This section introduces GraphQL functionality in Oracle REST Data Services.
The GraphQL feature in Oracle REST Data Services enables you to fetch the data from an Oracle REST Data Services enabled schema using GraphQL queries.
-
GraphQL Terminology This section describes the common terms used in this section.
-
Enabling GraphQL in Oracle REST Data Services This section describes how to enable GraphQL.
-
Enabling Objects for GraphQL This section explains how to enable the objects for GraphQL.
-
Accessing Objects Using GraphQL queries This section provides examples for using GraphQL queries against tables and views after REST-enabling the tables and views.
-
Examples of Filtering in Queries This section provides examples of filtering in queries against REST-enabled tables and views.
GraphQL Terminology
This section describes the common terms used in this section.
Following are the common terms used in this section:
-
GraphQL Schema Definition Language (SDL): Sometimes it is simply referred to as GraphQL schema language. It is a language with a simple syntax that allows to define a schema.
-
Schema: A schema in the GraphQL context refers to a collection of GraphQL types.
-
Type: Represents a kind of object that you can fetch from your service. Each REST-Enabled table or view object in Oracle REST Data Services represents a GraphQL type.
-
Field: A GraphQL type contains a set of fields that you can fetch in a query. Every column of a table or view object in Oracle REST Data Services represents a field.
Enabling GraphQL in Oracle REST Data Services
This section describes how to enable GraphQL.
To enable GraphQL, Oracle REST Data Services is required to run in a GraalVM runtime environment with the Java Script component enabled.
See Also: System Requirements
Enabling Objects for GraphQL
This section explains how to enable the objects for GraphQL.
Any REST-Enabled table or view of an Oracle REST Data Services enabled schema can be accessed through GraphQL queries. For a REST-Enabled object to be mapped into a GraphQL type, it is necessary that it has one or multiple primary keys associated to the object. If this condition is not satisfied, then the ROWID pseudo column is used to guarantee that the objects obtained in a query are unique and are not a duplicate derived from a join. Note: The use of ROWID as an identifier has some limitations.
GraphQL endpoint syntax:
http://<HOST>:<PORT>/ords/<Schema>/_/graphqlNote: This feature is available only for Oracle REST Data Services enabled schemas.
Accessing Protected REST-Enabled Objects
Any privilege or role defined by the user can protect the REST-Enabled objects that require authorization. For example, if a REST-enabled object is protected by the autoREST default privilege or role it requires the following roles and privileges to access such object::
-
oracle.dbtools.autorest.any.schema -
oracle.dbtools.role.autorest.<SCHEMANAME>.<OBJECTNAME> -
oracle.dbtools.autorest.privilege.<SCHEMANAME>.<OBJECTNAME>This means that, GraphQL request must have proper authorization in order to have access to the protected objects.This protection is not limited to the auto REST privileges and roles listed above since the GraphQL feature honors URI pattern protection.
See Also: About Oracle REST Data Services User Roles
Accessing Objects Using GraphQL queries
This section provides examples for using GraphQL queries against tables and views after REST-enabling the tables and views.
Following examples are discueed in this section:
-
Simple Query A simple query retrieves the data in a type present in the GraphQL Schema.
-
Join Query A join query retrieves the data from one or more relationships between existing types present in the GraphQL Schema.
Getting GraphQL Schema
The GraphQL schema is auto generated and it contains the REST-enabled objects (tables and views) of the rest enabled user database schema.
The generated schema includes the following:
-
Each REST-enabled object represented as a GraphQL type with its columns represented as fields and the relationships between the objects.
-
The resolvers for all the REST-enabled objects
-
Supported data types
To get the GraphQL schema, run the following query:
Syntax:
GET 'http://<HOST>:<PORT>/ords/<Schema>/_/graphql'Example query:
GET 'http://localhost:8080/ords/hr/_/graphql'Response:
{"schemaName":"HR","description":"the SDL representation of the 'HR' GraphQL Schema","SDL":"type Query { """Generic resolver for EMPLOYEES type."""\n
employees(primaryKey: JSON, where: JSON, sort: JSON, limit: Int, offset: Int):
[EMPLOYEES]\n\n """Generic resolver for COUNTRIES type."""\n
countries(primaryKey: JSON, where: JSON, sort: JSON, limit: Int, offset: Int):
[COUNTRIES]\n}\n\n"""\nThe 'Date' scalar type represents date values as specified by the
ISO 8601 format in UTC time zone (YYYY-MM-DDThh:mm:ssZ).\n"""\nscalar
Date\n\n"""\nThe `Float` scalar type represents signed double-precision fractional
values as specified by [IEEE 754](https://en.wikipedia.org/wiki/IEEE_floating_point).\n"""\nscalar
Float\n\n"""\nThe `Int` scalar type represents non-fractional signed whole numeric
values. Int can represent values between -(2^31) and 2^31 -
1.\n"""\nscalar
Int\n\n"""\nThe `JSON` scalar type represents JSON values as specified by [ECMA-404](http://www.ecma-international.org/publications/files/ECMA-ST/ECMA-404.pdf).\n"""\nscalar
JSON\n\n"""\nThe `String` scalar type represents textual data, represented as UTF-8
character sequences. The String type is most often used by GraphQL to represent free-form
human-readable text.\n"""\nscalar String\n\ntype COUNTRIES {\n country_id: String!\n
country_name: String\n region_id: Int\n}\n\ntype EMPLOYEES {\n employee_id: Int!\n
manager_id: Int\n phone_number: String\n commission_pct: Float\n department_id: Int\n
salary: Float\n first_name: String\n email: String!\n job_id: String!\n hire_date:
Date!\n last_name: String!\n\n """\n The relationship between the EMPLOYEES type
and the EMPLOYEES type on EMPLOYEES.MANAGER_ID = EMPLOYEES.EMPLOYEE_ID\n """\n
manager_id_employees(primaryKey: JSON, where: JSON, sort: JSON, limit: Int, offset: Int):
[EMPLOYEES]\n\n """\n The relationship between the EMPLOYEES type and the EMPLOYEES
type on EMPLOYEES.EMPLOYEE_ID = EMPLOYEES.MANAGER_ID\n """\n
employees_manager_id(primaryKey: JSON, where: JSON, sort: JSON, limit: Int, offset: Int):
[EMPLOYEES]\n}"}Simple Query
A simple query retrieves the data in a type present in the GraphQL Schema.
This example query fetches the employee_id, first_name, last_name, job_id, and salary in the employees type from the HR schema.
query Employees {
employees {
employee_id
first_name
last_name
job_id
salary
}
}Example cURL command:
curl --location 'http://localhost:8080/ords/hr/_/graphql' \
--header 'Content-Type: application/json' \
--data '{
"query": "{employees { employee_id first_name last_name job_id salary }}"
}'Response:
{
"data": {
"employees": [
{
"employee_id": 100,
"first_name": "Steven",
"last_name": "King",
"job_id": "AD_PRES",
"salary": 24000
},
{
"employee_id": 101,
"first_name": "Neena",
"last_name": "Kochhar",
"job_id": "AD_VP",
"salary": 17000
},
{
"employee_id": 103,
"first_name": "Alexander",
"last_name": "Hunold",
"job_id": "IT_PROG",
"salary": 9000
},
{
"employee_id": 104,
"first_name": "Bruce",
"last_name": "Ernst",
"job_id": "IT_PROG",
"salary": 6000
},
{
"employee_id": 105,
"first_name": "David",
"last_name": "Austin",
"job_id": "IT_PROG",
"salary": 4800
},
...
}Join Query
A join query retrieves the data from one or more relationships between existing types present in the GraphQL Schema.
Example 1:
The following query fetches all the cities associated with a location as well as the departments in each city and the employees who work in each one of the departments.
query Locations{
locations{
city
departments_location_id{
department_name
employees_department_id{
first_name
last_name
salary
}
}
}
}Example cURL Command:
curl --location 'http://localhost:8080/ords/hr/_/graphql' \
--header 'Content-Type: application/json' \
--data '{
"query": "query Locations{ locations{ city departments_location_id{ department_name employees_department_id{first_name last_name salary} } } }"
}'Response:
{
"data": {
"locations": [
{
"city": "Seattle",
"departments_location_id": [
{
"department_name": "Executive",
"employees_department_id": [
{
"first_name": "Steven",
"last_name": "King",
"salary": 24000
},
{
"first_name": "Neena",
"last_name": "Kochhar",
"salary": 17000
},
{
"first_name": "Lex",
"last_name": "De Haan",
"salary": 17000
}
]
},
{
"department_name": "Finance",
"employees_department_id": [
{
"first_name": "Nancy",
"last_name": "Greenberg",
"salary": 12000
},
{
"first_name": "Daniel",
"last_name": "Faviet",
"salary": 9000
},
{
"first_name": "John",
"last_name": "Chen",
"salary": 8200
},
{
"first_name": "Ismael",
"last_name": "Sciarra",
"salary": 7700
},
{
"first_name": "Jose Manuel",
"last_name": "Urman",
"salary": 7800
},
{
"first_name": "Luis",
"last_name": "Popp",
"salary": 6900
}
]
},
{
"department_name": "Purchasing",
"employees_department_id": [
{
"first_name": "Den",
"last_name": "Raphaely",
"salary": 11000
},
{
"first_name": "Alexander",
"last_name": "Khoo",
"salary": 3100
},
{
"first_name": "Shelli",
"last_name": "Baida",
"salary": 2900
},
{
"first_name": "Sigal",
"last_name": "Tobias",
"salary": 2800
},
{
"first_name": "Guy",
"last_name": "Himuro",
"salary": 2600
},
{
"first_name": "Karen",
"last_name": "Colmenares",
"salary": 2500
}
]
},
{
"department_name": "Administration",
"employees_department_id": [
{
"first_name": "Jennifer",
"last_name": "Whalen",
"salary": 4400
}
]
},
{
"department_name": "Accounting",
"employees_department_id": [
{
"first_name": "Shelley",
"last_name": "Higgins",
"salary": 12000
},
{
"first_name": "William",
"last_name": "Gietz",
"salary": 8300
}
]
},
{
"department_name": "IT Support",
"employees_department_id": []
},
{
"department_name": "Operations",
"employees_department_id": []
},
{
"department_name": "Payroll",
"employees_department_id": []
},
{
"department_name": "Construction",
"employees_department_id": []
},
{
"department_name": "Government Sales",
"employees_department_id": []
},
{
"department_name": "Retail Sales",
"employees_department_id": []
},
{
"department_name": "Contracting",
"employees_department_id": []
},
{
"department_name": "Recruiting",
"employees_department_id": []
},
{
"department_name": "Control And Credit",
"employees_department_id": []
},
{
"department_name": "NOC",
"employees_department_id": []
},
{
"department_name": "Treasury",
"employees_department_id": []
},
{
"department_name": "Manufacturing",
"employees_department_id": []
},
{
"department_name": "Corporate Tax",
"employees_department_id": []
},
{
"department_name": "IT Helpdesk",
"employees_department_id": []
},
{
"department_name": "Shareholder Services",
"employees_department_id": []
},
{
"department_name": "Benefits",
"employees_department_id": []
}
]
}
]
}
}Example 2:
The following example query fetches all the employees from the HR schema and the department in which they work:
query Employees {
employees {
employee_id
first_name
last_name
departments_department_id {
department_id
department_name
}
}
}Example cURL command:
curl --location 'http://localhost:8080/ords/hr/_/graphql' \
--header 'Content-Type: application/json' \
--data '{
"query": "{employees { employee_id first_name last_name departments_department_id{ department_id department_name } }}"
}'Response:
{
"data": {
"employees": [
{
"employee_id": 200,
"first_name": "Jennifer",
"last_name": "Whalen",
"departments_department_id": [
{
"department_id": 10,
"department_name": "Administration"
}
]
},
{
"employee_id": 201,
"first_name": "Michael",
"last_name": "Hartstein",
"departments_department_id": [
{
"department_id": 20,
"department_name": "Marketing"
}
]
},
{
"employee_id": 202,
"first_name": "Pat",
"last_name": "Fay",
"departments_department_id": [
{
"department_id": 20,
"department_name": "Marketing"
}
]
},...
]
}
}Note: GraphQL nesting depth is limited to a maximum of five levels. Any query with more than five nested joins returns an error. See Also:Understanding Configurable Settings
- Circular Relationships Between Objects This section explains with an example a circular relationship.
Circular Relationships Between Objects
This section explains with an example a circular relationship.
A table or view can have a circular relationship and GraphQL can be used to query the data.
Following is an example showing a circular relationship in the HR schema.
The employees table has a constraint defined between manager_id and employee_id columns.
The following example query fetches all the employees from the HR schema along with their respective managers:
query Employees {
employees {
employee_id
first_name
last_name
manager_id
manager_id_employees {
first_name
last_name
employee_id
}
}
}Example cURL Command:
curl --location 'http://localhost:8080/ords/hr/_/graphql' \
--header 'Content-Type: application/json' \
--data '{
"query": "{ employees { employee_id first_name last_name manager_id employees_manager_id{ first_name last_name employee_id } } }"
}'Response:
{
"data": {
"employees": [
{
"employee_id": 101,
"first_name": "Neena",
"last_name": "Kochhar",
"manager_id": 100,
"employees_manager_id": [
{
"first_name": "Steven",
"last_name": "King",
"employee_id": 100
}
]
},
{
"employee_id": 114,
"first_name": "Den",
"last_name": "Raphaely",
"manager_id": 100,
"employees_manager_id": [
{
"first_name": "Steven",
"last_name": "King",
"employee_id": 100
},
{
"first_name": "Eleni",
"last_name": "Zlotkey",
"employee_id": 149
}
]
},
{
"employee_id": 120,
"first_name": "Matthew",
"last_name": "Weiss",
"manager_id": 100,
"employees_manager_id": [
{
"first_name": "Steven",
"last_name": "King",
"employee_id": 100
},
{
"first_name": "John",
"last_name": "Russell",
"employee_id": 145
},
{
"first_name": "Karen",
"last_name": "Partners",
"employee_id": 146
}
]
}
}
}Examples of Filtering in Queries
This section provides examples of filtering in queries against REST-enabled tables and views.
To filter in a query, include the parameter <filterName>: GraphQLJSON, where GraphQLJSON is a JSON like object that represents the custom selection to be applied to the resource. Each filter has its own predefined GraphQLJSON syntax.
-
Supported Data Types This section lists the supported data types for filters.
-
Accessing REST-Enabled Objects that Contain Special Characters GraphQL does not support special characters in primary key(s) of the queried object.
Supported Data Types
This section lists the supported data types for filters.
| Data Type | Description |
|---|---|
String |
The string scalar type represents a textual data, represented as UTF-8 character sequences. The string type is most often used by GraphQL to represent free-form human-readable text. |
Int |
The int scalar type represents non-fractional signed whole numeric values. Int can represent values between -(2^31) and 2^31 - |
| 1. | |
Float |
The float scalar type represents signed double-precision fractional values as specified by IEEE 754. |
Date |
The date scalar type represents date values as specified by the ISO 8601 format in UTC time zone (YYYY-MM-DDThh:mm:ssZ). |
Timestamp |
The timestamp scalar type represents timestamp values as specified by the ISO 8601 format in UTC time zone (YYYY-MM-DDThh:mm:ss.sssZ). |
Boolean |
The boolean scalar type represents true or false. |
Filtering by Primary Key
Filtering by primary key enables you to retrieve the data by specifying its identifying key value or key values.
Primary Key Syntax:
value = String | Int | Float | Date | Timestamp
primaryKeyPair = <fieldName> : <value>
primaryKeyExp = { primaryKeyPair1, ... , primaryKeyPairN }The following query includes a filter that restricts the employee_id field to 100:
query {
employees(primaryKey: {employee_id: 100}){
employee_id
first_name
last_name
job_id
salary
}
}Example cURL command:
curl --location 'http://localhost:8080/ords/hr/_/graphql' \
--header 'Content-Type: application/json' \
--data '{
"query": "{ employees(primaryKey : {employee_id :100}) { first_name last_name department_id job_id } } "
}'Response:
{
"data": {
"employees": [
{
"first_name": "Steven",
"last_name": "King",
"department_id": 90,
"job_id": "AD_PRES"
}
]
}
}Note: This specific filter does not work if the primary key(s) of the queried object contains a special character such as . , $ @and #. If the primary key(s) of the object contains any of these special characters you can still filter using any of the filters described in the Where Filter section.
Filtering by Composite Primary Key
Filtering by primary key enables you to retrieve the data from the tables that have a composite primary key by adding a list of primary keys to the filter.
query {
compositeTable(primaryKey: { <fieldName> : <value>, <fieldName> : <value>}){
data
}
}Where Filter
Filtering using a where condition enables you to query the data and specify a valid condition or conditions that the fields present in the requested types should satisfy.
Where Filter Syntax:
fieldName = stringvalue = String | Int | Float | Date | Timestamp operator = eq | neq | gt | lt | gte | lte | like | nlike | in | nin | btwn | nbtwn
| nullbooleanOperator = and | orvalidFilter = { <fieldName> : { <operator> : <value> } }booleanExp = { <booleanOperator> : [ <ValidFilter1 | BoleanExp1>, ..., <ValidFilterN |
BoleanExpN> ] }whereExp = { where : <validFilter | booleanExp> }Table 1 Supported Operators
| Operator | GraphQLJSON Syntax | Description | Supported Data Types |
|---|---|---|---|
= |
{ column : { eq : value } } |
Equality | String | Int | Float | Date | Timestamp |
!=, <> |
{ column : { neq : value } } |
Inequality | String | Int | Float | Date | Timestamp |
> |
{ column : { gt : value } } |
Greater than | String | Int | Float | Date | Timestamp |
< |
{ column : { lt : value } } |
Less than | String | Int | Float | Date | Timestamp |
>= |
{ column : { gte : value } } |
Greater than or equal to | String | Int | Float | Date | Timestamp |
<= |
{ column : { lte : value } } |
Less than or equal to | String | Int | Float | Date | Timestamp |
LIKE |
{ column : { like : pattern } } |
Operator used for pattern matching | String |
NOT LIKE |
{ column : { nlike : pattern } } |
Operator used for pattern matching | String |
IN |
{ column : { in : [value1_, ... , value_n ] } } |
Equal to any value in a list of values | String | Int | Float | Date | Timestamp |
NOT IN |
{ column : { nin : [value_1, ... ,value_n] } } |
Not equal to any value in a list of values | String | Int | Float | Date | Timestamp |
BETWEEN |
{ column : { btwn : [value_1, value_2] } } |
Equivalent to >= n and <= y | String | Int | Float | Date | Timestamp |
NOT BETWEEN |
{ column : { nbtwn : [value_1, value_2] } } |
Equivalent to NOT >= n and <= y | String | Int | Float | Date | Timestamp |
IS NULL |
{ column : { null: [ Boolean ] } } |
NULL test | Boolean |
OR |
|
Logical operator, returns true if any expression is true. |
Not Applicable |
NOT |
{ NOT : { GraphQL expression}} |
Logical operator, negates the logical value of the expression on which it operates. | Not Applicable |
| AND | |
Logical operator, returns true if both expressions are true. |
Not Applicable |
Example: EQUALS (eq) operator
The following query includes a filter that restricts the the job_id field to IT_PROG.
query {
employees(where : {job_id: {eq : "IT_PROG"}}){
employee_id
first_name
last_name
job_id
salary
}
}Example cURL command:
curl --location 'http://localhost:8080/ords/hr/_/graphql' \
--header 'Content-Type: application/json' \
--data '{
"query": "{ employees(where : {job_id : {eq :"IT_PROG"}}) { employee_id first_name last_name job_id salary } } "
}'Response:
{
"data": {
"employees": [
{
"employee_id": 103,
"first_name": "Alexander",
"last_name": "Hunold",
"job_id": "IT_PROG",
"salary": 9000
},
{
"employee_id": 104,
"first_name": "Bruce",
"last_name": "Ernst",
"job_id": "IT_PROG",
"salary": 6000
},
{
"employee_id": 105,
"first_name": "David",
"last_name": "Austin",
"job_id": "IT_PROG",
"salary": 4800
},
{
"employee_id": 106,
"first_name": "Valli",
"last_name": "Pataballa",
"job_id": "IT_PROG",
"salary": 4800
},
{
"employee_id": 107,
"first_name": "Diana",
"last_name": "Lorentz",
"job_id": "IT_PROG",
"salary": 4200
}
]
}
}Example: Greater than (>) Operator and Date Data Type
The following query includes a filter that restricts the hire_date field to be greater than 01 Jan 2006.
query {
employees(where : { hire_date : { gt : "2006-01-01T00:00:00Z" } } ){
employee_id
first_name
last_name
hire_date
}
}Example: LIKE (like) operator
The following query includes a filter that restrics the first_name field to match the pattern S%:
query {
employees(where : { first_name : { like : "S%" } }){
employee_id
first_name
last_name
}
}Example: IN (in) operator
The following query includes a filter that restricts the the job_id field to IT_PROG or FI_ACCOUNT using the in operator:
query {
employees(where : { job_id : { in : ["IT_PROG", "FI_ACCOUNT"] } } ){
employee_id
first_name
last_name
job_id
salary
}
}Example: NOT (not) Operator
The following query includes a filter that negates the result of restricting the salary field to be between 2000 and 10000.
query Employees {
employees(where : {not : {salary : {btwn : [2000, 10000]}}}){
employee_id
first_name
last_name
job_id
salary
}
}Request:
curl --location 'http://localhost:8080/ords/hr/_/graphql' \
--header 'Content-Type: application/json' \
--data '{
"query": "{employees(where : {not : {salary : {btwn : [2000, 10000]}}}){
employee_id first_name last_name job_id salary } } "
}'Response:
{
"data": {
"employees": [
{
"employee_id": 100,
"first_name": "Steven",
"last_name": "King",
"job_id": "AD_PRES",
"salary": 24000
},
{
"employee_id": 101,
"first_name": "Neena",
"last_name": "Kochhar",
"job_id": "AD_VP",
"salary": 17000
},
{
"employee_id": 102,
"first_name": "Lex",
"last_name": "De Haan",
"job_id": "AD_VP",
"salary": 17000
},
{
"employee_id": 108,
"first_name": "Nancy",
"last_name": "Greenberg",
"job_id": "FI_MGR",
"salary": 12008
},
{
"employee_id": 114,
"first_name": "Den",
"last_name": "Raphaely",
"job_id": "PU_MAN",
"salary": 11000
},
{
"employee_id": 145,
"first_name": "John",
"last_name": "Russell",
"job_id": "SA_MAN",
"salary": 14000
},
{
"employee_id": 146,
"first_name": "Karen",
"last_name": "Partners",
"job_id": "SA_MAN",
"salary": 13500
},
{
"employee_id": 147,
"first_name": "Alberto",
"last_name": "Errazuriz",
"job_id": "SA_MAN",
"salary": 12000
},
{
"employee_id": 148,
"first_name": "Gerald",
"last_name": "Cambrault",
"job_id": "SA_MAN",
"salary": 11000
},
{
"employee_id": 149,
"first_name": "Eleni",
"last_name": "Zlotkey",
"job_id": "SA_MAN",
"salary": 10500
},
{
"employee_id": 162,
"first_name": "Clara",
"last_name": "Vishney",
"job_id": "SA_REP",
"salary": 10500
},
{
"employee_id": 168,
"first_name": "Lisa",
"last_name": "Ozer",
"job_id": "SA_REP",
"salary": 11500
},
{
"employee_id": 174,
"first_name": "Ellen",
"last_name": "Abel",
"job_id": "SA_REP",
"salary": 11000
},
{
"employee_id": 201,
"first_name": "Michael",
"last_name": "Hartstein",
"job_id": "MK_MAN",
"salary": 13000
},
{
"employee_id": 205,
"first_name": "Shelley",
"last_name": "Higgins",
"job_id": "AC_MGR",
"salary": 12008
}
]
}
}Example: AND (and) operator
The following query includes a filter that restricts the the job_id field to IT_PROG and the salary field to be between 4000 and 6000:
query Employees {
employees(where : { and : [
{job_id : { eq : "IT_PROG" }},
{salary : { btwn : [4000, 6000] }}
]}){
employee_id
first_name
last_name
job_id
salary
}
}Request:
query Employees {
employees(where : { and : [
{job_id : { eq : "IT_PROG" }},
{salary : { btwn : [4000, 6000] }}
]}){
employee_id
first_name
last_namecurl --location 'http://localhost:8080/ords/hr/_/graphql' \
--header 'Content-Type: application/json' \
--data '{
"query": "{employees(where : { and : [ {job_id : { eq : "IT_PROG" }}, {salary : { btwn : [4000, 6000] }} ] }){
employee_id first_name last_name job_id salary } } "
}'
job_id
salary
}
}Response:
{
"data": {
"employees": [
{
"employee_id": 104,
"first_name": "Bruce",
"last_name": "Ernst",
"job_id": "IT_PROG",
"salary": 6000
},
{
"employee_id": 105,
"first_name": "David",
"last_name": "Austin",
"job_id": "IT_PROG",
"salary": 4800
},
{
"employee_id": 106,
"first_name": "Valli",
"last_name": "Pataballa",
"job_id": "IT_PROG",
"salary": 4800
},
{
"employee_id": 107,
"first_name": "Diana",
"last_name": "Lorentz",
"job_id": "IT_PROG",
"salary": 4200
}
]
}
}Example: OR (or) operator
The following query includes a filter that restricts the the job_id field to IT_PROG or FI_ACCOUNT using or operator:
query Employees {
employees(where : { or : [
{job_id : { eq : "IT_PROG" }},
{job_id : { eq : "FI_ACCOUNT" }}
]}){
employee_id
first_name
last_name
job_id
salary
}
}Example: Where Filter in Children Types
All the filters described in the preceding sections can be applied to nested types in a query, that enables you to widen the range of fields that can be filtered in a single query.
The following query retrieves all employees that are managers of employees whose job_id is equal to IT_PROG:
query{
employees{
employee_id
first_name
last_name
job_id
salary
employees_manager_id(where : {job_id : {eq : "IT_PROG"}}){
employee_id
first_name
last_name
job_id
salary
}
}
}Request:
curl --location 'http://localhost:8080/ords/hr/_/graphql' \
--header 'Content-Type: application/json' \
--data '{
"query": "query{ employees{ employee_id first_name last_name job_id salary employees_manager_id( where : { job_id :
{ eq : "IT_PROG" } } ){employee_id first_name last_name job_id salary} } }"
}'Response:
{
"data": {
"employees": [
{
"employee_id": 102,
"first_name": "Lex",
"last_name": "De Haan",
"job_id": "AD_VP",
"salary": 17000,
"employees_manager_id": [
{
"employee_id": 103,
"first_name": "Alexander",
"last_name": "Hunold",
"job_id": "IT_PROG",
"salary": 9000
}
]
},
{
"employee_id": 103,
"first_name": "Alexander",
"last_name": "Hunold",
"job_id": "IT_PROG",
"salary": 9000,
"employees_manager_id": [
{
"employee_id": 104,
"first_name": "Bruce",
"last_name": "Ernst",
"job_id": "IT_PROG",
"salary": 6000
},
{
"employee_id": 105,
"first_name": "David",
"last_name": "Austin",
"job_id": "IT_PROG",
"salary": 4800
},
{
"employee_id": 106,
"first_name": "Valli",
"last_name": "Pataballa",
"job_id": "IT_PROG",
"salary": 4800
},
{
"employee_id": 107,
"first_name": "Diana",
"last_name": "Lorentz",
"job_id": "IT_PROG",
"salary": 4200
}
]
}
]
}
}Working with Dates/Timestamps Using Filters
Most of the filters described in the previous sections, can be applied on fields whose type is Date or Timestamp. To apply these filters on fields whose type is Date, you must use the format: YYYY-MM-DDThh:mm:ssZ. For the date fields, YYYY-MM-DD format can also be used. To apply these filters on fields whose type is Timestamp, you must use the format: YYYY-MM-DDThh:mm:ss.sssZ. The following query includes a filter that restricts the hire_date field to be inbetween the range 01 Jan 2006 and 01 Jun 2006:
query{
employees(where : {hire_date : {btwn : ["2006-01-01", "2006-06-01"]}}){
employee_id
first_name
last_name
job_id
salary
hire_date
}
}Request:
curl --location 'http://localhost:8080/ords/hr/_/graphql' --header 'Content-Type: application/json' --data '{ "query": "query{ employees(where : {hire_date : {btwn : ["2006-01-01",
"2006-06-01"]}}){employee_id first_name last_name job_id salary hire_date}
}"}'Response:
{
"data": {
"employees": [
{
"employee_id": 103,
"first_name": "Alexander",
"last_name": "Hunold",
"job_id": "IT_PROG",
"salary": 9000,
"hire_date": "2006-01-03T00:00:00Z"
},
{
"employee_id": 106,
"first_name": "Valli",
"last_name": "Pataballa",
"job_id": "IT_PROG",
"salary": 4800,
"hire_date": "2006-02-05T00:00:00Z"
},
{
"employee_id": 112,
"first_name": "Jose Manuel",
"last_name": "Urman",
"job_id": "FI_ACCOUNT",
"salary": 7800,
"hire_date": "2006-03-07T00:00:00Z"
},
{
"employee_id": 139,
"first_name": "John",
"last_name": "Seo",
"job_id": "ST_CLERK",
"salary": 2700,
"hire_date": "2006-02-12T00:00:00Z"
},
{
"employee_id": 140,
"first_name": "Joshua",
"last_name": "Patel",
"job_id": "ST_CLERK",
"salary": 2500,
"hire_date": "2006-04-06T00:00:00Z"
},
{
"employee_id": 143,
"first_name": "Randall",
"last_name": "Matos",
"job_id": "ST_CLERK",
"salary": 2600,
"hire_date": "2006-03-15T00:00:00Z"
},
{
"employee_id": 153,
"first_name": "Christopher",
"last_name": "Olsen",
"job_id": "SA_REP",
"salary": 8000,
"hire_date": "2006-03-30T00:00:00Z"
},
{
"employee_id": 169,
"first_name": "Harrison",
"last_name": "Bloom",
"job_id": "SA_REP",
"salary": 10000,
"hire_date": "2006-03-23T00:00:00Z"
},
{
"employee_id": 170,
"first_name": "Tayler",
"last_name": "Fox",
"job_id": "SA_REP",
"salary": 9600,
"hire_date": "2006-01-24T00:00:00Z"
},
{
"employee_id": 176,
"first_name": "Jonathon",
"last_name": "Taylor",
"job_id": "SA_REP",
"salary": 8600,
"hire_date": "2006-03-24T00:00:00Z"
},
{
"employee_id": 177,
"first_name": "Jack",
"last_name": "Livingston",
"job_id": "SA_REP",
"salary": 8400,
"hire_date": "2006-04-23T00:00:00Z"
},
{
"employee_id": 180,
"first_name": "Winston",
"last_name": "Taylor",
"job_id": "SH_CLERK",
"salary": 3200,
"hire_date": "2006-01-24T00:00:00Z"
},
{
"employee_id": 181,
"first_name": "Jean",
"last_name": "Fleaur",
"job_id": "SH_CLERK",
"salary": 3100,
"hire_date": "2006-02-23T00:00:00Z"
},
{
"employee_id": 196,
"first_name": "Alana",
"last_name": "Walsh",
"job_id": "SH_CLERK",
"salary": 3100,
"hire_date": "2006-04-24T00:00:00Z"
},
{
"employee_id": 197,
"first_name": "Kevin",
"last_name": "Feeney",
"job_id": "SH_CLERK",
"salary": 3000,
"hire_date": "2006-05-23T00:00:00Z"
}
]
}
}Accessing REST-Enabled Objects that Contain Special Characters
GraphQL does not support special characters in primary key(s) of the queried object.
GraphQL only permits object names (such as types and fields) to include characters from the set [_a-zA-Z0-9]. As a result, if an object or field name contains characters such as . , $ @ or #, then these must be replaced with underscores _ to form a valid GraphQL name. When executing queries, GraphQL can still reference the original names to retrieve the correct data, so no information is lost when mapping these types or fields.
Examples
Example 16-1 Use of REST-enabled Object name with Special Character
Following is an autoREST-enabled table:
CREATE TABLE special$chars_table AS
SELECT
employee_id AS id,
first_name AS name
FROM
employees;You can use the table in a graphql query:
query {
special_chars_table{
id
name
}
}Note: The table special$chars_table is represented as the special_chars_table type.
Example 16-2 cURL Command
curl --location 'http://localhost:8080/ords/hr/_/graphql' \
--header 'Content-Type: application/json' \
--data '{
"query": "query Special_Chars{ special_chars_table{ id name } }"
}'Response
{
"data": {
"special_chars_table": [
{
"name": "Ellen",
"id": 174
},
{
"name": "Sundar",
"id": 166
},
{
"name": "Mozhe",
"id": 130
},
{
"name": "Shelli",
"id": 116
},
{
"name": "Amit",
"id": 167
},
{
"name": "Elizabeth",
"id": 172
},
{
"name": "Sarah",
"id": 192
},
{
"name": "David",
"id": 151
},
{
"name": "Laura",
"id": 129
},
{
"name": "Harrison",
"id": 169
},
{
"name": "Hermann",
"id": 204
},
{
"name": "Alexis",
"id": 185
},
{
"name": "Anthony",
"id": 187
},
{
"name": "Gerald",
"id": 148
},
{
"name": "Nanette",
"id": 154
},
{
"name": "John",
"id": 110
},
{
"name": "Kelly",
"id": 188
},
{
"name": "Karen",
"id": 119
},
{
"name": "Curtis",
"id": 142
},
{
"name": "Pat",
"id": 202
},
{
"name": "Julia",
"id": 186
},
{
"name": "Jennifer",
"id": 189
},
{
"name": "Louise",
"id": 160
},
{
"name": "Alberto",
"id": 147
},
{
"name": "Britney",
"id": 193
},
{
"name": "Daniel",
"id": 109
},
{
"name": "Kevin",
"id": 197
},
{
"name": "Jean",
"id": 181
},
{
"name": "Tayler",
"id": 170
},
{
"name": "Adam",
"id": 121
},
{
"name": "Lex",
"id": 102
},
{
"name": "Ki",
"id": 135
},
{
"name": "Girard",
"id": 183
},
{
"name": "William",
"id": 206
},
{
"name": "Douglas",
"id": 199
},
{
"name": "Kimberely",
"id": 178
},
{
"name": "Danielle",
"id": 163
},
{
"name": "Nancy",
"id": 108
},
{
"name": "Peter",
"id": 152
},
{
"name": "Shelley",
"id": 205
},
{
"name": "Guy",
"id": 118
},
{
"name": "Alyssa",
"id": 175
},
{
"name": "Valli",
"id": 106
},
{
"name": "Susan",
"id": 203
},
{
"name": "Alexander",
"id": 103
},
{
"name": "Charles",
"id": 179
},
{
"name": "Vance",
"id": 195
},
{
"name": "Payam",
"id": 122
},
{
"name": "Alexander",
"id": 115
},
{
"name": "Janette",
"id": 156
},
{
"name": "Steven",
"id": 100
},
{
"name": "Sundita",
"id": 173
},
{
"name": "Renske",
"id": 137
},
{
"name": "James",
"id": 127
},
{
"name": "David",
"id": 165
},
{
"name": "Den",
"id": 114
},
{
"name": "Jack",
"id": 177
},
{
"name": "Jason",
"id": 133
},
{
"name": "Steven",
"id": 128
},
{
"name": "James",
"id": 131
},
{
"name": "Michael",
"id": 201
},
{
"name": "Mattea",
"id": 164
},
{
"name": "Randall",
"id": 143
},
{
"name": "Allan",
"id": 158
},
{
"name": "Samuel",
"id": 194
},
{
"name": "Irene",
"id": 126
},
{
"name": "Bruce",
"id": 104
},
{
"name": "Kevin",
"id": 124
},
{
"name": "Julia",
"id": 125
},
{
"name": "Diana",
"id": 107
},
{
"name": "Donald",
"id": 198
},
{
"name": "Christopher",
"id": 153
},
{
"name": "TJ",
"id": 132
},
{
"name": "Lisa",
"id": 168
},
{
"name": "Karen",
"id": 146
},
{
"name": "Joshua",
"id": 140
},
{
"name": "Randall",
"id": 191
},
{
"name": "Hazel",
"id": 136
},
{
"name": "Luis",
"id": 113
},
{
"name": "Trenna",
"id": 141
},
{
"name": "Michael",
"id": 134
},
{
"name": "Nandita",
"id": 184
},
{
"name": "Ismael",
"id": 111
},
{
"name": "John",
"id": 139
},
{
"name": "Sarath",
"id": 161
},
{
"name": "John",
"id": 145
},
{
"name": "Lindsey",
"id": 159
},
{
"name": "William",
"id": 171
},
{
"name": "Stephen",
"id": 138
},
{
"name": "Martha",
"id": 182
},
{
"name": "Patrick",
"id": 157
},
{
"name": "Jonathon",
"id": 176
},
{
"name": "Winston",
"id": 180
},
{
"name": "Sigal",
"id": 117
},
{
"name": "Sean",
"id": 150
},
{
"name": "Oliver",
"id": 155
},
{
"name": "Jose Manuel",
"id": 112
},
{
"name": "Peter",
"id": 144
},
{
"name": "Timothy",
"id": 190
},
{
"name": "Clara",
"id": 162
},
{
"name": "Shanta",
"id": 123
},
{
"name": "Alana",
"id": 196
},
{
"name": "Matthew",
"id": 120
},
{
"name": "Jennifer",
"id": 200
},
{
"name": "David",
"id": 105
},
{
"name": "Neena",
"id": 101
},
{
"name": "Eleni",
"id": 149
}
]
}
}Example 16-3 Use of REST-Enabled Object whose columns contain a column with a special character in their name
The following is an autoREST-enabled table:
CREATE TABLE emp AS
SELECT
employee_id AS id,
first_name AS name,
email AS "email@"
FROM
employees;You can use the table in a graphql query:
query {
emp{
id
name
email_
}
}Note: email@ column of the emp table is represented as the email_ field of the emp type.
cURL Command
curl --location 'http://localhost:8080/ords/hr/_/graphql' \
--header 'Content-Type: application/json' \
--data '{
"query": "query Special_Chars{ emp{ id name email_ } }"
}'Response
{
"data": {
"emp": [
{
"name": "Douglas",
"id": 199,
"email_": "DGRANT"
},
{
"name": "Jennifer",
"id": 200,
"email_": "JWHALEN"
},
{
"name": "Michael",
"id": 201,
"email_": "MMARTINE"
},
{
"name": "Pat",
"id": 202,
"email_": "PDAVIS"
},
{
"name": "Susan",
"id": 203,
"email_": "SJACOBS"
},
{
"name": "Hermann",
"id": 204,
"email_": "HBROWN"
},
{
"name": "Shelley",
"id": 205,
"email_": "SHIGGINS"
},
{
"name": "William",
"id": 206,
"email_": "WGIETZ"
},
{
"name": "Steven",
"id": 100,
"email_": "SKING"
},
{
"name": "Neena",
"id": 101,
"email_": "NYANG"
},
{
"name": "Lex",
"id": 102,
"email_": "LGARCIA"
},
{
"name": "Alexander",
"id": 103,
"email_": "AJAMES"
},
{
"name": "Bruce",
"id": 104,
"email_": "BMILLER"
},
{
"name": "David",
"id": 105,
"email_": "DWILLIAMS"
},
{
"name": "Valli",
"id": 106,
"email_": "VJACKSON"
},
{
"name": "Diana",
"id": 107,
"email_": "DNGUYEN"
},
{
"name": "Nancy",
"id": 108,
"email_": "NGRUENBE"
},
{
"name": "Daniel",
"id": 109,
"email_": "DFAVIET"
},
{
"name": "John",
"id": 110,
"email_": "JCHEN"
},
{
"name": "Ismael",
"id": 111,
"email_": "ISCIARRA"
},
{
"name": "Jose Manuel",
"id": 112,
"email_": "JMURMAN"
},
{
"name": "Luis",
"id": 113,
"email_": "LPOPP"
},
{
"name": "Den",
"id": 114,
"email_": "DLI"
},
{
"name": "Alexander",
"id": 115,
"email_": "AKHOO"
},
{
"name": "Shelli",
"id": 116,
"email_": "SBAIDA"
},
{
"name": "Sigal",
"id": 117,
"email_": "STOBIAS"
},
{
"name": "Guy",
"id": 118,
"email_": "GHIMURO"
},
{
"name": "Karen",
"id": 119,
"email_": "KCOLMENA"
},
{
"name": "Matthew",
"id": 120,
"email_": "MWEISS"
},
{
"name": "Adam",
"id": 121,
"email_": "AFRIPP"
},
{
"name": "Payam",
"id": 122,
"email_": "PKAUFLIN"
},
{
"name": "Shanta",
"id": 123,
"email_": "SVOLLMAN"
},
{
"name": "Kevin",
"id": 124,
"email_": "KMOURGOS"
},
{
"name": "Julia",
"id": 125,
"email_": "JNAYER"
},
{
"name": "Irene",
"id": 126,
"email_": "IMIKKILI"
},
{
"name": "James",
"id": 127,
"email_": "JLANDRY"
},
{
"name": "Steven",
"id": 128,
"email_": "SMARKLE"
},
{
"name": "Laura",
"id": 129,
"email_": "LBISSOT"
},
{
"name": "Mozhe",
"id": 130,
"email_": "MATKINSO"
},
{
"name": "James",
"id": 131,
"email_": "JAMRLOW"
},
{
"name": "TJ",
"id": 132,
"email_": "TJOLSON"
},
{
"name": "Jason",
"id": 133,
"email_": "JMALLIN"
},
{
"name": "Michael",
"id": 134,
"email_": "MROGERS"
},
{
"name": "Ki",
"id": 135,
"email_": "KGEE"
},
{
"name": "Hazel",
"id": 136,
"email_": "HPHILTAN"
},
{
"name": "Renske",
"id": 137,
"email_": "RLADWIG"
},
{
"name": "Stephen",
"id": 138,
"email_": "SSTILES"
},
{
"name": "John",
"id": 139,
"email_": "JSEO"
},
{
"name": "Joshua",
"id": 140,
"email_": "JPATEL"
},
{
"name": "Trenna",
"id": 141,
"email_": "TRAJS"
},
{
"name": "Curtis",
"id": 142,
"email_": "CDAVIES"
},
{
"name": "Randall",
"id": 143,
"email_": "RMATOS"
},
{
"name": "Peter",
"id": 144,
"email_": "PVARGAS"
},
{
"name": "John",
"id": 145,
"email_": "JSINGH"
},
{
"name": "Karen",
"id": 146,
"email_": "KPARTNER"
},
{
"name": "Alberto",
"id": 147,
"email_": "AERRAZUR"
},
{
"name": "Gerald",
"id": 148,
"email_": "GCAMBRAU"
},
{
"name": "Eleni",
"id": 149,
"email_": "EZLOTKEY"
},
{
"name": "Sean",
"id": 150,
"email_": "STUCKER"
},
{
"name": "David",
"id": 151,
"email_": "DBERNSTE"
},
{
"name": "Peter",
"id": 152,
"email_": "PHALL"
},
{
"name": "Christopher",
"id": 153,
"email_": "COLSEN"
},
{
"name": "Nanette",
"id": 154,
"email_": "NCAMBRAU"
},
{
"name": "Oliver",
"id": 155,
"email_": "OTUVAULT"
},
{
"name": "Janette",
"id": 156,
"email_": "JKING"
},
{
"name": "Patrick",
"id": 157,
"email_": "PSULLY"
},
{
"name": "Allan",
"id": 158,
"email_": "AMCEWEN"
},
{
"name": "Lindsey",
"id": 159,
"email_": "LSMITH"
},
{
"name": "Louise",
"id": 160,
"email_": "LDORAN"
},
{
"name": "Sarath",
"id": 161,
"email_": "SSEWALL"
},
{
"name": "Clara",
"id": 162,
"email_": "CVISHNEY"
},
{
"name": "Danielle",
"id": 163,
"email_": "DGREENE"
},
{
"name": "Mattea",
"id": 164,
"email_": "MMARVINS"
},
{
"name": "David",
"id": 165,
"email_": "DLEE"
},
{
"name": "Sundar",
"id": 166,
"email_": "SANDE"
},
{
"name": "Amit",
"id": 167,
"email_": "ABANDA"
},
{
"name": "Lisa",
"id": 168,
"email_": "LOZER"
},
{
"name": "Harrison",
"id": 169,
"email_": "HBLOOM"
},
{
"name": "Tayler",
"id": 170,
"email_": "TFOX"
},
{
"name": "William",
"id": 171,
"email_": "WSMITH"
},
{
"name": "Elizabeth",
"id": 172,
"email_": "EBATES"
},
{
"name": "Sundita",
"id": 173,
"email_": "SKUMAR"
},
{
"name": "Ellen",
"id": 174,
"email_": "EABEL"
},
{
"name": "Alyssa",
"id": 175,
"email_": "AHUTTON"
},
{
"name": "Jonathon",
"id": 176,
"email_": "JTAYLOR"
},
{
"name": "Jack",
"id": 177,
"email_": "JLIVINGS"
},
{
"name": "Kimberely",
"id": 178,
"email_": "KGRANT"
},
{
"name": "Charles",
"id": 179,
"email_": "CJOHNSON"
},
{
"name": "Winston",
"id": 180,
"email_": "WTAYLOR"
},
{
"name": "Jean",
"id": 181,
"email_": "JFLEAUR"
},
{
"name": "Martha",
"id": 182,
"email_": "MSULLIVA"
},
{
"name": "Girard",
"id": 183,
"email_": "GGEONI"
},
{
"name": "Nandita",
"id": 184,
"email_": "NSARCHAN"
},
{
"name": "Alexis",
"id": 185,
"email_": "ABULL"
},
{
"name": "Julia",
"id": 186,
"email_": "JDELLING"
},
{
"name": "Anthony",
"id": 187,
"email_": "ACABRIO"
},
{
"name": "Kelly",
"id": 188,
"email_": "KCHUNG"
},
{
"name": "Jennifer",
"id": 189,
"email_": "JDILLY"
},
{
"name": "Timothy",
"id": 190,
"email_": "TVENZL"
},
{
"name": "Randall",
"id": 191,
"email_": "RPERKINS"
},
{
"name": "Sarah",
"id": 192,
"email_": "SBELL"
},
{
"name": "Britney",
"id": 193,
"email_": "BEVERETT"
},
{
"name": "Samuel",
"id": 194,
"email_": "SMCLEOD"
},
{
"name": "Vance",
"id": 195,
"email_": "VJONES"
},
{
"name": "Alana",
"id": 196,
"email_": "AWALSH"
},
{
"name": "Kevin",
"id": 197,
"email_": "KFEENEY"
},
{
"name": "Donald",
"id": 198,
"email_": "DOCONNEL"
}
]
}
}Example 16-4 Use of REST-enabled object whose columns contains a column with a special character in their name in GraphQL filter expression
Following is an autoREST-enabled table:
CREATE TABLE emp AS
SELECT
employee_id AS id,
first_name AS name,
email AS "email@"
FROM
employees;You can use this table in a graphql query:
query {
emp(where : {email : {like : "S%"}}){
id
name
email_
}
}Note: email@ column of the emp table is represented as the email_ field of the emp type.
Example cURL Command
curl --location 'http://localhost:8080/ords/hr/_/graphql' \
--header 'Content-Type: application/json' \
--data '{
"query": "query Special_Chars{ emp(where : {email_ : {like : "S%"}}){ id name email_ } }"
}'Response
{
"data": {
"emp": [
{
"id": 203,
"name": "Susan",
"email_": "SJACOBS"
},
{
"id": 205,
"name": "Shelley",
"email_": "SHIGGINS"
},
{
"id": 100,
"name": "Steven",
"email_": "SKING"
},
{
"id": 116,
"name": "Shelli",
"email_": "SBAIDA"
},
{
"id": 117,
"name": "Sigal",
"email_": "STOBIAS"
},
{
"id": 123,
"name": "Shanta",
"email_": "SVOLLMAN"
},
{
"id": 128,
"name": "Steven",
"email_": "SMARKLE"
},
{
"id": 138,
"name": "Stephen",
"email_": "SSTILES"
},
{
"id": 150,
"name": "Sean",
"email_": "STUCKER"
},
{
"id": 161,
"name": "Sarath",
"email_": "SSEWALL"
},
{
"id": 166,
"name": "Sundar",
"email_": "SANDE"
},
{
"id": 173,
"name": "Sundita",
"email_": "SKUMAR"
},
{
"id": 192,
"name": "Sarah",
"email_": "SBELL"
},
{
"id": 194,
"name": "Samuel",
"email_": "SMCLEOD"
}
]
}
}Sorting the Data
Sorting enables you to sort the data in a ascending or descending order by one or more fields.
Sort Query Syntax:
sortValue = "asc" | "desc" | "ASC" | "DESC"
sortExp = [{<fieldName1> : sortValue}, ... ,{<fieldNameN> : sortValue} ]
sort : <sortExp>The following query specifies sort filter to order the employee_id field in a descending order:
query {
employees(sort : [ { employee_id : "desc" } ] ){
employee_id
first_name
last_name
salary
}
}Request:
curl --location 'http://localhost:8080/ords/hr/_/graphql' \
--header 'Content-Type: application/json' \
--data '{
"query": "query { employees(sort : [ { employee_id : "desc" } ] )
{ employee_id first_name last_name salary } }"
}'Response:
{
"data": {
"employees": [
{
"employee_id": 206,
"first_name": "William",
"last_name": "Gietz",
"salary": 8300
},
{
"employee_id": 205,
"first_name": "Shelley",
"last_name": "Higgins",
"salary": 12008
},
{
"employee_id": 204,
"first_name": "Hermann",
"last_name": "Baer",
"salary": 10000
},
{
"employee_id": 203,
"first_name": "Susan",
"last_name": "Mavris",
"salary": 6500
},
{
"employee_id": 202,
"first_name": "Pat",
"last_name": "Fay",
"salary": 6000
},
{
"employee_id": 201,
"first_name": "Michael",
"last_name": "Hartstein",
"salary": 13000
},
{
"employee_id": 200,
"first_name": "Jennifer",
"last_name": "Whalen",
"salary": 4400
},
...
]
}
}Example: Sorting by Multiple Columns
The following query includes a sort filter that orders the data in a descending order by department_id field and in an ascending order by salary field:
query {
employees(sort : [ { department_id : "desc" } , { salary : "asc" }] ){
employee_id
first_name
last_name
salary
department_id
}
}Keyset Pagination
Keyset pagination enables you to specify a limit and offset to paginate the data received from any given query. If sorting expression is not specified, then ROWID is used by default as a sort argument to uniquely address the rows.
The following query specifies the offset and limit parameters:
query Employees {
employees(limit: 3, offset: 5) {
employee_id
first_name
last_name
email
}
}Request:
curl --location 'http://localhost:8080/ords/hr/_/graphql' \
--header 'Content-Type: application/json' \
--data '{
"query": "query { employees( limit: 3, offset: 5 ){ employee_id first_name last_name email } }"
}'Response:
{
"data": {
"employees": [
{
"employee_id": 105,
"first_name": "David",
"last_name": "Austin",
"email": "DAUSTIN"
},
{
"employee_id": 106,
"first_name": "Valli",
"last_name": "Pataballa",
"email": "VPATABAL"
},
{
"employee_id": 107,
"first_name": "Diana",
"last_name": "Lorentz",
"email": "DLORENTZ"
}
]
}
}Example: Pagination with Other Filters
The following query specifies the offset and limit parameters and orders the results in a descending order by employee_id field:
query {
employees(sort : [ { employee_id : "DESC" } ], limit: 3, offset: 2){
employee_id
first_name
last_name
salary
department_id
}
}Example: Pagination in Nested Types
The following query specifies the limit parameter both in employees and in the nested type employees_manager_id and limits the number of employees returned in the nested object to two:
query{
employees(limit : 1){
employee_id
first_name
last_name
job_id
salary
employees_manager_id(limit : 2){
employee_id
first_name
}
}
}Request:
curl --location 'http://localhost:8080/ords/hr/_/graphql' \
--header 'Content-Type: application/json' \
--data '{
"query": "query {employees(limit : 1){employee_id first_name last_name job_id salary employees_manager_id(limit : 2){employee_id first_name}}}"
}'Response:
{
"data": {
"employees": [
{
"employee_id": 100,
"first_name": "Steven",
"last_name": "King",
"job_id": "AD_PRES",
"salary": 24000,
"employees_manager_id": [
{
"employee_id": 101,
"first_name": "Neena"
},
{
"employee_id": 102,
"first_name": "Lex"
}
]
}
]
}
}Using Dynamic Arguments in Queries: Variables
To replace variables with static values in GraphQL queries, perform the following steps: 1. Replace the static value with $variableName
-
Declare
$variableNameas one of the variables accepted by the query and then specify the data type -
Pass variables dictionary separately
The following query uses variables to use dynamic values in the filters:
query Employees($job_id : String, $min_salary : Int, $max_salary : Int){
employees (where : { and : [
{job_id : { eq : $job_id }},
{salary : { btwn : [$min_salary, $max_salary] }} ]}){
employee_id
manager_id
phone_number
commission_pct
department_id
salary
first_name
email
job_id
hire_date
last_name
}
}Variables Dictionary:
{
"job_id" : "IT_PROG",
"min_salary" : 4000,
"max_salary" : 6000
}Request:
curl --location 'http://localhost:8080/ords/hr/_/graphql' \
--header 'Content-Type: application/json' \
--data '{
"query": "query Employees($job_id : String, $min_salary : Int, $max_salary : Int){ employees (where : { and : [\n {job_id : { eq : $job_id }}, {salary : { btwn : [$min_salary, $max_salary] }} ]}){ employee_id manager_id phone_number commission_pct department_id salary first_name email job_id hire_date last_name }}",
"operationName": "Employees",
"variables": {
"job_id": "IT_PROG",
"min_salary": 4000,
"max_salary": 6000
}
}'GraphiQL
Oracle REST Data Services includes GraphiQL, an in-browser IDE for exploring GraphQL. Use the following endpoint and login with the Rest-enabled user database schema credentials:
http://<HOST>:<PORT>/ords/<SCHEMANAME>/_/graphiql