{"id":736,"date":"2016-08-17T15:52:17","date_gmt":"2016-08-17T15:52:17","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=736"},"modified":"2024-07-10T21:06:01","modified_gmt":"2024-07-10T21:06:01","slug":"aggregate-mongodb-data-using-group-by-query-in-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/aggregate-mongodb-data-using-group-by-query-in-ssis\/","title":{"rendered":"How to write MongoDB Aggregation Queries in SSIS (Group By)"},"content":{"rendered":"

Introduction<\/h2>\n

\"\"In this post you will learn how to write flexible MongoDB Aggregation Queries in SSIS<\/strong> (i.e. Group By Query) using SSIS MongoDB Source Component<\/a>. MongoDB provides powerful Aggregation Pipeline Engine which is conceptually same as writing Group By queries in traditional SQL world but its not exactly same.\u00a0SSIS MongoDB Source<\/a> returns nested MongoDB JSON documents into flat looking table with rows and column (denormalized data).<\/p>\n

If you want to learn how to write data to MongoDB (Insert, Update, Delete, Upsert) then click here<\/a>. If you want learn how to read data from MongoDB then click here<\/a>.<\/p>\n

So let’s explore concepts of\u00a0MongoDB Aggregation Pipeline<\/a> and see some MongoDB query examples for SSIS use case.<\/p>\n

Prerequisites<\/span><\/h2>\r\nBefore we perform the steps listed in this article, you will need to make sure the following prerequisites are met:\r\n
    \r\n \t
  1. SSIS<\/abbr> designer installed.\u00a0Sometimes it is referred to as BIDS<\/abbr> or SSDT<\/abbr> (download it from the Microsoft site<\/a>).<\/li>\r\n \t
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\r\n \t
  3. Make sure\u00a0ZappySys SSIS PowerPack<\/a><\/span>\u00a0is installed (download it<\/a>, if you haven't already).<\/li>\r\n \t
  4. (Optional step<\/em>).<\/em>\u00a0Read this article<\/a>, if you are planning to deploy packages to a server and schedule their execution later.<\/li>\r\n<\/ol><\/div>\n

    SSIS MongoDB Source Introduction<\/h2>\n

    SSIS MongoDB Source<\/a>\u00a0supports three modes to read data from MongoDB collection. Each mode also supports JSONPath Expression to extract nested array<\/a>.<\/p>\n

      \n
    1. Table Mode<\/li>\n
    2. SQL Query Mode<\/li>\n
    3. JSON Query Mode (Native)<\/li>\n<\/ol>\n

      To read more about supported SQL Syntax read help file here<\/a>.<\/p>\n

      Using SSIS MongoDB Source to Query Data (Aggregation)<\/h2>\n

      Now lets look at how to use SSIS MongoDB Source<\/a>\u00a0to query MongoDB data using Aggregation Engine.<\/p>\n

      MongoDB Aggregation Pipeline Syntax in SSIS<\/h3>\n

      SSIS MongoDB Source<\/a>\u00a0support all pipeline commands to write aggregation queries but its slighly different syntax than shell command so your SHell Query won’t work directly but you can copy some part of your Shell query. Check below Syntax.<\/p>\n

      MongoDB Native Query Syntax (Shell Command) for Aggregation:<\/strong><\/p>\n

      db.YourCollectionName.aggregate(\r\n[\r\n[ <pipeline-stage1>]\r\n[,<pipeline-stage2>]\r\n...\r\n...\r\n[,<pipeline-stageN>]\r\n]\r\n)<\/pre>\n
      SSIS MongoDB Source Query Syntax for Aggregation:<\/strong>
      \n<\/code><\/p>\n
      {Table:YourTableName ,Method:aggregate [,MaxRows: N] [,AllowDiskUse:true] [,AllowCursor:true] [,Timeout:#####] }\r\n{\r\npipeline: [\r\n[ <pipeline-stage1>]\r\n[,<pipeline-stage2>]\r\n...\r\n...\r\n[,<pipeline-stageN>]\r\n]\r\n}<\/pre>\n
       <\/p>\n
      Example Aggregation Queries (SQL to SSIS Mapping)<\/h2>\n
      Assume that we want to write following SQL Query using SSIS MongoDB Source<\/a>\u00a0or using MongoDB Shell<\/p>\n
      SELECT State as StateAbbr,SUM(population) as TotalPopulation \r\nFROM ZipCodes \r\nWHERE status='Active' \r\nHAVING SUM(population) > 25\r\nOrder By TotalPopulation DESC<\/pre>\n
      Above SQL Query can be translated to below JSON Query which can be used in SSIS MongoDB Source. As we noted before Below syntax is not same as Shell command so it will only run in SSIS MongoDB Source.<\/p>\n
      {Table: ZipCodes,Method: aggregate}\r\n{\r\n  pipeline: \r\n  [\r\n    {$match : { status: \"A\" } } ,\r\n    {$group : {_id :  \"$state\", TotalPopulation: { $sum: \"$pop\" }}},\r\n    {$match: { TotalPopulation: { $gt: 25 } } },\r\n    {$project : { StateAbbr: \"$_id\" , _id: 0, TotalPopulation:1 } },\r\n    {$sort : {TotalPopulation : -1}}\r\n  ]\r\n}<\/pre>\n
      So as you saw in above query various clause of SELECT queries are defined as pipeline stages. You can add any possible stages in different order. Based on their order it will be applied so order of stage is very important.\u00a0 For more information about various pipeline stages click here<\/a>. Below is high level translation of SQL Query to MongoDB Aggregation Query.<\/p>\n
      \"SSIS<\/a>
      SSIS MongoDB Source – Example of MongoDB Group By Query (Aggregation Framework – Pipeline)<\/p><\/div>\n
      SQL to MongoDB Aggregation Mapping<\/strong><\/p>\n
      \n\n\n\n\n\n\n\n\n\n
      SQL Clause<\/th>\nMongo Pipeline Stage<\/th>\n<\/tr>\n<\/thead>\n
      SELECT<\/td>\n$project<\/a><\/td>\n<\/tr>\n
      WHERE<\/td>\n$match<\/a><\/td>\n<\/tr>\n
      GROUP BY<\/td>\n$group<\/a><\/td>\n<\/tr>\n
      HAVING<\/td>\n$match<\/a><\/td>\n<\/tr>\n
      ORDER BY<\/td>\n$sort<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
      Now let’s look at few examples how to translate traditional SQL\u00a0 Queries to JSON Query for SSIS MongoDB Source. We will use sample data we inserted in previous section.<\/p>\n
      Creating Sample Data for MongoDB Query Examples<\/h3>\n
      For this exercise to write sample queries using below data. So let’s first create a collection called Order and insert following records using your favourite MongoDB client tool.<\/p>\n
      db.Orders.insert(\r\n  {\r\n    \"OrderId\" : 1000,\r\n    \"CustomerId\" : \"CUS001\",\r\n    \"OrderTotal\" : 2000.50,\r\n    \"Quantity\" : 20,\r\n    \"OrderDate\" : ISODate(\"2010-01-01\"),\r\n    \"Country\" : \"USA\",\r\n    \"Items\" : [ {ItemId:10, UnitPrice: 100.00,Quantity: 10}, {ItemId:11, UnitPrice: 200.10,Quantity: 5}],\r\n    \"PaymentInfo\" : { \r\n      \"Method\" : \"Card\",\r\n      \"Currency\" : \"USD\" \r\n    }\r\n  }\r\n)\r\n\r\ndb.Orders.insert(\r\n  {\r\n    \"OrderId\" : 1001,\r\n    \"CustomerId\" : \"CUS001\",\r\n    \"OrderTotal\" : 1000.00,\r\n    \"Quantity\" : 10,\r\n    \"OrderDate\" : ISODate(\"2011-01-02\"),\r\n    \"Country\" : \"USA\",\r\n    \"Items\" : [ {ItemId:10, UnitPrice: 100.00,Quantity: 10}],\r\n    \"PaymentInfo\" : { \r\n      \"Method\" : \"Card\",\r\n      \"Currency\" : \"USD\" \r\n    }\r\n  }\r\n)\r\n\r\ndb.Orders.insert(\r\n  {\r\n    \"OrderId\" : 1002,\r\n    \"CustomerId\" : \"CUS002\",\r\n    \"OrderTotal\" : 1000.00,\r\n    \"Quantity\" : 5,\r\n    \"OrderDate\" : ISODate(\"2012-01-01\"),\r\n    \"Country\" : \"USA\",\r\n    \"Items\" : [ {ItemId:13, UnitPrice: 200.00,Quantity: 5}],\r\n    \"PaymentInfo\" : { \r\n      \"Method\" : \"Bank\",\r\n      \"Currency\" : \"USD\" \r\n    }\r\n  }\r\n)\r\n\r\ndb.Orders.insert(\r\n  {\r\n    \"OrderId\" : 1003,\r\n    \"CustomerId\" : \"CUS003\",\r\n    \"OrderTotal\" : 59000.00,\r\n    \"Quantity\" : 10,\r\n    \"OrderDate\" : ISODate(\"2005-01-02\"),\r\n    \"Country\" : \"India\",\r\n    \"Items\" : [ {ItemId:14, UnitPrice: 5900.00,Quantity: 10}],\r\n    \"PaymentInfo\" : { \r\n      \"Method\" : \"Check\",\r\n      \"Currency\" : \"INR\" \r\n    }\r\n  }\r\n)<\/pre>\n
      Here is how it would look like (We used RoboMongo<\/strong><\/a> i.e. Robo 3T<\/strong>) but you can use mongo.exe command line to issue above commands.<\/p>\n
      \"Inserting<\/a>
      Inserting Example data for MongoDB Aggregation Queries (RoboMongo \/ Robo 3T)<\/p><\/div>\n
      Example 1 –\u00a0MongoDB Aggregation Query with COUNT(*) – $group, $project<\/h3>\n
      Here is how to write COUNT(*) query in MongoDB. Below query will find order count by country. Each query must output _id field and you can refer Document column using $ prefix (i.e. “$Country<\/strong>” or for nested object use dot separator “$PaymentInfo.Method<\/strong>” ) . To output _id column with different name we have used\u00a0Country: “$_id”. Because we including copy of _id we can remove _id column by doing\u00a0 _id: 0.<\/strong>\u00a0 To include column in output use YourColumanName: 1<\/strong>\u00a0in $project stage.<\/p>\n
      SQL Query:<\/strong><\/p>\n
      SELECT Country,Count(*) as OrderCount \r\nFROM Orders\r\nGROUP BY Country<\/pre>\n
      Mongo Shell Query:<\/strong><\/p>\n
      db.Orders.aggregate(\r\n [\r\n   {$group : {_id : \"$Country\",OrderCount: { $sum: 1 }}} \r\n  ,{$project : {Country: \"$_id\", _id: 0, OrderCount: 1 }}\r\n ]\r\n)<\/pre>\n
      SSIS MongoDB Source Query:<\/strong><\/p>\n
      {Table: Orders,Method: aggregate} \r\n{ pipeline: \r\n   [\r\n    {$group : {_id : \"$Country\",OrderCount: { $sum: 1 }}} \r\n   ,{$project : {Country: \"$_id\", _id: 0, OrderCount: 1 }}\r\n  ]\r\n}<\/pre>\n
      Example 2 –\u00a0MongoDB Aggregation Query with WHERE and ORDER BY- $match, $group, $project, $sort<\/h3>\n
      Here is how to write COUNT(*) query in MongoDB. Below query will find order count by country.<\/p>\n
      SQL Query:<\/strong><\/p>\n
      SELECT Country,Count(*) as OrderCount \r\nWHERE Quantity > 1\r\nFROM Orders\r\nGROUP BY Country\r\nORDER BY Country<\/pre>\n
      Mongo Shell Query:<\/strong><\/p>\n
      db.Orders.aggregate(\r\n [\r\n   {$match : {Quantity : { $gt: 1 }}} \r\n  ,{$group : {_id : \"$Country\",OrderCount: { $sum: 1 }}} \r\n  ,{$project : {Country: \"$_id\", _id: 0, OrderCount: 1 }}\r\n  ,{$sort : {Country: 1}}\r\n ]\r\n)<\/pre>\n
      SSIS MongoDB Source Query:<\/strong><\/p>\n
      {Table: Orders,Method: aggregate} \r\n{ pipeline: \r\n   [\r\n     {$match : {Quantity : { $gt: 1 }}} \r\n    ,{$group : {_id : \"$Country\",OrderCount: { $sum: 1 }}} \r\n    ,{$project : {Country: \"$_id\", _id: 0, OrderCount: 1 }} ,{$sort : {Country: 1}}  \r\n   ]\r\n}<\/pre>\n
      Example 3 –\u00a0MongoDB Aggregation Query with WHERE and HAVING Clause – $match, $group<\/h3>\n
      Here is an example of WHERE and HAVING clause. They both use $match stage but their orders are different. WHERE condition is applied before GROUP BY (i.e. $group) and HAVING condition is applied after GROUP BY.<\/p>\n
      SQL Query:<\/strong><\/p>\n
      SELECT Country,Count(*) as OrderCount\r\nFROM Orders \r\nWHERE Quantity > 1\r\nGROUP BY Country\r\nHAVING Count(*) > 0<\/pre>\n
      Mongo Shell Query:<\/strong><\/p>\n
      db.Orders.aggregate(\r\n [ \r\n   {$match : {Quantity: { $gt: 1 }}}\r\n  ,{$group : {_id : \"$Country\",OrderCount: { $sum: 1 }}} \r\n  ,{$match : {OrderCount: { $gte: 1 }}}\u00a0\r\n  ,{$project : {Country: \"$_id\", _id: 0, OrderCount: 1 }}\r\n ]\r\n)<\/pre>\n
      SSIS MongoDB Source Query:<\/strong><\/p>\n
      {Table: Orders,Method: aggregate} \r\n{ pipeline: \r\n   [\r\n      {\r\n        $group : {_id : \"$Country\",OrderCount: { $sum: 1 }}\r\n      }      \r\n   ]\r\n}<\/pre>\n
      Example 4 –\u00a0MongoDB Aggregation Query with $unwind (Flatten nested Array)<\/h3>\n
      Now lets look at an example of $unwind operator<\/a>. $unwind can be used to perform operations on nested array. Common things would be flatten the hierarchy and then apply group by operation. Lets find out Average Quantity of Items By Order Number<\/p>\n
      SQL Query:<\/strong><\/p>\n
      SELECT Orders.OrderId as OrderNumber,Avg(Items.Quantity) as AvgItemQty\r\nFROM Orders \r\nJOIN Items ON Orders.OrderId = Items.OrderId\r\nGROUP BY Orders.OrderId<\/pre>\n
      Mongo Shell Query:<\/strong><\/p>\n
      db.Orders.aggregate(\r\n [ \r\n   {$unwind : \"$Items\"}\r\n  ,{$group : {_id : \"$OrderId\",AvgItemQty: { $avg: \"$Items.Quantity\" }}} \r\n  ,{$project : {OrderNumber: \"$_id\", _id: 0, AvgItemQty: 1 }}\r\n ]\r\n)<\/pre>\n
      SSIS MongoDB Source Query:<\/strong><\/p>\n
      {Table: Orders,Method: aggregate} \r\n{ pipeline: \r\n  [ \r\n    {$unwind : \"$Items\"}\r\n   ,{$group : {_id : \"$OrderId\",AvgItemQty: { $avg: \"$Items.Quantity\" }}} \r\n   ,{$project : {OrderNumber: \"$_id\", _id: 0, AvgItemQty: 1 }}\r\n  ]\r\n)\r\n\r\n<\/pre>\n
      <\/h2>\n
      MongoDB Query with Dynamic Value \/ ISO DateTime<\/h2>\n
      There will be a time when you have to supply Dynamic Value from Variable or supply Dynamic Date time inside your query. Dates are little different than other data types. Check this article<\/a> to learn more about passing dates in MongoDB Query.<\/p>\n
      For Dynamic Date use SSIS Variables Format Specifiers<\/a><\/p>\n
      Example of DateTime query (Static Value)<\/strong><\/h3>\n
      {Table: Orders,Method: aggregate} \r\n{ pipeline: \r\n   [\r\n     {$match : {OrderDate : { $gt: new ISODate(\"2010-01-01\") }}} \r\n    ,{$group : {_id : \"$Country\",OrderCount: { $sum: 1 }}} \r\n    ,{$project : {Country: \"$_id\", _id: 0, OrderCount: 1 }} ,{$sort : {Country: 1}}  \r\n   ]\r\n}<\/pre>\n
      Example of using Dynamic Value from Variable<\/strong><\/h3>\n
      To pass date from SSIS Variable you can use\u00a0Format Specifiers<\/a>. (e.g. yyyy-MM-dd<\/strong>\u00a0 or for full ISO date\u00a0yyyy-MM-ddTHH:mm:ssZ<\/strong>\u00a0)<\/p>\n
      {Table: Orders,Method: aggregate} \r\n{ pipeline: \r\n   [\r\n     {$match : {OrderDate : { $gt: new ISODate(\"{{User::varOrderDate,yyyy-MM-dd}}\") }}} \r\n    ,{$group : {_id : \"$Country\",OrderCount: { $sum: 1 }}} \r\n    ,{$project : {Country: \"$_id\", _id: 0, OrderCount: 1 }} ,{$sort : {Country: 1}}  \r\n   ]\r\n}<\/pre>\n
      <\/h3>\n
      Conclusion<\/h2>\n
      MongoDB is one of the most popular NoSQL \/ Document Storage Engine. SSIS PowerPack<\/a> make super easy and efficient to read\/write data in MongoDB without any coding effort. Download SSIS PowerPack<\/a>\u00a0to try out MongoDB features along with 70+ other components .<\/p>\n
      Search terms:<\/strong>
      \nmongodb group by count
      \nmongodb group by multiple fields
      \nmongodb group by date
      \nmongodb group example
      \nmongodb group sort
      \nmongodb group by month
      \nmongodb group by having
      \nmongodb group distinct
      \nmongodb group by sum
      \nmongodb aggregate
      \nmongodb aggregate sort
      \nmongodb aggregate unwind<\/p>\n
       <\/p>\n","protected":false},"excerpt":{"rendered":"
      Introduction In this post you will learn how to write flexible MongoDB Aggregation Queries in SSIS (i.e. Group By Query) using SSIS MongoDB Source Component. MongoDB provides powerful Aggregation Pipeline Engine which is conceptually same as writing Group By queries in traditional SQL world but its not exactly same.\u00a0SSIS MongoDB Source returns nested MongoDB JSON […]<\/p>\n","protected":false},"author":1,"featured_media":2115,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[45],"tags":[46],"yoast_head":"\r\nHow to write MongoDB Aggregation Queries in SSIS (Group By) | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Learn how to aggregate MongoDB data using group by query in SSIS. Perform SUM, COUNT, MAX, MIN with group by and having clause similar in SQL query language\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/zappysys.com\/blog\/aggregate-mongodb-data-using-group-by-query-in-ssis\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"How to write MongoDB Aggregation Queries in SSIS (Group By) | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Learn how to aggregate MongoDB data using group by query in SSIS. Perform SUM, COUNT, MAX, MIN with group by and having clause similar in SQL query language\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/aggregate-mongodb-data-using-group-by-query-in-ssis\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/ZappySys\/\" \/>\r\n<meta property=\"article:published_time\" content=\"2016-08-17T15:52:17+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2024-07-10T21:06:01+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"88\" \/>\r\n\t<meta property=\"og:image:height\" content=\"88\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"ZappySys\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/zappysys\/\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/aggregate-mongodb-data-using-group-by-query-in-ssis\/\",\"url\":\"https:\/\/zappysys.com\/blog\/aggregate-mongodb-data-using-group-by-query-in-ssis\/\",\"name\":\"How to write MongoDB Aggregation Queries in SSIS (Group By) | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/aggregate-mongodb-data-using-group-by-query-in-ssis\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/aggregate-mongodb-data-using-group-by-query-in-ssis\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png\",\"datePublished\":\"2016-08-17T15:52:17+00:00\",\"dateModified\":\"2024-07-10T21:06:01+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"Learn how to aggregate MongoDB data using group by query in SSIS. Perform SUM, COUNT, MAX, MIN with group by and having clause similar in SQL query language\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/aggregate-mongodb-data-using-group-by-query-in-ssis\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/aggregate-mongodb-data-using-group-by-query-in-ssis\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/aggregate-mongodb-data-using-group-by-query-in-ssis\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png\",\"width\":88,\"height\":88},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/aggregate-mongodb-data-using-group-by-query-in-ssis\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to write MongoDB Aggregation Queries in SSIS (Group By)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/zappysys.com\/blog\/#website\",\"url\":\"https:\/\/zappysys.com\/blog\/\",\"name\":\"ZappySys Blog\",\"description\":\"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/zappysys.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\",\"name\":\"ZappySys\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"caption\":\"ZappySys\"},\"sameAs\":[\"http:\/\/www.zappysys.com\/\",\"https:\/\/www.facebook.com\/ZappySys\/\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/\"],\"url\":\"https:\/\/zappysys.com\/blog\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to write MongoDB Aggregation Queries in SSIS (Group By) | ZappySys Blog","description":"Learn how to aggregate MongoDB data using group by query in SSIS. Perform SUM, COUNT, MAX, MIN with group by and having clause similar in SQL query language","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/zappysys.com\/blog\/aggregate-mongodb-data-using-group-by-query-in-ssis\/","og_locale":"en_US","og_type":"article","og_title":"How to write MongoDB Aggregation Queries in SSIS (Group By) | ZappySys Blog","og_description":"Learn how to aggregate MongoDB data using group by query in SSIS. Perform SUM, COUNT, MAX, MIN with group by and having clause similar in SQL query language","og_url":"https:\/\/zappysys.com\/blog\/aggregate-mongodb-data-using-group-by-query-in-ssis\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2016-08-17T15:52:17+00:00","article_modified_time":"2024-07-10T21:06:01+00:00","og_image":[{"width":88,"height":88,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png","type":"image\/png"}],"author":"ZappySys","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/zappysys\/","twitter_misc":{"Written by":"ZappySys","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/aggregate-mongodb-data-using-group-by-query-in-ssis\/","url":"https:\/\/zappysys.com\/blog\/aggregate-mongodb-data-using-group-by-query-in-ssis\/","name":"How to write MongoDB Aggregation Queries in SSIS (Group By) | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/aggregate-mongodb-data-using-group-by-query-in-ssis\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/aggregate-mongodb-data-using-group-by-query-in-ssis\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png","datePublished":"2016-08-17T15:52:17+00:00","dateModified":"2024-07-10T21:06:01+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"Learn how to aggregate MongoDB data using group by query in SSIS. Perform SUM, COUNT, MAX, MIN with group by and having clause similar in SQL query language","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/aggregate-mongodb-data-using-group-by-query-in-ssis\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/aggregate-mongodb-data-using-group-by-query-in-ssis\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/aggregate-mongodb-data-using-group-by-query-in-ssis\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png","width":88,"height":88},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/aggregate-mongodb-data-using-group-by-query-in-ssis\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to write MongoDB Aggregation Queries in SSIS (Group By)"}]},{"@type":"WebSite","@id":"https:\/\/zappysys.com\/blog\/#website","url":"https:\/\/zappysys.com\/blog\/","name":"ZappySys Blog","description":"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/zappysys.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82","name":"ZappySys","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","caption":"ZappySys"},"sameAs":["http:\/\/www.zappysys.com\/","https:\/\/www.facebook.com\/ZappySys\/","https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/"],"url":"https:\/\/zappysys.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/736"}],"collection":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=736"}],"version-history":[{"count":18,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/736\/revisions"}],"predecessor-version":[{"id":11126,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/736\/revisions\/11126"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/2115"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=736"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=736"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=736"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}