BSON and JSON built-in opaque data types

The BSON and JSON data types are built-in opaque types of HCL OneDB™ that can be accessed and manipulated in local and distributed queries and in other DML operations. These are SQL data types that directly support relational database operations on data in BSON or JSON document store format. However, if you plan to query JSON and BSON data through the wire listener, you must create your database objects, such as collections and indexes, through the wire listener. You can use SQL statements to query JSON and BSON data whether you created your database objects through the wire listener or with SQL statements.

JSON and BSON documents contain one or more fields, which are similar to columns, and values for fields. A JSON or BSON column can contain multiple documents, each similar to a row in a relational database. The data values in JSON and BSON documents can be numbers, strings, or Boolean values. Documents can be nested within documents.

The database server validates JSON or BSON documents that you insert into the database. The JSON data type contains plain text. The BSON data type is a binary format of the JSON data type. You can create a table column of either the JSON or the BSON data type, however, the BSON data type is better suited for storing structured data in an HCL OneDB database. The database server can operate on data in BSON columns, but can only insert and display data in JSON columns.

JSON and BSON documents up to 4 KB are stored in-row. Documents that are greater than 4 KB in size are stored in the sbspace that is associated with the table, or the default sbspace if the table does not have a designated sbspace. The maximum size of a JSON or BSON document is 32 KB. The maximum size of a JSON or BSON column is limited only by the operating system.

JSON data type

The JSON data type is a plain text format for entering and displaying structured data. JSON is named after the JavaScript Object Notation (JSON), a data-interchange format that is based on the object-literal notation of JavaScript.

A JSON document has the format:

Figure 1: JSON document

1  { + , field :
2.1 ? " value? "
2.1 %JSON document
2.1 %JSON array  }
JSON array

