REST API Examples: Relational Tables

This topic provides a tutorial on accessing and querying relational data in REST.

The REST path /api/servers/{alias}/databases/{dbname}/tables provides you a relational view of your data in a particular database. This contrasts with the /api/servers/{alias}/databases/{dbname}/collections path which provides REST APIs specific to JSON/BSON collections and with the /api/servers/{alias}/databases/{dbname}/timeseries path which is TimeSeries specific. Even so, the relational /api/servers/{alias}/databases/{dbname}/tables path can be used for tables of any type (relational, collection, or TimeSeries); although in the case of JSON collections, the data is formatted slightly differently depending on whether you are querying under the "tables" path or the "collections" path.

The examples in this topic will focus on running the REST APIs against traditional relational tables. See REST API Examples: JSON Collections, REST API Examples: TimeSeries Tables and Calendars, and REST API Examples: TimeSeries Queries for examples specific to the other table and data types.

Example 1: List tables in a database

To list all of the tables in a database:

Request
GET http://localhost:8080/api/servers/server1/databases/mydb/tables
Response
[
    "call_type",
    "catalog",
    "cust_calls",
    "customer",
    ...
]

Example 2: Create a table

To create a new table, POST a JSON document describing the table to be created. This document must include a name, a set of columns, and optionally the dbspace to create the table in.

The HTTP response is a JSON document describing the new table.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb/tables
Request Body
{
  "name": "classes",
  "columns": [
    {
      "name": "classid",
      "type": "int",
      "primaryKey": true
    },
    {
      "name": "level",
      "type": "int"
    },
    {
      "name": "subject",
      "type": "varchar(255)"
    },
    {
      "name": "department",
      "type": "varchar(100)"
    }
  ],
  "dbspace": "dbs1"
}
Response
{
    "tabid": 102,
    "tabname": "classes",
    "owner": "onedbsa",
    "created": {
        "$date": "2021-03-06T06:00:00Z"
    },
    "type": "relational",
    "columns": [
        {
            "name": "classid",
            "type": "integer",
            "size": 10,
            "nullable": false
        },
        {
            "name": "level",
            "type": "integer",
            "size": 10,
            "nullable": true
        },
        {
            "name": "subject",
            "type": "varchar",
            "size": 255,
            "nullable": true
        },
        {
            "name": "department",
            "type": "varchar",
            "size": 100,
            "nullable": true
        }
    ],
    "primaryKeyColumns": [
        "classid"
    ]
}

Example 3: Get table information

To get information about a table, including the names and types of its columns:

Request
GET http://localhost:8080/api/servers/server1/databases/mydb/tables/classes
Response
{
    "tabid": 102,
    "tabname": "classes",
    "owner": "onedbsa",
    "created": {
        "$date": "2021-03-06T06:00:00Z"
    },
    "type": "relational",
    "columns": [
        {
            "name": "classid",
            "type": "integer",
            "size": 10,
            "nullable": false
        },
        {
            "name": "level",
            "type": "integer",
            "size": 10,
            "nullable": true
        },
        {
            "name": "subject",
            "type": "varchar",
            "size": 255,
            "nullable": true
        },
        {
            "name": "department",
            "type": "varchar",
            "size": 100,
            "nullable": true
        }
    ],
    "primaryKeyColumns": [
        "classid"
    ]
}

Example 4: Insert a row into a table

To insert data into a table, use the insert API to POST a JSON document that represents the row to insert. The key names in the JSON document must map to the column names in the table, with the value for each key being the value to insert into that column.

The response includes the number of rows inserted and the response time in milliseconds.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb/tables/classes/insert
Request Body
{
  "classid": 1,
  "level": "101",
  "subject": "French 1",
  "department": "Languages"
}
Response
{
    "n": 1,
    "responseTime": 35
}

Example 5: Load multiple rows into a table

To load (insert) multiple rows into a table in a single request, use the load API. POST a JSON document with a data array of documents representing each new row to insert.

The response includes the number of rows inserted and the response time in milliseconds.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb/tables/classes/load
Request Body
{
  "data": [
    {
      "classid": 2,
      "level": "101",
      "subject": "Algebra 1",
      "department": "Mathematics"
    },
    {
      "classid": 3,
      "level": "102",
      "subject": "Algebra 2",
      "department": "Mathematics"
    },
    {
      "classid": 4,
      "level": "110",
      "subject": "Geometry",
      "department": "Mathematics"
    },
    {
      "classid": 5,
      "level": "111",
      "subject": "Trigonometry",
      "department": "Mathematics"
    },
    {
      "classid": 6,
      "level": "101",
      "subject": "Chemistry",
      "department": "Science"
    },
   {
      "classid": 7,
      "level": "105",
      "subject": "Biology",
      "department": "Science"
    },
    {
      "classid": 8,
      "level": "110",
      "subject": "Physics",
      "department": "Science"
    }
  ]
}
Response
{
    "n": 7,
    "responseTime": 32
}

Example 6: Query for all rows in a table

To query a table, POST a JSON document to the query API. The query document can contain a filter, fields, limit, skip, and orderBy fields.

If no filter is provided, all rows in the table will be returned, subject to the number of rows specified in the limit field. The REST API server's default limit is 100 rows, but this can be configured in the REST configuration file.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb/tables/classes/query
Request Body
{}
Response
{
    "results": [
        {
            "classid": 1,
            "level": 101,
            "subject": "French 1",
            "department": "Languages"
        },
        {
            "classid": 2,
            "level": 101,
            "subject": "Algebra 1",
            "department": "Mathematics"
        },
        {
            "classid": 3,
            "level": 102,
            "subject": "Algebra 2",
            "department": "Mathematics"
        },
        {
            "classid": 4,
            "level": 110,
            "subject": "Geometry",
            "department": "Mathematics"
        },
        {
            "classid": 5,
            "level": 111,
            "subject": "Trigonometry",
            "department": "Mathematics"
        },
        {
            "classid": 6,
            "level": 101,
            "subject": "Chemistry",
            "department": "Science"
        },
        {
            "classid": 7,
            "level": 105,
            "subject": "Biology",
            "department": "Science"
        },
        {
            "classid": 8,
            "level": 110,
            "subject": "Physics",
            "department": "Science"
        }
    ],
    "hasMore": false,
    "responseTime": 9
}

Example 7: Query a table using limit and skip

Any query request can override the default limit by providing a limit filed directly in the request body. Specify any positive integer or set the limit to -1 to ensure all rows are returned in one response.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb/tables/classes/query
Request Body
{
    "limit": 3
}
Response
{
    "results": [
        {
            "classid": 1,
            "level": 101,
            "subject": "French 1",
            "department": "Languages"
        },
        {
            "classid": 2,
            "level": 101,
            "subject": "Algebra 1",
            "department": "Mathematics"
        },
        {
            "classid": 3,
            "level": 102,
            "subject": "Algebra 2",
            "department": "Mathematics"
        }
    ],
    "hasMore": true,
    "responseTime": 17
}

In addition to the query results, each query response includes a hasMore field. This field indicates whether or not there are still more rows to return for that query. If hasMore is true, then the results array included in the response was bound by the limit (either by the explicit limit in the request or by the default limit). If hasMore is false, then all of the rows that matched the query request were included in the response. If you want to ensure that all rows are returned in a single response, you must set limit to -1 in your query request body.

If, like in the example above, you receive a response where hasMore is true, then you can retrieve the next batch of rows by reissuing the same REST query request with the addition of a skip field to indicate where to start with the next batch of results. For example, to receive the next batch of results after the query above, you would reissue the same query request but with a skip of 3 since 3 rows were returned in the first batch.
Request
POST http://localhost:8080/api/servers/server1/databases/mydb/tables/classes/query
Request Body
{
    "limit": 3,
    "skip": 3
}
Response
{
    "results": [
        {
            "classid": 4,
            "level": 110,
            "subject": "Geometry",
            "department": "Mathematics"
        },
        {
            "classid": 5,
            "level": 111,
            "subject": "Trigonometry",
            "department": "Mathematics"
        },
        {
            "classid": 6,
            "level": 101,
            "subject": "Chemistry",
            "department": "Science"
        }
    ],
    "hasMore": true,
    "responseTime": 13
}

In this way, you can continue to iterate over each batch of query results until all rows are returned.

Important: When iterating over query results, it is a good idea to use an orderBy clause as shown in the next example to ensure a consistent order of result rows as you request each additional batch of query results.

Example 8: Query a table using projection fields and an order by clause

You can set the fields property in your query request body to specify which columns to include in the results. If specified, fields must be an array of column names that exist in the table queried.

To control the order of rows returned from a query, include an orderBy property in the query request document. The orderBy property must be an array of documents that define both the column name (key) and direction to order by ("asc" for ascending and "desc" for descending).

Request
POST http://localhost:8080/api/servers/server1/databases/mydb/tables/classes/query
Request Body
{
    "fields": ["department", "level", "subject"],
    "orderBy": [ { "key": "department", "direction": "asc"}, { "key": "level", "direction": "desc"} ]
}
Response
{
    "results": [
        {
            "department": "Languages",
            "level": 101,
            "subject": "French 1"
        },
        {
            "department": "Mathematics",
            "level": 111,
            "subject": "Trigonometry"
        },
        {
            "department": "Mathematics",
            "level": 110,
            "subject": "Geometry"
        },
        {
            "department": "Mathematics",
            "level": 102,
            "subject": "Algebra 2"
        },
        {
            "department": "Mathematics",
            "level": 101,
            "subject": "Algebra 1"
        },
        {
            "department": "Science",
            "level": 110,
            "subject": "Physics"
        },
        {
            "department": "Science",
            "level": 105,
            "subject": "Biology"
        },
        {
            "department": "Science",
            "level": 101,
            "subject": "Chemistry"
        }
    ],
    "hasMore": false,
    "responseTime": 31
}

Example 9: Query a table using a query filter

A query request body can also include a filter defines which rows to return in the result. The REST API supports two types of query filters: a comparison query filter and a logical query filter.
Comparison Query Filter

