REST API Examples: JSON Collections

This topic provides a tutorial on accessing and querying JSON/BSON data in collections through REST.

The REST path /api/servers/{alias}/databases/{dbname}/collections provides APIs specific to JSON collections in HCL OneDB™.

A JSON collection holds BSON (binary JSON) data. BSON documents have a flexible schema and can be used with unstructed data where the structure and contents of BSON documents can differ from one document to another. This differs from relational tables where all rows must following the same predefined structure.

HCL OneDB fully supports JSON collections through both the REST API and the MongoDB API provided through the wire listener.

Note: You may notice that many of these examples look similar to those in the REST API Examples: Relational Tables. This is purposeful. The REST API aims for a unified API and uniform data access for all of the different HCL OneDB data types, even if the underlying database storage differs across the various types.

Example 1: List collections in a database

To list all of the collections in a database, send an HTTP GET request to the collections path for a particular database. Keep in mind that this API will only list the JSON collections in the database; it will not include any relational or TimeSeries tables that may also exist in the database.

Request
GET http://localhost:8080/api/servers/server1/databases/mydb/collections
Response
[
    "people",
    "catalog",
    "events"
    ...
]

Example 2: Create a JSON collection

A JSON collection is a specific type of table in OneDB that holds JSON documents. While from REST or your application perspective, you will work with the data in JSON format; on the database server, the data is stored in BSON (binary JSON).

To create a new JSON collection, POST a JSON document to the collections path that includes the new collection name and optionally the dbspace. The response will include information about the newly created JSON collection.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb/collections
Request Body
{
  "name": "classes"
}
Response
{
    "tabid": 104,
    "tabname": "classes",
    "owner": "onedbsa",
    "created": {
        "$date": "2021-03-10T06:00:00Z"
    },
    "type": "collection"
}

For more advanced users, you may want to control whether and what type of index is automatically created for your JSON collection. By default, the REST API will automatically created a unique index on the "_id" field within your documents. This matches the automatic creation of _id indexes when creating collections through the OneDB MongoDB API provided by the wire listener. If you do not want to automatically create an index on the _id field, you can set indexIdField to false in the JSON document you POST when creating the collection.

If you do want an index on the _id field, but want to control the index type, you can set the idIndexFunction field to the function you want to use for your BSON functional index. By default, a string-based index is created on the _id field in your JSON documents. If you wanted an integer index, however, you can set idIndexFunction to bson_value_int. The idIndexFunction is an advanced property, but by matching your index type to the data type you will be using in the _id field of your JSON documents, you may see better performance in queries on the _id field. See BSON processing functions for a list of available BSON functions.

Example 3: Get collection information

To get information about a JSON collection:

Request
GET http://localhost:8080/api/servers/server1/databases/mydb/collections/classes
Response
{
    "tabid": 104,
    "tabname": "classes",
    "owner": "informix",
    "created": {
        "$date": "2021-03-10T06:00:00Z"
    },
    "type": "collection"
}

Example 4: Insert a JSON document into a collection

To insert a JSON document into a collection, use the insert API to POST the JSON document to insert. The contents of this JSON document are flexbile and can vary from document to document within the same collection if desired.

If an _id field is not included in the JSON document to insert, a unique _id will automatically be added by the REST API. When added by the REST API, the _id field will be a MongoDB compatible ObjectId ($oid). This behavior also matches wire listener inserts into a JSON collection.

The HTTP response to an insert includes the number of documents inserted and the response time in milliseconds.

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

Example 5: Load multiple rows into a table

To load (insert) multiple documents into a collection in a single request, use the load API. POST a JSON document with a data array of documents to insert.

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

