10 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.
Topics:
10.1 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.
10.2 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 Requirements10.3 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.10.3.1 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.schemaoracle.dbtools.role.autorest.<SCHEMANAME>.<OBJECTNAME>oracle.dbtools.autorest.privilege.<SCHEMANAME>.<OBJECTNAME>
10.4 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:
10.4.1 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}"}10.4.2 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
},
...
}10.4.3 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 Settings10.4.3.1 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
}
]
}
}
}10.5 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.
10.5.1 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.
|
10.5.2 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"
}
]
}
}10.5.3 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 10-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 |
10.5.3.1 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
}
]
}
}10.5.3.2 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
}
}10.5.3.3 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
}
}10.5.3.4 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
}
}10.5.3.5 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
}
]
}
}10.5.3.6 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
}
]
}
}10.5.3.7 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
}
}10.5.3.8 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
}
]
}
]
}
}10.5.3.9 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"
}
]
}
}10.6 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
},
...
]
}
}10.6.1 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
}
}10.7 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"
}
]
}
}10.7.1 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
}
}10.7.2 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"
}
]
}
]
}
}10.8 Using Dynamic Arguments in Queries: Variables
To replace variables with static values in GraphQL queries, perform the following
steps:
- 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
}
}'