Informix JSON commands
The JSON commands are available in addition to the supported MongoDB commands. These commands enable functionality that is supported by and they are run by using the MongoDB API.
The syntax for using
commands in the MongoDB shell is:
db.runCommand({command_document})
The
command_document contains the command and any parameters.createTextIndex
Create bts indexes.
Important: If you create text indexes by using the createTextIndex
command, you must query them by using the $ifxtext
query operator. If you create text indexes by using the MongoDB syntax
for text indexes, you must query them by using the MongoDB $text
query operator.
>>-createTextIndex:"collection_name",--name:"indexName"---------> >--+----------------------------+-------------------------------> | .-,------------. | | V | | '-, key:{---"--column--"-+-}-' >--, options:{-+------------------------------+-}-------------->< | (1) | '-| btx index parameters |-----'
Notes:
- 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.
>>-exportCollection:"collection_name",--file:"filepath",--------> .-json-. >--format:-+-+-"-+------+-"-+--+--------------------------------------+-+--> | '-"jsonArray"--' | .-,------. | | | | V | | | | '-,--fields--:--{--"----filter-+--"--}-' | | .-,------. | | V | | '-"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:
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.> db.runCommand({exportCollection:"c",file:"/tmp/export.out" ,format:"json"}) { "ok":1, "n":1000, "millis":NumberLong(119), "rate":8403.361344537816 }
- 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:
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.> db.runCommand({exportCollection:"c",file:"/tmp/export.out" , format:"jsonArray"}) { "ok":1, "n":1000, "millis":NumberLong(81), "rate":12345.67901234568 }
- 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:
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.> db.runCommand({exportCollection:"c",file:"/tmp/export.out" ,format:"csv",fields:{"_id":1 ,"name":"1"}}) { "ok":1, "n":1000, "millis":NumberLong(57), "rate":17543.859649122805 }
- 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}}}) {"ok":1,"n":100,"millis":NumberLong(5),"rate":20000}
importCollection
Import JSON collections from the wire listener to a file.
>>-importCollection:"collection_name",--file:"filepath",--------> .-json------. >--format:"-+-jsonArray-+-"------------------------------------>< '-csv-------'
- 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.
killCursors
Close native cursors that were created with the &nativeCursor query modifier in a REST API query.
.----------. V | >>-killCursors:1--cursorIds:[---cursorID-+-]-------------------><
- killCursors
- This required parameter closes native cursors.
- cursorIds
- This required parameter lists the native cursor IDs to close.
The following command closes the cursor with the ID of 22:
GET /dbname/$cmd?query={killCursors:1, cursorsIds:[22]}
lockAccounts
Lock
a database or user account.
Important:
- To run this command, you must be the instance administrator.
- If you specify the lockAccounts:1 command without specifying a db or user argument, all accounts in all databases are locked.
>>-lockAccounts:----1-+------------------------------------------------------------------+--->< +-,db:-+-"database_name"---------------------------------------+-"-+ | | .-,---------------. | | | | V | | | | +-[---"database_name"-+-]-------------------------------+ | | +-{"$regex":"json_document"}----------------------------+ | | | .-,---------------------------------------------. | | | | V | | | | '-{---+-"include":-+-"database_name"------------+-+-+-}-' | | | | .-,---------------. | | | | | | V | | | | | | +-[---"database_name"-+-]----+ | | | | '-{"$regex":"json_document"}-' | | | '-"exclude":-+-"database_name"------------+-' | | | .-,---------------. | | | | V | | | | +-[---"database_name"-+-]----+ | | '-{"$regex":"json_document"}-' | '-,user:-+-"user_name"-----+---------------------------------------' '-"json_document"-'
- lockAccounts:1
- This required parameter locks a database or user account.
- db
- This optional parameter specifies the database name of an account to lock. For example, to lock all accounts in database that is named foo:
> db.runCommand({lockAccounts:1,db:"foo"})
- exclude
- This optional parameter specifies the databases to exclude. For example, to lock all accounts on
the system except the accounts that are in the databases named alpha and
beta:
> db.runCommand({lockAccounts:1,db:{"exclude":["alpha","beta"]})
- include
- This optional parameter specifies the databases to include. For
example, to lock all accounts in the databases named delta and gamma:
> db.runCommand({lockAccounts:1,db:{"include":["delta","gamma"]})
- $regex
- This optional MongoDB evaluation
query operator selects values from a specified JSON document. For
example, to lock accounts for databases that begin with the character a. and
end in e:
> db.runCommand({lockAccounts:1,db:{"$regex":"a.*e"})
- user
- This optional parameter specifies the user accounts to lock. For
example, to lock the account of all users that are not named alice:
> db.runCommand({lockAccounts:1,user:{$ne:"alice"}});
runFunction
Run an SQL function through the wire listener. This command is equivalent to the SQL statement EXECUTE FUNCTION.
>>-runFunction:"function_name"----------------------------------> >--+-------------------------------+--------------------------->< | .-,--------. | | V | | '-,"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:
The following example returns multiple values from a func_return3 function:> db.runCommand({runFunction:"add_values", "arguments":[3,6]}) {"returnValue": 9, "ok":1}
> 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.
>>-runProcedure:"procedure_name"--------------------------------> >--+-------------------------------+--------------------------->< | .-,--------. | | V | | '-,"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
Enable or disable transaction support for a session, run a batch transaction, or, when
transaction support is enabled, commit or rollback transactions. This command binds or unbinds a
connection to the current MongoDB session in
a database. The relationship between a MongoDB session and the JDBC connection
is not static.
Important: This command is not supported for queries that are run on
shard servers.
>>-transaction:--+-"enable"---------------------------------------------------------------+->< +-"disable"--------------------------------------------------------------+ +-"commit"---------------------------------------------------------------+ +-"rollback"-------------------------------------------------------------+ +-"execute",--"commands":--[command_docs]--+---------------------------+-+ | '-,"finally":[command_docs]-' | '-"--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 Informix®
Warehouse Accelerator. 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
Unlock
a database or user account.
Important:
- To run this command, you must be the instance administrator.
- If you specify the unlockAccounts:1 command without specifying a db or user argument, all accounts in all databases are unlocked.
>>-unlockAccounts:------1-+------------------------------------------------------------------+----->< +-,db:-+-"database_name"---------------------------------------+-"-+ | | .-,---------------. | | | | V | | | | +-[---"database_name"-+-]-------------------------------+ | | +-{"$regex":"json_document"}----------------------------+ | | | .-,---------------------------------------------. | | | | V | | | | '-{---+-"include":-+-"database_name"------------+-+-+-}-' | | | | .-,---------------. | | | | | | V | | | | | | +-[---"database_name"-+-]----+ | | | | '-{"$regex":"json_document"}-' | | | '-"exclude":-+-"database_name"------------+-' | | | .-,---------------. | | | | V | | | | +-[---"database_name"-+-]----+ | | '-{"$regex":"json_document"}-' | '-,user:-+-"user_name"-----+---------------------------------------' '-"json_document"-'
- unlockAccounts:1
- This required parameter unlocks a database or user account.
- db
- This optional parameter specifies the database name of an account
to unlock. For example, to unlock all accounts in database that is
named foo:
> db.runCommand({unlockAccounts:1,db:"foo"})
- exclude
- This optional parameter specifies the databases to exclude. For example, to unlock all accounts
on the system except the accounts that are in the databases named alpha and
beta:
> db.runCommand({unlockAccounts:1,db:{"exclude":["alpha","beta"]})
- include
- This optional parameter specifies the databases to include. For
example, to unlock all accounts in the databases named delta and gamma:
> db.runCommand({unlockAccounts:1,db:{"include":["delta","gamma"]})
- $regex
- This optional MongoDB evaluation
query operator selects values from a specified JSON document. For
example, to unlock accounts for databases that begin with the character a. and
end in e:
> db.runCommand({unlockAccounts:1,db:{"$regex":"a.*e"})
- user
- This optional parameter specifies the user accounts to unlock.
For example, to unlock the account of all users that are not named alice:
> db.runCommand({unlockAccounts:1,user:{$ne:"alice"}});