How to call MongoDB aggregate pipeline in SSIS

SSIS PowerPack MongoDB
1

Introduction

This article demonstrates how to migrate pipelines in ZappySys MongoDB components.

Prerequisites

  • SSIS PowerPack: Download and install the ZappySys SSIS PowerPack.

  • Get MongoDB installed on your machine

Concepts

MongoDB Aggregation Pipeline Syntax in SSIS

The SSIS MongoDB Source supports all pipeline commands for aggregation queries. However, its syntax differs slightly from shell commands, so your Shell Query won’t work directly. You can copy some parts of your Shell query; check the syntax below.

MongoDB Native Query Syntax (Shell Command) for Aggregation:

db.YourCollectionName.aggregate(
[
[[ <pipeline-stage1>]]
[[,<pipeline-stage2>]]
...
...
[[,<pipeline-stageN>]]
] )

SSIS MongoDB Source Query Syntax for Aggregation:

{Table:YourTableName ,Method:aggregate [,MaxRows: N] [,AllowDiskUse:true] [,AllowCursor:true] [,Timeout:#####] }
{
pipeline: [
[[ <pipeline-stage1>]]
[[,<pipeline-stage2>]]
...
...
[[,<pipeline-stageN>]]
] }

Example Aggregation Queries (SQL to SSIS Mapping)

Let’s consider translating the following SQL Query to a JSON Query for use in SSIS MongoDB Source or MongoDB Shell:

SELECT State as StateAbbr,SUM(population) as TotalPopulation 
FROM ZipCodes 
WHERE status='Active' 
HAVING SUM(population) &gt; 25
Order By TotalPopulation DESC

The corresponding JSON Query for SSIS MongoDB Source is as follows.

{Table: ZipCodes,Method: aggregate}
{
  pipeline: 
  [
    {$match : { status: "A" } } ,
    {$group : {_id :  "$state", TotalPopulation: { $sum: "$pop" }}},
    {$match: { TotalPopulation: { $gt: 25 } } },
    {$project : { StateAbbr: "$_id" , _id: 0, TotalPopulation:1 } },
    {$sort : {TotalPopulation : -1}}
  ]
}

The translation involves representing various SELECT query clauses as pipeline stages. The order of these stages is crucial as they are applied in that sequence.

You can check in our component more examples here:

MongoDb source native query examples
MongoDb source native query examples1230×701 56.1 KB

SQL to MongoDB Aggregation Mapping

SQL Clause Mongo Pipeline Stage
SELECT $project
WHERE $match
GROUP BY $group
HAVING $match
ORDER BY $sort

Conclusion

This article guides migrating MongoDB pipelines in ZappySys components. For any issues or further assistance, contact our support team via chat on the ZappySys website or through email at support@zappysys.com.

References

For further troubleshooting, refer to these articles: