GraphQL in Oracle REST Data Services

14.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.

Parent topic: GraphQL in Oracle REST Data Services

14.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.

14.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>/_/graphql

Note:

This feature is available only for Oracle REST Data Services enabled schemas.

Accessing Protected REST-Enabled Objects

Parent topic: GraphQL in Oracle REST Data Services

14.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.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.

14.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:

Getting GraphQL Schema
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.

Parent topic: GraphQL in Oracle REST Data Services

14.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}"}

Parent topic: Accessing Objects Using GraphQL queries

14.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
      },
    ...
}

Parent topic: Accessing Objects Using GraphQL queries

14.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.

14.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
          }
        ]
      }
  }
}

Parent topic: Join Query

14.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.

Supported Data Types
This section lists the supported data types for filters.
Filtering by Primary Key
Where Filter
Accessing REST-Enabled Objects that Contain Special Characters
GraphQL does not support special characters in primary key(s) of the queried object.

Parent topic: GraphQL in Oracle REST Data Services

14.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`.

Parent topic: Examples of Filtering in Queries

14.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"
      }
    ]
  }
}

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

Parent topic: Examples of Filtering in Queries

14.5.2.1 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
  }
}

Parent topic: Filtering by Primary Key

14.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 14-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`	`{ or : [ { GraphQL expression 1 } ..., { GraphQL expression n } ] }`	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	`{ and : [ { GraphQL expression 1 }, ...., { GraphQL expression n } ] }`	Logical operator, returns `true` if both expressions are true.	Not Applicable

Parent topic: Examples of Filtering in Queries

14.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
            }
        ]
    }
}

Parent topic: Where Filter

14.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
  }
}

Parent topic: Where Filter

14.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
  }
}

Parent topic: Where Filter

14.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
  }
}

Parent topic: Where Filter

14.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
      }
    ]
  }
}

Parent topic: Where Filter

14.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
            }
        ]
    }
}

Parent topic: Where Filter

14.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
  }
}

Parent topic: Where Filter

14.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
          }
        ]
      }
    ]
  }
}

Parent topic: Where Filter

14.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"
      }
    ]
  }
}

Parent topic: Where Filter

14.5.4 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

Parent topic: Examples of Filtering in Queries

14.5.4.1 Examples

Example 14-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 14-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 14-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 14-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"
      }
    ]
  }
}

Parent topic: Accessing REST-Enabled Objects that Contain Special Characters

14.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
      },
     ...
    ]
  }
}

Example: Sorting by Multiple Columns

Parent topic: GraphQL in Oracle REST Data Services

14.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
  }
}

Parent topic: Sorting the Data

14.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"
            }
        ]
    }
}

Parent topic: GraphQL in Oracle REST Data Services

14.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
  }
}

Parent topic: Keyset Pagination

14.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"
          }
        ]
      }
    ]
  }
}

Parent topic: Keyset Pagination

14.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 $variableName as 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
    }
}'

Parent topic: GraphQL in Oracle REST Data Services

14.9 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

Parent topic: GraphQL in Oracle REST Data Services

14 GraphQL in Oracle REST Data Services

14.1 GraphQL Terminology

14.2 Enabling GraphQL in Oracle REST Data Services

14.3 Enabling Objects for GraphQL

14.3.1 Accessing Protected REST-Enabled Objects

14.4 Accessing Objects Using GraphQL queries

14.4.1 Getting GraphQL Schema

14.4.2 Simple Query

14.4.3 Join Query

14.4.3.1 Circular Relationships Between Objects

14.5 Examples of Filtering in Queries

14.5.1 Supported Data Types

14.5.2 Filtering by Primary Key

14.5.2.1 Filtering by Composite Primary Key

14.5.3 Where Filter

14.5.3.1 Example: EQUALS (eq) operator

14.5.3.2 Example: Greater than (>) Operator and Date Data Type

14.5.3.3 Example: LIKE (like) operator

14.5.3.4 Example: IN (in) operator

14.5.3.5 Example: NOT (not) Operator

14.5.3.6 Example: AND (and) operator

14.5.3.7 Example: OR (or) operator

14.5.3.8 Example: Where Filter in Children Types

14.5.3.9 Working with Dates/Timestamps Using Filters

14.5.4 Accessing REST-Enabled Objects that Contain Special Characters

14.5.4.1 Examples

14.6 Sorting the Data

14.6.1 Example: Sorting by Multiple Columns

14.7 Keyset Pagination

14.7.1 Example: Pagination with Other Filters

14.7.2 Example: Pagination in Nested Types

14.8 Using Dynamic Arguments in Queries: Variables

14.9 GraphiQL