Request
POST http://localhost:8080/api/servers/server1/databases/mydb/collections/classes/load
Request Body
{
  "data": [
    {
      "classid": 2,
      "level": "101",
      "subject": "Algebra 1",
      "department": "Mathematics"
    },
    {
      "classid": 3,
      "level": "102",
      "subject": "Algebra 2",
      "department": "Mathematics",
      "prereqs": ["Algebra 1"]
    },
    {
      "classid": 4,
      "level": "110",
      "subject": "Geometry",
      "department": "Mathematics",
      "location": {
          "building": "AM1",
          "room": 111
      }
    },
    {
      "classid": 5,
      "level": "111",
      "subject": "Trigonometry",
      "department": "Mathematics",
      "prereqs": ["Algebra 1", "Geometry"],
      "comments": "2nd semester only",
      "location": {
          "building": "AM1",
          "room": 104
      }
    },
    {
      "classid": 6,
      "level": "101",
      "subject": "Chemistry",
      "department": "Science",
      "comments": {
          "building": "SCH",
          "room": 111
      }
    },
   {
      "classid": 7,
      "level": "105",
      "subject": "Biology",
      "department": "Science"
    },
    {
      "classid": 8,
      "level": "110",
      "subject": "Physics",
      "department": "Science"
    }
  ]
}
Response
{
    "n": 7,
    "responseTime": 35
}

Example 6: Query for all documents in a collection

To query a collection, 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 documents in the collection will be returned, subject to the number of documents specified in the limit field. The REST API server's default limit is 100 documents, but this can be configured in the REST configuration file.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb/collections/classes/query
Request Body
{}
Response
{
    "results": [
        {
            "classid": 1,
            "level": "101",
            "subject": "French 1",
            "department": "Languages",
            "alt_subject": "Français 1",
            "_id": {
                "$oid": "60494d26917b337f4a9ca774"
            }
        },
        {
            "classid": 2,
            "level": "101",
            "subject": "Algebra 1",
            "department": "Mathematics",
            "_id": {
                "$oid": "60494e4a917b337f4a9ca775"
            }
        },
        {
            "classid": 3,
            "level": "102",
            "subject": "Algebra 2",
            "department": "Mathematics",
            "prereqs": [
                "Algebra 1"
            ],
            "_id": {
                "$oid": "60494e4a917b337f4a9ca776"
            }
        },
        {
            "classid": 4,
            "level": "110",
            "subject": "Geometry",
            "department": "Mathematics",
            "location": {
                "building": "AM1",
                "room": 111
            },
            "_id": {
                "$oid": "60494e4a917b337f4a9ca777"
            }
        },
        {
            "classid": 5,
            "level": "111",
            "subject": "Trigonometry",
            "department": "Mathematics",
            "prereqs": [
                "Algebra 1",
                "Geometry"
            ],
            "comments": "2nd semester only",
            "location": {
                "building": "AM1",
                "room": 104
            },
            "_id": {
                "$oid": "60494e4a917b337f4a9ca778"
            }
        },
        {
            "classid": 6,
            "level": "101",
            "subject": "Chemistry",
            "department": "Science",
            "comments": {
                "building": "SCH",
                "room": 111
            },
            "_id": {
                "$oid": "60494e4a917b337f4a9ca779"
            }
        },
        {
            "classid": 7,
            "level": "105",
            "subject": "Biology",
            "department": "Science",
            "_id": {
                "$oid": "60494e4a917b337f4a9ca77a"
            }
        },
        {
            "classid": 8,
            "level": "110",
            "subject": "Physics",
            "department": "Science",
            "_id": {
                "$oid": "60494e4a917b337f4a9ca77b"
            }
        }
    ],
    "hasMore": false,
    "responseTime": 67
}

Example 7: Query a collection 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/collections/classes/query
Request Body
{
    "limit": 3
}
Response
{
    "results": [
        {
            "classid": 1,
            "level": "101",
            "subject": "French 1",
            "department": "Languages",
            "alt_subject": "Français 1",
            "_id": {
                "$oid": "60494d26917b337f4a9ca774"
            }
        },
        {
            "classid": 2,
            "level": "101",
            "subject": "Algebra 1",
            "department": "Mathematics",
            "_id": {
                "$oid": "60494e4a917b337f4a9ca775"
            }
        },
        {
            "classid": 3,
            "level": "102",
            "subject": "Algebra 2",
            "department": "Mathematics",
            "prereqs": [
                "Algebra 1"
            ],
            "_id": {
                "$oid": "60494e4a917b337f4a9ca776"
            }
        }
    ],
    "hasMore": true,
    "responseTime": 17
}

