Running SQL commands by using the MongoDB API

You can run SQL statements by using the MongoDB API and retrieve results back. The results of the SQL statements are treated like they are documents in a JSON collection.

Before you begin

You must enable SQL operations by setting security.sql.passthrough=true in the wire listener properties file.

Procedure

From the MongoDB shell command, use the abstract system collection system.sql as the collection name and $sql as the query operator, followed by the SQL statement.

For example:

> db.getCollection("system.sql").find({ "$sql": "sql_statement" })

To use host variables, include question marks in the SQL statement, and include the $bindings operator with an array that contains a value for each host variable in order of appearance. For example:

> db.getCollection("system.sql").find({ "$sql": "sql_statement", 
  "$bindings": [values]})

Examples

Create an SQL table

In this example, an SQL table is created by running the HCL OneDB™ CREATE TABLE command by using the MongoDB API:

> db.getCollection("system.sql").find({ "$sql": "create table foo (c1 int)" })

Drop an SQL table

In this example, an SQL table is dropped by running the HCL OneDB DROP TABLE command by using the MongoDB API:

> db.getCollection("system.sql").find({"$sql": "drop table foo" })

Delete SQL customer call records that are more than 5 years old

In this example, customer call records stored in SQL tables are deleted by running the HCL OneDB DELETE command by using the MongoDB API:

> db.getCollection("system.sql").findOne({ "$sql": 
  "delete from cust_calls where (call_dtime + interval(5) year to year) < current" })

Result: 7 rows were deleted.

{ "n" : 7 }

Join JSON collections

In this example, a query counts the number of orders customers placed by using an outer join to include the customers who did not place orders.

> db.getCollection("system.sql").find({ "$sql": "select
 c.customer_num,o.customer_num as order_cust,count(order_num) as
 order_count from customer c left outer join orders o on
 c.customer_num = o.customer_num group by 1, 2 order by 2" })

Result:

{ "customer_num" : 113, "order_cust" : null, "order_count" : 0 }
{ "customer_num" : 114, "order_cust" : null, "order_count" : 0 }
{ "customer_num" : 101, "order_cust" : 101, "order_count" : 1 }
{ "customer_num" : 104, "order_cust" : 104, "order_count" : 4 }
{ "customer_num" : 106, "order_cust" : 106, "order_count" : 2 }

Delete rows based on a host variable

In this example, the statement includes a host variable that specifies to delete the rows that have the name "john".

> db.getCollection("system.sql").find({"$sql": "delete from mytab 
                                  where name = ?", "$bindings" : ["john"] })

Run a user-defined function with host variables

In this example, the statement runs a user-defined routine with two host variables to raise prices.

> db.getCollection("system.sql").find({
  "$sql": "execute function raise_price(?, ?)", 
  "$bindings" : [101, 0.10] })