HCL OneDB JSON commands

db.runCommand({command_document})

• createTextIndex
• exportCollection
• importCollection
• lockAccounts
• runFunction
• runProcedure
• transaction
• unlockAccounts

createTextIndex

Create HCL OneDB Basic Text Search (BTS) indexes.

1 createTextIndex:"collection_name", name:"indexName"? , key:{+ , " column "} , options:{? %btx index parameters(1)}

createTextIndex

This required parameter specifies the name of the collection or relational table where the BTS index is created.

name

This required parameter specifies the name of the BTS index.

options

This required parameter specifies the name-value pairs for the BTS parameters that are used when creating the index. If no parameter values are required, you can specify an empty document.

Use BTS index parameters to customize the behavior of the index and how text is indexed. Include JSON index parameters to control how JSON and BSON documents are indexed. For example, you can index the documents as field name-value pairs instead of as unstructured text so that you can search for text by field. The name and values of the BTS index parameters in the options parameter are the same as the syntax for creating a BTS access method with the SQL CREATE INDEX statement. The following example creates an index named articlesIdx on the articles collection by using the BTS parameter all_json_names="yes":

db.runCommand({ 
 createTextIndex:"articles", 
 name:"articlesIdx", 
 options:{all_json_names:"yes"}})

key

This parameter is required if you are indexing relational tables, but optional if you are indexing collections. This parameter specifies which columns to index for relational tables.

The following example creates an index named myidx in the mytab relational table on the title and abstract columns:

db.runCommand({ 
 createTextIndex:"mytab", 
 name:"myidx", 
 key:{"title":"text","abstract":"text"}, 
 options:{}})

exportCollection

Export JSON collections from the wire listener to a file.

1 exportCollection:"collection_name", file:"filepath", format:
2.1 
2.2.1 "!json"
2.2.1 "jsonArray"
2.1?  , fields : { " + , filter " }
2.1 "csv", fields : { " + , filter " }?  , query : { " query_document " }

exportCollection

This required parameter specifies the collection name to export.

file

This required parameter specifies the output file path on the host machine where the wire listener is running. For example:

• UNIX™ is file:"/tmp/export.out"
• Windows™ is file:"C:/temp/export.out"

format

This required parameter specifies the exported file format.

json

Default. The .json file format. One JSON-serialized document per line is exported.

The following command exports all documents from the collection that is named c by using the json format:

> db.runCommand({exportCollection:"c",file:"/tmp/export.out"
 ,format:"json"})

The result of this command will look like this:

{
  "ok":1,
  "n":1000,
  "millis":NumberLong(119),
  "rate":8403.361344537816
}
              

Where "n" is the number of documents that are exported, "millis" is the number of milliseconds it took to export, and "rate" is the number of documents per second that are exported.

jsonArray

The .jsonArray file format. This format exports an array of JSON-serialized documents with no line breaks. The array format is JSON-standard.

The following command exports all documents from the collection c by using the jsonArray format:

> db.runCommand({exportCollection:"c",file:"/tmp/export.out"
 , format:"jsonArray"})
{
	"ok":1,
	"n":1000,
	"millis":NumberLong(81),
	"rate":12345.67901234568
}

Where "n" is the number of documents that are exported, "millis" is the number of milliseconds it took to export, and "rate" is the number of documents per second that are exported.

csv

The .csv file format. Comma-separated values are exported. You must specify which fields to export from each document. The first line of the .csv file contains the fields and all subsequent lines contain the comma-separated document values.

fields

This parameter specifies which fields are included in the output file. This parameter is required for the csv format, but optional for the json and jsonArray formats.

The following command exports all documents from the collection that is named c by using the csv format, only output the "_id" and "name" fields:

> db.runCommand({exportCollection: "c" ,file:"/tmp/export.out",
 format: "csv", fields: {"_id": 1, "name": 1}})

query

This optional parameter specifies a query document that identifies which documents are exported. The following example exports all documents from the collection that is named c that have a "qty" field that is less than 100:

> db.runCommand({exportCollection: "c", file: "/tmp/export.out",
  format: "json", query: {"qty": {"$lt": 100}}})

importCollection

Import JSON collections from the wire listener to a file.

1 importCollection:"collection_name", file:"filepath", format:"
2.1! json
2.1 jsonArray
2.1 csv
1"

importCollection

The required parameter specifies the collection name to import.

file

This required parameter specifies the input file path. For example, file: "/tmp/import.json".

Important: The input file must be on the same host machine where the wire listener is running.

format

This required parameter specifies the imported file format.

json

Default. The .json file format.

The following example imports documents from the collection that is named c by using the json format:

> db.runCommand({importCollection: "c", file: "/tmp/import.out",
  format:"json"})

jsonArray

The .jsonArray file format.

The following example imports documents from the collection c by using the jsonArray format:

> db.runCommand({exportCollection: "c", file: "/tmp/import.out",
  format:"jsonArray"})

csv

The .csv file format.

lockAccounts

1  lockAccounts:
2.1 1
2.2.1 ,db:
2.2.2.1 "database_name"
2.2.2.1 [+ ,"database_name"]
2.2.2.1 {"$regex":"json_document"}
2.2.2.1 {
2.2.2.2.1+ ,
2.2.2.2.1 "include":
2.2.2.2.2.1 "database_name"
2.2.2.2.2.1 [+ ,"database_name"]
2.2.2.2.2.1 {"$regex":"json_document"}
2.2.2.2.1 "exclude":
2.2.2.2.2.1 "database_name"
2.2.2.2.2.1 [+ ,"database_name"]
2.2.2.2.2.1 {"$regex":"json_document"}
2.2.2.1}
2.2.1"
2.2.1 ,user:
2.2.2.1 "user_name"
2.2.2.1 "json_document"

runFunction

Run an SQL function through the wire listener. This command is equivalent to the SQL statement EXECUTE FUNCTION.

1 runFunction:"function_name"? ,"arguments":[+ , argument]

runFunction

This required parameter specifies the name of the SQL function to run. For example, a current function returns the current date and time:

> db.runCommand({runFunction: "current"})
{"returnValue": 2016-04-05 12:09:00, "ok":1}

arguments

This parameter specifies an array of argument values to the function. You must provide as many arguments as the function requires. For example, an add_values function requires two arguments to add together:

> db.runCommand({runFunction: "add_values", "arguments": [3,6]})
{"returnValue": 9, "ok": 1}

The following example returns multiple values from a func_return3 function:

> db.runCommand({runFunction: "func_return3", "arguments" :[101]})
{"returnValue": {"serial_num": 1103, "name": "Newton", "points": 100}, "ok": 1}

runProcedure

Run an SQL stored procedure through the wire listener. This command is equivalent to the SQL statement EXECUTE PROCEDURE.

1 runProcedure:"procedure_name"? ,"arguments":[+ , argument]

runProcedure

This required parameter specifies the name of the SQL procedure to run. For example, a colors_list stored procedure, which uses a WITH RESUME clause in its RETURN statement, returns multiple rows about colors:

> db.runCommand({runProcedure: "colors_list"})
{"returnValue": [
  {"color" : "Red","hex" : "FF0000"},               
  {"color" : "Blue","hex" : "0000A0"},               
  {"color" :"White","hex" : "FFFFFF"}
], "ok" : 1}

arguments

This parameter specifies an array of argument values to the procedure. You must provide as many arguments as the procedure requires. For example, an increase_price procedure requires two arguments to identify the original price and the amount of increase:

> db.runCommand({runProcedure: "increase_price", "arguments": [101, 10]})
{"ok":1}

transaction

1  transaction:
2.1 "enable"
2.1 "disable"
2.1 "commit"
2.1 "rollback"
2.1  "execute", "commands": [command_docs]? ,"finally":[command_docs]
2.1  " status "

enable