1  { "op" :
2.1! =
2.1 !=
2.1 <
2.1 <=
2.1 >
2.1 >=
2.1 IN
2.1 NOT IN
1 ,
1 

1  "key" : column_name , 

1  "value" : value }

A comparison query filter is used to compare a column's value against some constant, or in the case of the IN or NOT IN operations, a list of constants. When using a comparison query filter, you must provide a JSON document with an op field defining the comparison operator (=, !=, <, <=, >, >=, IN, or NOT IN), a key field defining the column name, and a value field defining the value(s) to compare against. For example, { "op": ">", "key": "level", "value": 110 } will match all rows where the level column is greater than 110. For equality comparisons, the op field is optional meaning that { "key": "subject", "value": "Algebra 1" } is the equivalent of { "op": "=", "key": "subject", "value": "Algebra 1" }.

Logical Query Filter

1 {"op":
2.1 and
2.1 or
1, "filters":[+ ,
2.1 Comparison Query Filter
2.1 Logical Query Filter]}

A logical query filter is an AND or OR query filter that logically combines two or more other query filters. The query filters combined by the AND or OR operation can be comparison query filters and/or additional logical query filters.

A logical query filter is specified as a JSON document with an op field set to and or or and an additional filters field that provides the list of comparison and/or logical query filter documents to be combined by the logical and or or operation.

For example, the query filter in the following request will return all rows in the table whose subject is "Algebra 1" or "Algebra 2" or whose department is "Science" with a "level" less than 110.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb/tables/classes/query
Request Body
{
    "filter": {  
        "op": "or", 
        "filters": [
            { "op": "=", "key": "subject", "value": "Algebra 1"},
            { "op": "=", "key": "subject", "value": "Algebra 2"}, 
            { 
                "op": "and", 
                "filters": [
                    { "op": "=", "key": "department", "value": "Science"},
                    { "op": "<", "key": "level", "value": "110"}                
                ] 
            }
        ]
    }
}
Response
{
    "results": [
        {
            "classid": 2,
            "level": 101,
            "subject": "Algebra 1",
            "department": "Mathematics"
        },
        {
            "classid": 3,
            "level": 102,
            "subject": "Algebra 2",
            "department": "Mathematics"
        },
        {
            "classid": 6,
            "level": 101,
            "subject": "Chemistry",
            "department": "Science"
        },
        {
            "classid": 7,
            "level": 105,
            "subject": "Biology",
            "department": "Science"
        }
    ],
    "hasMore": false,
    "responseTime": 10
}

The query filter can be combined with any the other query options discussed above, including fields, orderBy, limit, and skip as shown in the following example.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb/tables/classes/query
Request Body
{
    "fields": ["subject", "level"],
    "filter": { "op": "IN", "key": "department", "value": ["Languages", "Science"] },
    "orderBy": [ { "key": "subject", "direction": "asc" } ],
    "limit": -1,
    "skip": 0
}
Response
{
    "results": [
        {
            "subject": "Biology",
            "level": 105
        },
        {
            "subject": "Chemistry",
            "level": 101
        },
        {
            "subject": "French 1",
            "level": 101
        },
        {
            "subject": "Physics",
            "level": 110
        }
    ],
    "hasMore": false,
    "responseTime": 11
}

Example 10: Update rows in a table

To update rows in a table, POST a JSON document to the update API describing the changes to make to one or more rows in the table. The JSON document must include a filter and an update field.

The filter field defines which rows in the table to update. To update all rows in the table, specify the filter as an empty document. To define specific rows to update, the filter document follows the same format as query filters defined above.

The update field is a document of key-value pairs representing to the column name to update and the new value for the column.

The example below sets the level column of the classes table to 120 for the class whose subject is "Biology". While this example shows only one column (level) to update, you can include as many column key/value pairs as you want in the update document.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb/tables/classes/update
Request Body
{
    "filter": {
        "key": "subject",
        "op": "=",
        "value": "Biology"
    },
    "updates": {
        "level": 120
    }
}
Response
{
    "n": 1,
    "responseTime": 10
}

Example 11: Delete rows from a table

To delete rows from a table, POST a JSON document to the delete API. This document must contain a filter field that defines the rows to be deleted. The format of the filter fields is the same as the query filters described above. The n field in the delete response will indicate the number of rows deleted from the table.
Request
POST http://localhost:8080/api/servers/server1/databases/mydb/tables/classes/delete
Request Body
{
    "filter": {
        "key": "classid",
        "op": "=",
        "value": 6
    }
}
Response
{
    "n": 1,
    "responseTime": 17
}
To delete all rows in a table, set the filter field to an empty document.
Note: When deleting all rows, a TRUNCATE statement is used; as a result, the n in the response will always be zero and will not accurately reflect the number of rows deleted from the table.

Example 12: Drop a table

To drop a table from the database, run an HTTP DELETE request against the table url. For example:
Request
DELETE http://localhost:8080/api/servers/server1/databases/mydb/tables/classes
Response
HTTP Status OK (200)