1  [ + ,
2.1 ? " value? "
2.1 %JSON document
2.1 %JSON array ]
Element Description
field A string of Unicode characters that represents a field name.
value A string of Unicode characters that represents a data value.
JSON documents require the following notation:
  • Braces ({ and }) surround each JSON document. JSON documents can be nested.
  • A comma (, ) separates field-value pairs.
  • Double quotation marks ( " ) delimit character values. Quotation marks are not necessary around numeric values.
  • Brackets ([ and ]) surround arrays of values. Arrays can contain nested documents or arrays.

The following JSON document has a nested document and an array:

{ "person" : {
     "givenname" : "Jim", 
     "surname" : "Flynn",
     "age" : 29,
     "cars" : [ "dodge", "olds" ]
     }
}

BSON data type

The BSON data type is the binary representation of a JSON data type format for serializing JSON documents.

When you insert JSON documents through the wire listener with MongoDB API commands, a BSON column that is named data is created in the specified collection. The MongoDB API command also automatically adds an ObjectId field-value pair. When you insert JSON and BSON documents through SQL statements or HCL OneDB utilities, the documents do not contain ObjectIds unless you explicitly include them.

To create a BSON column in a table with an SQL statement, use the standard CREATE TABLE statement and specify a BSON column. The only valid default value for a BSON column is NULL.

The BSON data type differs from standard SQL data types in that you must cast the data or use built-in BSON processing functions:

  • To insert data that is in plain text with an SQL statement into a BSON column, you must cast the data to JSON. If you do not provide the cast to JSON, the database server treats the document as a string.
  • To view BSON data in a readable format when you select data from a BSON column with an SQL statement, you must either cast the column to JSON or use a BSON value function in a function expression. BSON value functions convert field values in BSON columns to standard SQL data types. You can use dot notation to select data from specific fields within the BSON column and a JSON cast to return the results in a readable format.
  • To update BSON data with an SQL statement, use the BSON_GET or the BSON_UPDATE function.
  • When you include a JSON document as an expression in an SQL statement that operates on a BSON column, you must cast the document to JSON and then to BSON.
  • To create an index on a BSON field, use the BSON_GET function and the USING BSON keywords as the index-key specification in the CREATE INDEX statement.

For more information, see BSON processing functions.

Supported SQL operations for BSON and JSON data types

The following table lists supported DDL operations on BSON and JSON data types.

Table 1. DDL operations on BSON and JSON data type
Operation Supported for BSON data type Supported for JSON data type
Create a table or a temporary table with one or more columns of the data type or a distinct type of the data type Yes Yes
Alter a table to add a column of the data type Yes Yes
Drop a column of the data type Yes Yes
Create a B-tree index on a column of the data type No No
Create an index or a functional index on a field Yes No
Create a basic text search index on a column of the data type Yes Yes
Truncate tables with columns of the data type Yes Yes
Fragment a table based on field values Yes Yes
Create a view based on columns of the data type and field values Yes No
Compress data in a column of the data type Yes Yes
Create a cast on the data type Yes Yes
Create triggers on fields Yes No
Include a column of the data type in a TimeSeries row data type Yes No

The following table lists supported DML operations on BSON and JSON data types.

Table 2. DML operations on BSON and JSON data
Operation Supported for BSON data type Supported for JSON data type
Cast data Yes Yes
Use a cursor to read data Yes Yes
Select contents of column of the data type Yes Yes
Select field values with dot notation or with BSON processing functions Yes No
Update data Yes No
Insert data Yes Yes
Find the size of a field value Yes No
Update statistics on a table with columns of the data type Yes Yes
Merge tables based on field values Yes No
Join tables based on field values Yes No
Load and unload data with the LOAD and UNLOAD statements, external tables, the onload and onunload utilities, or the dbschema, dbexport, and dbimport utilities Yes Yes
Replicate data to Remote stand-alone secondary servers Yes No
Shard data among Enterprise Replication servers Yes No

Restrictions on BSON and JSON data types

The following operations are not supported for BSON or JSON data types:

  • Encryption of BSON or JSON columns
  • Table-level restore of tables that have BSON or JSON columns
  • Capture data through the Change Data Capture API

Examples

The examples use the following document:

{ "person" : {
     "givenname" : "Jim", 
     "surname" : "Flynn",
     "age" : 29,
     "cars" : [ "dodge", "olds" ]
     }
}

Example: Create a table with a BSON column

The following statements create a table with a BSON column and insert a document:

CREATE DATABASE testdb WITH LOG;
CREATE TABLE IF NOT EXISTS bson_table(bson_col BSON);

INSERT INTO bson_table VALUES(
  '{person:{givenname:"Jim",surname:"Flynn",age:29,cars:["dodge","olds"]}}'::JSON);

The document is explicitly cast to JSON and then implicitly cast to BSON.

Example: Select the whole BSON column

The following statement returns the contents of the BSON column:

SELECT bson_col::JSON FROM bson_table;

(expression)
{"person":{"givenname":"Jim","surname":"Flynn","age":29,"cars":["dodge","olds"]}}

If the column is not cast to JSON format, the returned data is in an unreadable binary format.

Example: Select a field-value pair with dot notation

The following statement returns the value of the surname field:

SELECT bson_col.person.surname::JSON FROM bson_table;

  (expression)  
  {surname:"Flynn"}

The dot notation uses the same format as table_name.column_name, but specifies a field within the column. The cast to JSON is necessary, and the returned value is a JSON document with the specified field-value pair.

Example: Select a field value with a BSON value function

The following statement returns the value of the surname field:

SELECT BSON_VALUE_LVARCHAR(bson_col, "person.surname") FROM bson_table;

   (expression)  
   Flynn

The BSON_VALUE_LVARCHAR function converts the value of the surname field to an LVARCHAR data type. A cast to JSON is not needed, and the returned value is only the value of the specified field.

Example: Create an index on a BSON field

The following statement creates an index on the surname field in the BSON column:

CREATE INDEX idx2 ON bson_table(
   BSON_GET(bson_col, "person.surname")) USING BSON;

The BSON_GET function specifies that the surname field is indexed. The USING BSON keywords are necessary to specify that the index is created on a BSON column.