In addition to the query results, each query reponse includes a hasMore field. This field indicates whether or not there are still more documents 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 documents that matched the query request were included in the response. If you want to ensure that all documents 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, you can retrieve the next batch of documents 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 documents were returned in the first batch.
Request
POST http://localhost:8080/api/servers/server1/databases/mydb/collections/classes/query
Request Body
{
    "limit": 3,
    "skip": 3
}
Response
{
    "results": [
        {
            "classid": 4,
            "level": "110",
            "subject": "Geometry",
            "department": "Mathematics",
            "location": {
                "building": "AM1",
                "room": 111
            },
            "_id": {
                "$oid": "60494e4a917b337f4a9ca777"
            }
        },
        {
            "classid": 5,
            "level": "111",
            "subject": "Trigonometry",
            "department": "Mathematics",
            "prereqs": [
                "Algebra 1",
                "Geometry"
            ],
            "comments": "2nd semester only",
            "location": {
                "building": "AM1",
                "room": 104
            },
            "_id": {
                "$oid": "60494e4a917b337f4a9ca778"
            }
        },
        {
            "classid": 6,
            "level": "101",
            "subject": "Chemistry",
            "department": "Science",
            "comments": {
                "building": "SCH",
                "room": 111
            },
            "_id": {
                "$oid": "60494e4a917b337f4a9ca779"
            }
        }
    ],
    "hasMore": true,
    "responseTime": 11
}

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

Note: 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 documents as you request each additional batch of query results.

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

You can set the fields property in your query request body to specify which key/value pairs from your JSON documents to include in the results. If specified, fields must be an array key names.

To control the order of documents 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 key name and direction to order by ("asc" for ascending and "desc" for descending).

Request
POST http://localhost:8080/api/servers/server1/databases/mydb/collections/classes/query
Request Body
{
    "fields": ["department", "level", "subject"],
    "orderBy": [ { "key": "department", "direction": "asc"}, { "key": "level", "direction": "desc"} ]
}
Response
{
    "results": [
        {
            "level": "101",
            "subject": "French 1",
            "department": "Languages"
        },
        {
            "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": "Mathematics"
        },
        {
            "level": "110",
            "subject": "Physics",
            "department": "Science"
        },
        {
            "level": "105",
            "subject": "Biology",
            "department": "Science"
        },
        {
            "level": "101",
            "subject": "Chemistry",
            "department": "Science"
        }
    ],
    "hasMore": false,
    "responseTime": 96
}

Example 9: Query a collection using a query filter

A query request body can also include a filter defines which documents to return in the result. The REST API supports two types of query filters: a comparison query filter and a logical query filter. This is the same query format as that used for relational tables.
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" : key_name , 

1  "value" : value 

1  "function" : bson_function }

A comparison query filter is used to compare a particular value in a JSON document 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 key name, and a value field defing the value(s) to compare against. For example, { "op": ">", "key": "level", "value": 110 } will match all documents where the level field 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 this list of comparison and/or logical query filter documents to be combined by the logical and or or operataion.

For example, the query filter in the following request will return all documents in the classes collection 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/collections/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",
            "_id": {
                "$oid": "60494e4a917b337f4a9ca775"
            }
        },
        {
            "classid": 3,
            "level": "102",
            "subject": "Algebra 2",
            "department": "Mathematics",
            "prereqs": [
                "Algebra 1"
            ],
            "_id": {
                "$oid": "60494e4a917b337f4a9ca776"
            }
        },
        {
            "classid": 6,
            "level": "101",
            "subject": "Chemistry",
            "department": "Science",
            "comments": {
                "building": "SCH",
                "room": 111
            },
            "_id": {
                "$oid": "60494e4a917b337f4a9ca779"
            }
        },
        {
            "classid": 7,
            "level": "105",
            "subject": "Biology",
            "department": "Science",
            "_id": {
                "$oid": "60494e4a917b337f4a9ca77a"
            }
        }
    ],
    "hasMore": false,
    "responseTime": 76
}

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/collections/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": [
        {
            "level": "105",
            "subject": "Biology"
        },
        {
            "level": "101",
            "subject": "Chemistry"
        },
        {
            "level": "101",
            "subject": "French 1"
        },
        {
            "level": "110",
            "subject": "Physics"
        }
    ],
    "hasMore": false,
    "responseTime": 25
}

Example 10: Query a collection using a query filter on a nested field

If your JSON documents include nested sub-documents, you can use dot notation to refer to nested fields within those sub-documents.

