MongoDBRead
MongoDBRead performs read operations on a Mongo database.
Mongo is a NoSql database, which provides a mechanism for storage and retrieval of data that is different to the tabular relations used in relational databases like SQL Server, MySql and Oracle.
Properties
For large datasets select 'Row by row' for the Return options property.
Select the Operation first, then select the Return option - these selections will determine which other properties will be available to complete.
Collection
The collection to read from.
Connection string
A connection string to your database. learn more
Operation
Options:
Find: Selects documents in a collection.
Aggregate: Calculates aggregate values for the data in a collection.
Query
A MongoDb query document. learn more
For example, if we select from a collection of documents using the attributes "quantity" and "price", the criteria will return all documents in the collection with fields "quantity" greater than 100 or "price" less than 9.95:
Fields
Specifies which fields to return.
Format is, as in the example above:
field = the field name
value = 1 to include and 0 to exclude
The parameter contains either include or exclude specifications, not both, unless the exclude is for the _id field.
Sort
Specifies the order in which the query returns matching documents.
Format is, as in the example above:
field = the field name
value = 1 to sort ascending and -1 to sort descending
Skip
An integer indicating how many documents to skip before returning results.
Limit
An integer indicating the maximum number of documents to return.
Pipelines
Only available when Operation is 'Aggregate'.
The aggregation pipelines should be specified as a JSON formatted array of aggregation objects.
If we have a collection of "People" documents with attributes "age" and "name", as in the example below, the aggregation pipeline will sort the documents in the collection by age, and group the results into a single document with attributes "total" (containing the total number of records in the collection) and "eldest" (the "name" attribute of the document with the greatest "age").:
Pipelines can make use of operators, like $sum and $first in the example above. learn more
Click the ... icon to open the MongoDB Pipelines Editor that can be used to construct your aggregation pipeline.
Result type
The Type that will be used to store the results from the database. The names of the fields in the custom type should match the field names of the documents returned from MongoDb.
The Type can optionally include a string field named "id" which will be populated by the document's unique id attribute.
Return options
First row
The function will return the first document returned by the query. If no data is returned by the query, an error will be reported.First row, else empty row
The function will return the first document returned by the query. If no data is returned by the query, the function will return a document containing default values.List of rows
The function will return all documents in one list. The list can then be used later in the process without having to execute the query again.Row by row
The function will automatically return one document at a time. You will see a "ForEachRow" loop icon as a child of this function. Any function you attach to the results will be inside of the loop. This is recommended whenever you expect to retrieve multiple items, but you don't need the complete list of items all at once.
What are ID Attributes?
Each document in a Mongo collection is uniquely identified by an Object ID field called "_id". When reading from the database, it is possible to retrieve this id by adding a string field named "id" or "_id" to the Type specified in the result type property.
When using the object id as an element in the Query property, the string value will need to be converted back into a mongo object id. For example, a document with id "525bc19331eec126ecdcf199" can be retrieved using the query: