MongoDB Execute
The MongoDB Execute step connects to a MongoDB cluster and executes Mongo shell-style commands. The commands can:
- Be statically defined.
- Have wildcards substituted with data from incoming rows.
- Be defined in a field on incoming rows.
- SCRAM-SHA-1/SCRAM-SHA-256
- LDAP
- Kerberos
- X509
General
Enter the following information in the transformation step name field:
- Step name: Specify the unique name of the MongoDB Execute step on the canvas. You can customize the name or leave it as the default.
Options
The MongoDB Execute step features two tabs with fields. Each tab is descibed below.
Main tab
Use this tab to specify the connection string and command execution properties.
ConnectionTo specify your connection, use the following:
| Option | Description |
| String | Enter a MongoDBconnection string URI. Both mongodb:// and mongodb+srv:// schemes are supported. his option supports variable substitution, and it can contain the output of the Encr.bat/encr.sh command. For more information on URI construction, please see MongoDB's Connection String URI Format information. |
| Test and Get DBs | Click to test the connection string and get the databases you are authorized to access. |
| Database | Select from the list of databases on which you want ot perform the commands. |
Command(s) source
There are a few options to run commands.
Select the Script option when you want to run static commands.
| Option | Description |
| Script |
Enter the commands you want to execute in the text box. You can use multiple commands by separating them with semi-colons. |
| Execute for every row of input | Select this option to execute the command for each incoming row for the step. When selected, a question mark(?) wild card field substitution can be used to modify the commands that will be invoked with row data. |
Select the Field option when the run commands are specified on an incoming field.
| Option | Description |
| Name | The name of an incoming field that contains one or more commands to execute. |
| Perform ? value substition | Select this option to perform a field name substitution before the step runs. |
Step tab
You use this tab to specify step behavior.
| Option | Description |
| Result field name | Enter a value to add a String field to the step's output stream containing output information for each command executed. If more than one command is specified for execution, there will be a row output with resulting information for each command. |
| Command field name | Enter a value to add a String field to the step's output stream containing command information for each retrieved row. This field identifies the structure of each row’s results when there are multiple commands executed in the script and the subsequent step parses the information. Leave the field name empty to omit this field in the output stream, . |
| Stop on error | Select this option to write to the log and stop the transformation when an error occurs. If if you do not select this option, errors are reported to the step's error-handling stream. |
Execute commands
The commands are divided into two sub-types. Those that operate directly on the selected database, and those that operate on a collection of the selected database. For more information, see:
Generally the commands follow the structure of commands in the MongoDB shell. However, there are some cases where extra input and output options are limited. Commands may accept arguments of- STRING (double or single qoutes)
- BSON (less restrictive JSON)
- [ BSON, ...] (a collection of BSON).
Database commands
| Command | Description | Output |
db.createCollection( STRING collectionName ) | Creates a collection with the specified name. Strings are surrounded by double qoutes. For example: db.createCollection("identities"); | The String = "true" when the database is created. If the command fails, an error is raised. |
db.listCollectionNames( ) | Lists the names of the collections in the selected database. | An output row for each collection in the database with the result field containing the String name of the collection. |
db.listCollections( ) | Lists collection details of all collections in the selected database. | An output row for each collection in the database with the result field containing a BSON document with collection details. |
db.runCommand( BSON command ) | Runs a command on the selected database. See db.runCommand for details on a similar shell command. For example: db.runCommand( { find : "identities", projection : { superhero : 1, _id : 0 } } ); | An output row containing with the result field containing a BSON document of command results. |
Collection commands
| Command | Description | Output |
db.collection.aggregate( [BSON, ...] pipelineStages ) | Runs an aggregation pipeline, which is capable of advanced aggregation operations such as sorts, distincts, groupings. For example: db.identities.aggregate( [ { $sort : { superhero : -1 } } ] ); See aggregation stages for details. | An output row for each BSON document that results from the aggregate command. |
db.collection.countDocuments( ) | Counts the documents in a collection. For example: db.identities.countDocuments(); See db.collection.countDocuments for details on a similar shell command. | An output row with the result field containing the count of all documents in the collection. |
db.collection.countDocuments( BSON query ) | Counts the documents in a collection. For example: db.identities.countDocuments( { superhero : { $gt : "S" } } ); See db.collection.countDocuments for details on query formation from a similar shell command. | An output row with the result field containing the count of all documents that matched the query in the collection. |
db.collection.countDocuments( BSON query, BSON options ) | Counts the documents in a collection. Option: For example: | An output row with the result field containing the count of all documents that matched the query in the collection while respecting the options provided. |
db.collection.deleteMany( BSON filter ) | Deletes one or more documents in the collection matching the filter. For example: db.identities.deleteMany( { superhero : { $gt : "Z" } } ); See db.collection.deleteMany for filter creation details from a similar shell command. | An output row with the result field containing a BSON document with the deletedCount and a Boolean value indicating the command was acknowledged. |
db.collection.deleteOne( BSON filter ) | Deletes at most one document in the collection matching the filter. For example: db.identities.deleteOne( { superhero : { $gt : "D" } } ); See db.collection.deleteOne for filter creation details from a similar shell command. | An output row with the result field containing a BSON document with the deletedCount and a Boolean indicating the command was acknowledged. |
db.collection.drop( ) | Drops a collection. For example: db.createCollection( "aliases" ); db.aliases.drop(); | The String = "true" when the collection is dropped/removed. . |
db.collection.find( ) | Finds the documents in a collection. For example: db.identities.find(); See db.collection.find for details on a similar shell command. | An output row for every document found in the collection with the result field containing a BSON representation of the document. |
db.collection.find( BSON query ) | Finds the documents in a collection matching the provided query. For example: db.identities.find( { superhero : { $gt : "S" } } ); See db.collection.find for details on query formation from a similar shell command. | An output row for every document found in the collection with the result field containing a BSON representation of the document. |
db.collection.insertMany( [BSON, ...] documents ) | Insert documents into the collection. For example: db.identities.insertMany( [ { superhero : "CodeMan" }, { superhero : "ETL Pro" } ] ); See db.collection.insertMany for details on a similar shell command. | An output row with the result field containing a BSON document with an insertedIds array and a Boolean indicating the command was acknowledged. |
db.collection.insertMany( [BSON, ...] documents, BSON options ) | Insert documents into the collection with options. Option: { ordered: BOOLEAN } For example: | An output row with the result field containing a BSON document with an insertedIds array and a Boolean indicating the command was acknowledged. |
db.collection.insertOne( BSON document ) | Inserts a document into the collection.For example: db.identities.insertOne( { superhero : "CodeMan" } ); See db.collection.insertOne for details on a similar shell command. | An output row with the result field containing a BSON document with the insertedId and a Boolean indicating the command was acknowledged. |
db.collection.mapReduce( STRING map, STRING reduce ) | Performs a mapReduce on the collection using the given map and reduce functions provided as Strings (surrounded by quotes). For example: db.identities.mapReduce( "function() { emit( this.superhero, 1 ); }", "function( key, values ) { return values.length; }" ); See db.collection.mapReduce for details on a similar shell command. | A set of output rows with the result field containing a BSON document per row of mapReduce results. |
db.collection.updateMany( BSON filter, BSON update ) | Updates documents matching the filter in the collection according to updates specified in the update parameter. For example: db.identities.updateMany( { superhero : "CodeMan" }, { $set : { secret_identity: "John Doe" } } ); See db.collection.updateMany for details on constructing the filter and update parameters from a similar shell command. | An output row with the result field containing a BSON document with the matchedCount, modifiedCount, and a Boolean indicating the command was acknowledged. |
db.collection.updateMany( BSON filter, BSON update, BSON options ) | Updates documents matching the filter in the collection according to updates specified in the update parameter adhering to the specified options. Option: For example: | An output row with the result field containing a BSON document with the matchedCount, modifiedCount, the upsertedId if using the upsert option, and a Boolean indicating the command was acknowledged. |
db.collection.updateOne( BSON filter, BSON update ) | Updates at most one top document matching the filter in the collection according to updates specified in the update parameter. For example: db.identities.updateOne( { superhero : "CodeMan" }, { $set : { secret_identity: "John Doe" } } ); See db.collection.updateOne for details on constructing the filter and update parameters from a similar shell command. | An output row with the result field containing a BSON document with the matchedCount, modifiedCount, and a Boolean indicating the command was acknowledged. |
db.collection.updateOne( BSON filter, BSON update, BSON options ) | Updates at most one top document matching the filter in the collection according to updates specified in the update parameter adhering to the specified options. Option: For example: | An output row with the result field containing a BSON document with the matchedCount, modifiedCount, the upsertedId if using the upsert option, and a Boolean indicating the command was acknowledged. |
Example of Execute step
This example demonstrates the use of the Execute for every row of input option.
There is an incoming row with a String field called "first_name" and an Integer field called "age":
| first_name | age |
| Roger | 26 |
| Peter | 53 |
If the
db.people.insertOne( { first: "?{first_name}", age: ?{age} } );
command is in the Script area, once the transformation is executed and the two rows are input to the step, the following two commands will be evaluated with the wild card substitution and executed:
db.people.insertOne( { first: "Roger", age: 26 } );
db.people.insertOne( { first: "Peter", age: 53 } );
Metadata injection support
All fields of this step support metadata injection. You can use this step with ETL metadata injection to pass metadata to your transformation at runtime.