Manipulate BSON data with SQL statements

As an alternative to using the MongoDB API, you can use Informix® SQL to manipulate BSON data. 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.

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 Informix® 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 Informix® Warehouse Accelerator. 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 and BSON_UPDATE functions manipulate 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

In the following example, a table that is named people is created with names and ages fields that are inserted by using the interactive JavaScript™ shell interface to MongoDB:

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 set to data.

The following statement selects the name and age fields with dot notation and displays the results in a readable format by casting the results to JSON:

> 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.