For example, in the following document, the "location" field contains a sub-document. 

{
        "classid": 4,
        "level": "110",
        "subject": "Geometry",
        "department": "Mathematics",
        "location": {
                "building": "AM1",
                "room": 111
        },
        "_id": {
                "$oid": "60494e4a917b337f4a9ca777"
            }
}

When querying, you can use the key "location.building" to refer to the "building" field within the "location" sub-document as in the following example.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb/collections/classes/query
Request Body
{
    "fields": ["subject", "level", "location"],
    "filter": { "op": "=", "key": "location.building", "value": "AM1" }
}
Response
{
    "results": [
        {
            "level": "110",
            "subject": "Geometry",
            "location": {
                "building": "AM1",
                "room": 111
            }
        },
        {
            "level": "111",
            "subject": "Trigonometry",
            "location": {
                "building": "AM1",
                "room": 104
            }
        }
    ],
    "hasMore": false,
    "responseTime": 39
}                                                }

Example 11: Query a collection using a specific BSON function

When applying a query filter, you may chose to add a function field to your comparison query filter to direct the REST API to use a particular BSON function for the query comparison. When a function is not specified, the REST API will use the bson_get function which will result in a BSON to BSON comparison on the database server. In some cases, you may want to use one of the BSON processing functions for comparisons instead, particularly in cases where you have used these functions to index particular fields in your JSON collection.

Possible function options include:


1  "function" :
2.1! bson_get
2.1 bson_value_lvarchar
2.1 bson_value_varchar
2.1 bson_value_int
2.1 bson_value_bigint
2.1 bson_value_double
2.1 bson_value_date
2.1 bson_value_timestamp
2.1 bson_value_boolean
1
In the following example, the same REST query is issued as in the previous example, just with function now set to bson_value_lvarchar which will result in a string comparison as opposed to a BSON comparison on the database server.
Note: The use of a BSON processing function in your query does not impact your query results. However it can improve query performance in certain cases, depending on how your JSON collection is indexed.
Request
POST http://localhost:8080/api/servers/server1/databases/mydb/collections/classes/query
Request Body
{
    "fields": ["subject", "level", "location"],
    "filter": { "op": "=", "key": "location.building", "value": "AM1",  "function": "bson_value_lvarchar"}
}
Response
{
    "results": [
        {
            "level": "110",
            "subject": "Geometry",
            "location": {
                "building": "AM1",
                "room": 111
            }
        },
        {
            "level": "111",
            "subject": "Trigonometry",
            "location": {
                "building": "AM1",
                "room": 104
            }
        }
    ],
    "hasMore": false,
    "responseTime": 56
}

Example 12: Update documents in a collection

To update documents in a collection, 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 JSON documents in the collection to update. To update all documents in the collection, specify the filter as an empty document. To define specific JSON documents 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 keys to update or add to the matching JSON document(s).

The example below sets the level field to 120 and the AP field to true for the class whose subject is "Biology".

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

Example 13: Replace a document in a collection

The update API will modify all of the fields in your matching JSON documents that are specified in the updates field of the request body. It will not modify any existing fields that are not specified in the updates field.

The REST API also provides a replace API for collections. This API will replace the JSON document(s) matched by the filter with the newDocument included in the request body.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb/collections/classes/replace
Request Body
{
    "filter": { "key": "classid", "op": "=", "value": 8},
    "newDocument": {
            "_id": {
                "$oid": "60494e4a917b337f4a9ca77b"
            },
            "classid": 8,
            "level": "110",
            "subject": "Physics",
            "department": "Science",
            "comments": {
                "building": "SCH",
                "room": 127
            }
        }
}
Response
{
    "n": 1,
    "responseTime": 79
}

Example 14: Delete documents from a collection

To delete documents from a collection, POST a JSON document to the delete API. This document must contain a filter field that defines the JSON documents 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 documents deleted from the collections.
Request
POST http://localhost:8080/api/servers/server1/databases/mydb/collections/classes/delete
Request Body
{
    "filter": {
        "key": "classid",
        "op": "=",
        "value": 6
    }
}
Response
{
    "n": 1,
    "responseTime": 31
}
To delete all documents in a collection, set the filter field to an empty document.
Note: When deleting all documents, 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 documents deleted from the collection.

Example 15: Drop a collection

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