{"id":333,"date":"2016-01-25T17:23:29","date_gmt":"2016-01-25T17:23:29","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=333"},"modified":"2018-11-14T13:56:18","modified_gmt":"2018-11-14T13:56:18","slug":"read-mongodb-data-from-array-extract-nested-sub-documents","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/read-mongodb-data-from-array-extract-nested-sub-documents\/","title":{"rendered":"Read MongoDB data from array (extract nested sub documents)"},"content":{"rendered":"<h2>Introduction<\/h2>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-2115 alignleft\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png\" alt=\"\" width=\"88\" height=\"88\" \/>In this post you will learn how to read MongoDB data from Array. We recently introduced\u00a0new <strong>JSON Path expression<\/strong> feature\u00a0in <a href=\"\/\/zappysys.com\/products\/ssis-powerpack\/ssis-mongodb-source\/\">SSIS MongoDB Source Connector<\/a> to extract nested information from MongoDB documents.<\/p>\n<p>&nbsp;<\/p>\n<h2>Read documents from Array in MongoDB<\/h2>\n<p>By default <a href=\"\/\/zappysys.com\/products\/ssis-powerpack\/ssis-mongodb-source\/\">SSIS MongoDB Source Connector<\/a> doesn&#8217;t support extracting data using <strong>JSON Path expression<\/strong>. So if you want to read nested data from sub-document or array then you have enable that option.<\/p>\n<p>Consider following sample Document stored in MongoDB. Lets say you want to extract all orders for each customer.<\/p>\n<p>For that you have enter <strong>$.Orders[*]<\/strong> in the JSON Path expression as below screenshots. Notice it also brings parent\u00a0columns of each Orders (By default parent columns prefixed with P_ ).<\/p>\n<p>To exclude parent columns from your resultset got to Advanced JSON Filter Options tab -&gt; Uncheck &#8220;<strong>Include Parent Columns<\/strong>&#8221; option.<\/p>\n<h3>Sample MongoDB Document<\/h3>\n<pre class=\"lang:js decode:true \">{\r\n    \"_id\" : ObjectId(\"56a2ae6369e0f137a449f58b\"),\r\n    \"CustomerID\" : \"ALFKI\",\r\n    \"CompanyName\" : \"Alfreds Futterkiste\",\r\n    \"ContactName\" : \"Maria Anders\",\r\n    \"ContactTitle\" : \"Sales Representative\",\r\n    \"LocationInfo\" : {\r\n        \"City\" : \"Berlin\",\r\n        \"Address\" : \"Obere Str. 57\",\r\n        \"Region\" : null,\r\n        \"PostalCode\" : \"12209\",\r\n        \"Country\" : \"Germany\"\r\n    },\r\n    \"Phone\" : \"030-0074321\",\r\n    \"Fax\" : \"030-0076545\",\r\n    \"OrdersIDList\" : [ \r\n        10643, \r\n        10692, \r\n        10702\r\n    ],\r\n    \"Orders\" : [ \r\n        {\r\n            \"OrderID\" : 10643,\r\n            \"EmployeeID\" : null,\r\n            \"OrderDate\" : \"1997-08-25 00:00:00\"\r\n        }, \r\n        {\r\n            \"OrderID\" : 10692,\r\n            \"EmployeeID\" : 4,\r\n            \"OrderDate\" : \"1997-10-03 00:00:00\"\r\n        }, \r\n        {\r\n            \"OrderID\" : 10702,\r\n            \"EmployeeID\" : 4,\r\n            \"OrderDate\" : \"1997-10-13 00:00:00\"\r\n        }\r\n    ]\r\n}<\/pre>\n<p>&nbsp;<\/p>\n<div id=\"attachment_334\" style=\"width: 720px\" class=\"wp-caption alignnone\"><a href=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/mongodb-read-data-from-array-extract-nested-documents.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-334\" class=\"size-full wp-image-334\" src=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/mongodb-read-data-from-array-extract-nested-documents.png\" alt=\"Read MongoDB Data from array (extract nested sub documents)\" width=\"710\" height=\"521\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/mongodb-read-data-from-array-extract-nested-documents.png 710w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/mongodb-read-data-from-array-extract-nested-documents-300x220.png 300w\" sizes=\"(max-width: 710px) 100vw, 710px\" \/><\/a><p id=\"caption-attachment-334\" class=\"wp-caption-text\">Read MongoDB Data from array (extract nested sub documents)<\/p><\/div>\n<h2>Server side Filtering using MongoDB Query<\/h2>\n<p>JSON Path filtering happens on client side so first your selected set of documents fetched from MongoDB server and then transformation of happens on client side based on JSON Path you specified (de-normalization).<\/p>\n<p>Below screenshots shows how to use MongoDB SQL Query Language (<a href=\"\/\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/mongodb-source.htm#A_QueryLanguage\" target=\"_blank\" rel=\"noopener\">Read more here<\/a>) to filter data on server side and then apply <strong>JSON Path expression<\/strong> to extract sub-documents or array items from MongoDB documents.<\/p>\n<div style=\"width: 667px\" class=\"wp-caption alignnone\"><img loading=\"lazy\" decoding=\"async\" src=\"\/\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/images\/mongodb-source\/ssis-mongodb-source-settings.png\" alt=\"MongoDB Server Side Filter - Extract MongoDB Data from Array or nested sub-documents using SQL Query and JSON Path\" width=\"657\" height=\"511\" \/><p class=\"wp-caption-text\">MongoDB Server Side Filter &#8211; Extract MongoDB Data from Array or nested sub-documents using SQL Query and JSON Path<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Introduction In this post you will learn how to read MongoDB data from Array. We recently introduced\u00a0new JSON Path expression feature\u00a0in SSIS MongoDB Source Connector to extract nested information from MongoDB documents. &nbsp; Read documents from Array in MongoDB By default SSIS MongoDB Source Connector doesn&#8217;t support extracting data using JSON Path expression. So if [&hellip;]<\/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":[6,71,46,12,4],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\r\n<title>Read MongoDB data from array (extract nested sub documents) | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Learn how to read MongoDB data from Array using SSIS MongoDB Source Connector. Extract data using SQL query language for MongoDB and JSON Path expression\" \/>\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\/read-mongodb-data-from-array-extract-nested-sub-documents\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"Read MongoDB data from array (extract nested sub documents) | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Learn how to read MongoDB data from Array using SSIS MongoDB Source Connector. Extract data using SQL query language for MongoDB and JSON Path expression\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/read-mongodb-data-from-array-extract-nested-sub-documents\/\" \/>\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-01-25T17:23:29+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2018-11-14T13:56:18+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=\"2 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/read-mongodb-data-from-array-extract-nested-sub-documents\/\",\"url\":\"https:\/\/zappysys.com\/blog\/read-mongodb-data-from-array-extract-nested-sub-documents\/\",\"name\":\"Read MongoDB data from array (extract nested sub documents) | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/read-mongodb-data-from-array-extract-nested-sub-documents\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/read-mongodb-data-from-array-extract-nested-sub-documents\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png\",\"datePublished\":\"2016-01-25T17:23:29+00:00\",\"dateModified\":\"2018-11-14T13:56:18+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"Learn how to read MongoDB data from Array using SSIS MongoDB Source Connector. Extract data using SQL query language for MongoDB and JSON Path expression\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/read-mongodb-data-from-array-extract-nested-sub-documents\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/read-mongodb-data-from-array-extract-nested-sub-documents\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/read-mongodb-data-from-array-extract-nested-sub-documents\/#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\/read-mongodb-data-from-array-extract-nested-sub-documents\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Read MongoDB data from array (extract nested sub documents)\"}]},{\"@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":"Read MongoDB data from array (extract nested sub documents) | ZappySys Blog","description":"Learn how to read MongoDB data from Array using SSIS MongoDB Source Connector. Extract data using SQL query language for MongoDB and JSON Path expression","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\/read-mongodb-data-from-array-extract-nested-sub-documents\/","og_locale":"en_US","og_type":"article","og_title":"Read MongoDB data from array (extract nested sub documents) | ZappySys Blog","og_description":"Learn how to read MongoDB data from Array using SSIS MongoDB Source Connector. Extract data using SQL query language for MongoDB and JSON Path expression","og_url":"https:\/\/zappysys.com\/blog\/read-mongodb-data-from-array-extract-nested-sub-documents\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2016-01-25T17:23:29+00:00","article_modified_time":"2018-11-14T13:56:18+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":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/read-mongodb-data-from-array-extract-nested-sub-documents\/","url":"https:\/\/zappysys.com\/blog\/read-mongodb-data-from-array-extract-nested-sub-documents\/","name":"Read MongoDB data from array (extract nested sub documents) | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/read-mongodb-data-from-array-extract-nested-sub-documents\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/read-mongodb-data-from-array-extract-nested-sub-documents\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png","datePublished":"2016-01-25T17:23:29+00:00","dateModified":"2018-11-14T13:56:18+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"Learn how to read MongoDB data from Array using SSIS MongoDB Source Connector. Extract data using SQL query language for MongoDB and JSON Path expression","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/read-mongodb-data-from-array-extract-nested-sub-documents\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/read-mongodb-data-from-array-extract-nested-sub-documents\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/read-mongodb-data-from-array-extract-nested-sub-documents\/#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\/read-mongodb-data-from-array-extract-nested-sub-documents\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Read MongoDB data from array (extract nested sub documents)"}]},{"@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\/333"}],"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=333"}],"version-history":[{"count":3,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/333\/revisions"}],"predecessor-version":[{"id":5377,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/333\/revisions\/5377"}],"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=333"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=333"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=333"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}