Manipulate BSON data with SQL statements

You might have an existing application on relational tables that uses SQL to access the data, but you want to add BSON data to your database. You can create a table with a BSON column, insert the data, and manipulate the data with SQL statements. BSON documents that you insert through SQL statements or HCL OneDB utilities do not contain generated ObjectId field-value pairs or other MongoDB metadata.

Alternatively, you might use a MongoDB client for daily data processing, but need the querying capabilities of SQL for data analysis. For example, you can use SQL statements to join tables that have BSON columns with other tables based on BSON field values. You can create views that have columns of BSON field values. You can run warehouse queries on BSON data with . If you have time series data, you can use the corresponding specialized SQL routines to analyze the data.

You can use BSON processing functions to manipulate BSON data in SQL statements. The BSON value functions convert BSON field values to standard SQL data types, such as INTEGER and LVARCHAR. The BSON_GET function retrieves field-value pairs and the BSON_UPDATE function manipulates field-value pairs. You can convert all or part of a relational table to a BSON document with the genBSON function.

Example: Using SQL to query a collection

db.createCollection("people"); 
db.people.insert({"name":"Anne","age":31}); 
db.people.insert({"name":"Bob","age":39}); 
db.people.insert({"name":"Charlie","age":29});

For SQL statements, the table name is people and the BSON column name is data. When you create a collection through a MongoDB API command, the name of the BSON column is always set to data.

> SELECT data.name::JSON, data.age::JSON FROM people;

(expression)  {"name":"Anne"}
(expression)  {"age":31}

(expression)  {"name":"Bob"}
(expression)  {"age":39}

(expression)  {"name":"Charlie"}
(expression)  {"age":29}

3 row(s) retrieved.