This optional parameter enables transaction mode for the current session in the current database. The following example shows how to enable transaction mode:

> db.runCommand({transaction: "enable"})
{"ok":1}

disable

This optional parameter disables transaction mode for the current session in the current database. The following example shows how to disable for transaction mode:

> db.c.find()
{"_id":ObjectId("52a8f9c477a0364542887ed4"),"a":1}
> db.runCommand({transaction: "disable"})
{"ok":1}

commit

If transactions are enabled, this optional parameter commits the current transaction. If transactions are disabled, an error is shown. The following example shows how to commit the current transaction:

> db.c.insert({"a": 1})
> db.runCommand({transaction: "commit"})
{"ok":1}

rollback

If transactions are enabled, this optional parameter rolls back the current transaction. If transactions are disabled, an error is shown. The following example shows how to roll back the current transaction:

> db.c.insert({"a": 2})
> db.c.find()
{"_id":ObjectId("52a8f9c477a0364542887ed4"),"a":1}
{"_id":ObjectId("52a8f9e877a0364542887ed5"),"a":2}
> db.runCommand({transaction: "rollback"})
{"ok":1}

execute

This optional parameter runs a batch of commands as a single transaction. If transaction mode is not enabled for the session, this parameter enables transaction mode for the duration of the transaction.

The list of command documents can include insert, update, delete, findAndModify, and find command documents. In insert, update, and delete command documents, you cannot set the ordered property to false. You can use a find command document to run queries, including SQL queries, but not commands. A find command document can include the $orderby, limit, skip, and sort operators. The following example deletes a document from the inventory collection and inserts documents into the archive collection:

> db.runCommand({"transaction" : "execute", 
 "commands" : [
	{"delete": "inventory", "deletes" : [ { "q" : { "_id" : 432432 } } ] },
	{"insert" : "archive", 
	 "documents" : [ { "_id": 432432, "name" : "apollo", "last_status" : 9} ]
        }
 ]
})

Include the optional finally argument if you have a set of command documents to run at the end of the transaction regardless of whether the transaction is successful. The following example runs a query with the . The command document for the finally argument unsets the USE_DWA environment variable regardless of whether the previous query succeeds.

> db.runCommand({"transaction" : "execute", 
 "commands" : [
    {"find" : "system.sql", "filter" : {"$sql" : 
              "SET ENVIRONMENT USE_DWA 'ACCELERATE ON'" } },
    {"find" : "system.sql", "filter" : {"$sql" : 
              "SELECT SUM(s.amount) as sum FROM sales AS s 
              WHERE s.prid = 100 GROUP BY s.zip" } }
 ], 
"finally" : [{"find":"system.sql", "filter" : {"$sql" : 
             "SET ENVIRONMENT USE_DWA 'ACCELERATE OFF'" } } ]
})

status

This optional parameter prints status information to indicate whether transaction mode is enabled, and if transactions are supported by the current database. The following example shows how to print status information:

> db.runCommand({transaction: "status"})
{"enabled": true, "supports": true, "ok": 1}

unlockAccounts

1  unlockAccounts:
2.2.1 1
2.2.2.1 ,db:
2.2.2.2.1 "database_name"
2.2.2.2.1 [+ ,"database_name"]
2.2.2.2.1 {"$regex":"json_document"}
2.2.2.2.1 {
2.2.2.2.2.1+ ,
2.2.2.2.2.1 "include":
2.2.2.2.2.2.1 "database_name"
2.2.2.2.2.2.1 [+ ,"database_name"]
2.2.2.2.2.2.1 {"$regex":"json_document"}
2.2.2.2.2.1 "exclude":
2.2.2.2.2.2.1 "database_name"
2.2.2.2.2.2.1 [+ ,"database_name"]
2.2.2.2.2.2.1 {"$regex":"json_document"}
2.2.2.2.1}
2.2.2.1"
2.2.2.1 ,user:
2.2.2.2.1 "user_name"
2.2.2.2.1 "json_document"