MongoDB V2 Connector > Mappings for MongoDB V2 > MongoDB V2 sources in mappings

MongoDB V2 sources in mappings

To read data from MongoDB, configure a MongoDB V2 object as the Source transformation in a mapping.

Specify the name and description of the MongoDB V2 source. Configure the source, query options, and advanced properties for the source object. When you configure a Source transformation, you can add or remove source fields of primitive data types.

You can parameterize the MongoDB V2 source connection and source object with values specified in a parameter file when you configure a mapping or a mapping in advanced mode.

The following table describes the source properties that you can configure for a MongoDB V2 source:

Property	Description
Connection	Name of the MongoDB V2 source connection. You can select an existing connection or create a new connection. You can also click New Parameter to define a parameter for the source connection.
Source Type	Type of the source object. Select one of the following options: - Single Object - Multiple Objects¹ - Parameter Related objects appear based on simple primary key-foreign key relationships. You can use the Advanced Relationship option to define the relationship for objects that you want to join.
Object	Name of the MongoDB V2 source object based on the source type selected. Select the source object for a single source.
¹ Applies only to mappings in advanced mode.

The following table describes the query options that you can configure in a Source transformation:

Property	Description
Filter	Filter value in a read operation. Click Configure to add conditions to filter records and reduce the number of rows that the Secure Agent reads from the source. You can specify the following filter conditions: - Not parameterized. Use a basic filter to specify the object, field, operator, and value to select specific records. - Completely parameterized. Use a parameter to represent the field mapping. - Advanced. Use an advanced filter to define a more complex filter condition on an object. For more information about the complex filter conditions, see Filter formats. You can configure filter conditions for columns of the following data types: - Date - Double - Float - Integer - String
Select distinct rows only	Not applicable.

Property

Description

Filter

Filter value in a read operation. Click Configure to add conditions to filter records and reduce the number of rows that the Secure Agent reads from the source.

You can specify the following filter conditions:

- Not parameterized. Use a basic filter to specify the object, field, operator, and value to select specific records.
- Completely parameterized. Use a parameter to represent the field mapping.
- Advanced. Use an advanced filter to define a more complex filter condition on an object.

For more information about the complex filter conditions, see Filter formats.

You can configure filter conditions for columns of the following data types:

- Date
- Double
- Float
- Integer
- String

Select distinct rows only

Not applicable.

The following table describes the advanced properties that you can configure in a Source transformation:

Property	Description
Pre Query	Statement that you want to run before reading data from the source. You can use only insert, update, and delete operations. For example, if you want to insert records in the database before you read the records from the table, specify the following preSQL statement: { insert: 'yourCollection', documents: [ { name: 'Alice', age: 25 } ] }
Post Query	Statement that you want to run after reading data from the source. You can use only insert, update, and delete operations. For example, if you want to update records in a table after you read the records from a table, specify the following postSQL statement: { update: 'yourCollection', updates: [ { q: { name: 'Alice' }, u: { $set: { age: 26 } } } ] }
Additional Properties	Additional options that you want to specify to read data from MongoDB. Separate multiple options with an ampersand. For information about the additional properties that you can configure, see the Configure additional properties chapter.
Tracing Level	Amount of detail that appears in the log for this transformation. You can use the following tracing levels: - Terse - Normal - Verbose Initialization - Verbose Default is normal.

Joining multiple objects

When you create a Source transformation to run mappings in advanced mode, you can select multiple MongoDB V2 objects as the source type and then configure an advanced relationship to combine the tables.

Perform the following steps to join multiple objects in a Source transformation:

1In the Source transformation properties, select the Source Type as Multiple Objects.

2From the Actions menu, select Add Source Object, and then select the source object that you want to add from the displayed list.

3From the Related Objects Actions menu, select the Advanced Relationship option to define the relationship between the tables.

The following image shows an advanced relationship condition defined between MongoDB tables:

Note: You can join only two source tables. You must define only the join condition, not the complete join query.

Adding and removing source fields

You can configure the source fields that you want to use in the data flow.

You can add fields to a mapping source and retrieve a field from the source object that is not displayed in the list. To add a field, click This image allows you to add fields to a mapping source.

This image allows you to add fields to a mapping source.

, and then enter the field name, type, precision, and scale. You can also remove fields that you do not want to use in the mapping. To remove a field, select the field and then click This image allows you to remove fields from the source object.

This image allows you to remove fields from the source object.

This image displays the options to add and remove source fields.

Filter formats

Consider the following filter formats for the advanced filter option:

•Equals - { "field": "value" }
•Not equal - { "field": { "$ne": "value" } }
•Greater than - { "field": { "$gt": "value" } }
•Greater than or equal - { "field": { "$gte": "value" } }
•Less than - { "field": { "$lt": "value" } }
•Less than or equal - { "field": { "$lte": "value" } }
•Combines filters with logical AND - { "$and": [{ "field1": { "$gt": "value1" } }, { "field2": { "$lt": "value2" } }] }
•Combines filters with logical OR - { "$or": [{ "field1": "value1" }, { "field2": "value2" }] }Inverts a filter - { "field": { "$not": { "$eq": "value" } } }
•Matches documents with the specified BSON type - { "field": { "$type": "string" } } / { "field": { "$type": "int" } }
•Matches if field’s value is in the provided array - { "field": { "$in": ["value1", "value2", "value3"] } }
•Matches if field’s value is not in the provided array - { "field": { "$nin": ["value1", "value2", "value3"] } }
•Regular expression matching - { "field": { "$regex": "pattern" } } / { "field": { "$regex": "pattern", "$options": "i" } }

Key range partitioning

You can configure key range partitioning when you use mapping tasks to read data from MongoDB V2 sources. With key range partitioning, the Secure Agent distributes rows of source data based on the fields that you define as partition keys. The Secure Agent compares the field value to the range values for each partition and sends rows to the appropriate partitions.

Use key range partitioning for columns that have an even distribution of data values. Otherwise, the partitions might have unequal size. For example, a column might have 10 rows between key values 1 and 1000 and the column might have 999 rows between key values 1001 and 2000. If the mapping includes multiple sources, use the same number of key ranges for each source.

When you define key range partitioning for a column, the Secure Agent reads the rows that are within the specified partition range. For example, if you configure two partitions for a column with the ranges as 10 through 20 and 30 through 40, the Secure Agent does not read the rows 20 through 30 because these rows are not within the specified partition range.

You can configure a partition key for fields of the following data types:

•Date
•Decimal128
•Double
•Int32
•Int64
•Integer
•String

You can't use key range partitioning for mapping tasks that are based on mappings in advanced mode.

For more information about partitions, see Transformations.