REST API Examples: CLOB and BLOB data

This topic provides a tutorial on the REST APIs for working with CLOB and BLOB data types.

The REST API has a couple of APIs specific to CLOB and BLOB data types. When you insert and update CLOB and BLOB data, you can use the regular relational table insert API. When you query a table with a CLOB or BLOB table type, however, the JSON representing the row will not include your CLOB or BLOB data directly. Instead you will get the large object locator that identifies the CLOB/BLOB data and you can issue a follow-up REST request to fetch the object associated with the locator. The examples below demonstrate this process.

Example 1: Querying for CLOB data

To query for CLOB data, you begin by querying your table using the relational table query API. CLOB columns in the query results will be represented in JSON by metadata describing the CLOB, including the large object type, the LOB locator id, and a url that you can use to retrieve the full CLOB value. For example:
"clob_column": {
        "$lob": {
                "type": "clob",
                "id": <locator_id>,
                "url": <url to retrieve the CLOB value>
        }
}

The following example shows a REST query against a table with a CLOB column.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb1/tables/clobtab/query
Request Body
{ "limit": 1 }
Response
{
    "results": [
        {
            "id": 1,
            "type": "SPEC",
            "c": {
                "$lob": {
                    "type": "clob",
                    "id": "01000000d9c8b7a6020000000500000001000000e7c2aa5f000000000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000",
                    "url": "http://localhost:8080/api/servers/server1/databases/mydb1/clobs/01000000d9c8b7a6020000000500000001000000e7c2aa5f000000000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000"
                }
            }
        }
    ],
    "hasMore": false,
    "responseTime": 26
}

To retrieve the actual CLOB value, run an HTTP GET request on the url provided within the $lob sub-document. CLOB values are returned in an HTTP Response with Content-Type text/plain. The entire CLOB value will be returned in the response.

Request
GET http://localhost:8080/api/servers/server1/databases/mydb1/clobs/01000000d9c8b7a6020000000500000001000000e7c2aa5f000000000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000
Response
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor 
incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud 
exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat...

Example 2: Inserting and Updating CLOB data

To insert or update rows with a CLOB column, follow the relational insert and update APIs. Provide the CLOB value as a text string within your insert or update document. The REST API will ensure the data gets inserted properly as a CLOB.
Request
POST http://localhost:8080/api/servers/server1/databases/mydb1/tables/clobtab/insert
Request Body
{ 
    "id": 2, 
    "type": "SPEC", 
    "c": "This is the start of my text..."
}
Response
{
    "n": 1,
    "responseTime": 45
}

Here is a CLOB update example.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb1/tables/clobtab/update
Request Body
{
    "filter": { "key": "id", "op": "=", "value": 2 },
    "updates": {
        "c": "A new version of my text..."
    }
}
Response
{
    "n": 1,
    "responseTime": 36
}

Example 3: Querying BLOB data

You query for BLOB data in the same way you do CLOB data. Begin by querying your table using the relational table query API. BLOB columns in query results will be represented as:
"blob_column": {
        "$lob": {
                "type": "blob",
                "id": <locator_id>,
                "url": <url to retrieve the BLOB value>
        }
}

The following example shows a REST query against a table with a BLOB column.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb1/tables/blobtab/query
Request Body
{ "limit": 1 }
Response
{
    "results": [
        {
            "id": 1,
            "b": {
                "$lob": {
                    "type": "blob",
                    "id": "01000000d9c8b7a6020000000500000002000000f0c2aa5f000000000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000",
                    "url": "http://localhost:8080/api/servers/server1/databases/mydb1/blobs/01000000d9c8b7a6020000000500000002000000f0c2aa5f000000000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000"
                }
            }
        }
    ],
    "hasMore": true,
    "responseTime": 34
}

To retrieve the actual BLOB value, run an HTTP GET request on the url provided within the $lob sub-document. BLOB values are returned in an HTTP Response with Content-Type application/octet-stream. The entire BLOB value will be returned in the response.

Request
GET http://localhost:8080/api/servers/server1/databases/mydb1/blobs/l01000000d9c8b7a6020000000500000002000000f0c2aa5f000000000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000
Response
<Binary data>

Example 4: Inserting and Updating BLOB data

To insert or update rows with a BLOB column, follow the relational insert and update APIs.

The REST API uses the MongoDB Extended JSON v2 format for JSON serialization. In this format, binary data is serialized to a JSON string as follows:
{ "$binary":
   {
      "base64": <Base64 encoded payload string (with padding as “=”)>,
      "subType": "<t>"
   }
}

Here is an example insert request that includes binary data to be inserted into the BLOB column named b.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb1/tables/blobtab/insert
Request Body
{ 
        "id": 2, 
        "b": {"$binary": "qqu7vMzN3d7u7/8=", "$type": "00"}
}
Response
{
    "n": 1,
    "responseTime": 48
}

Here is a similar BLOB update example.

Request
POST http://localhost:8080/api/servers/server1/databases/mydb1/tables/blobtab/update
Request Body
{
        "filter": { "key": "id", "op": "=", "value": 2 },
        "updates": {
            "b": {"$binary": "qqu7vMzN3d7u7/8=", "$type": "00"}
        }
}
Response
{
    "n": 1,
    "responseTime": 52
}