# Azure cosmos DB dataset one column having sub dataset that need to be extract **URL:** \ **Category:** CData Drivers\ **Tags:** question, nosql\ **Created:** [January 30, 2025, 9:22am UTC](https://community.cdata.com/t/azure-cosmos-db-dataset-one-column-having-sub-dataset-that-need-to-be-extract/1009 "2025-01-30T09:22:53Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Renukachari\_Kasee](https://avatars.discourse-cdn.com/v4/letter/r/ecccb3/32.png) [@Renukachari\_Kasee](https://community.cdata.com/u/Renukachari_Kasee)\ **Post date:** [January 30, 2025, 9:22am UTC](https://community.cdata.com/t/azure-cosmos-db-dataset-one-column-having-sub-dataset-that-need-to-be-extract/1009/1 "2025-01-30T09:22:54Z")
Hi All, Please help me on to extract the column dataset using cdata cosomos db driver using cdata connector. ![16cf34e0-679e-4816-80ad-749f99672765.png](https://us1.discourse-cdn.com/cdata/original/1X/9049af706d91c1978d6ef9db890cc54310e207b9.png) Thanks, Chari ---
**Author:** ![Renukachari\_Kasee](https://avatars.discourse-cdn.com/v4/letter/r/ecccb3/32.png) [@Renukachari\_Kasee](https://community.cdata.com/u/Renukachari_Kasee)\ **Post date:** [January 30, 2025, 12:42pm UTC](https://community.cdata.com/t/azure-cosmos-db-dataset-one-column-having-sub-dataset-that-need-to-be-extract/1009/2 "2025-01-30T12:42:17Z")
Hi Team, and also please help me to create a query for reation between two fields mentioned below ![c1217c05-81eb-49d2-9dd7-869acb2284c4.png](https://us1.discourse-cdn.com/cdata/original/1X/b12c0cb7732904ec4b85a06bcbd6be1c1f26b972.png) ---
**Author:** ![Ethem\_Q](https://sea3.discourse-cdn.com/cdata/user_avatar/community.cdata.com/ethem_q/32/1997_2.png) [@Ethem\_Q](https://community.cdata.com/u/Ethem_Q)\ **Post date:** [January 30, 2025, 7:01pm UTC](https://community.cdata.com/t/azure-cosmos-db-dataset-one-column-having-sub-dataset-that-need-to-be-extract/1009/3 "2025-01-30T19:01:11Z")
Hi ​ @Renukachari_Kasee In order to extract the nested values from the breakdown column you will need to set the FlatenArrays connection property. Setting it to -1 will flatten all the elements of the nested arrays. You can read more about this here: [CData SSIS Components for Azure Cosmos DB - FlattenArrays](https://cdn.cdata.com/help/EHK/rssis/RSBCosmosdb_p_FlattenArrays.htm) Once you set FlattenArrays=-1 the table will expose additional columns reflecting the nested values. You can now run a query as follows: ```auto SELECT Id, _ts, breakdown.0.language AS Language, breakdown.0.editor AS editor …. FROM [platform-engineering].[history] ``` As for your second question, can you please provide more details as to what exactly are you looking to accomplish? Should you have any further questions feel free to reach out to the support team at [support@cdata.com](mailto:support@cdata.com). ---
**Author:** ![Renukachari\_Kasee](https://avatars.discourse-cdn.com/v4/letter/r/ecccb3/32.png) [@Renukachari\_Kasee](https://community.cdata.com/u/Renukachari_Kasee)\ **Post date:** [January 30, 2025, 7:31pm UTC](https://community.cdata.com/t/azure-cosmos-db-dataset-one-column-having-sub-dataset-that-need-to-be-extract/1009/4 "2025-01-30T19:31:29Z")
```auto select c.RUN_DATE,c.date,c._ts,c.last_update,c.total_seats,c.organization,z.created_at,z.last_activity_at,z.last_activity_editor,z.plan_type,z.updated_at,z.assignee.id as assignee_id,z.assignee.login as assignee_login,z.assignee.name as assignee_name,z.assignee.node_id as assignee_node_id,z.assignee.type as assignee_typefrom seats_history c join z in c.seats ``` Hi Etham, above queryn is working fine in Azure data studio, but not working in Cdata connector. do i need to do any specific settings please suggset ---
**Author:** ![Renukachari\_Kasee](https://avatars.discourse-cdn.com/v4/letter/r/ecccb3/32.png) [@Renukachari\_Kasee](https://community.cdata.com/u/Renukachari_Kasee)\ **Post date:** [January 30, 2025, 11:57pm UTC](https://community.cdata.com/t/azure-cosmos-db-dataset-one-column-having-sub-dataset-that-need-to-be-extract/1009/5 "2025-01-30T23:57:12Z")
and also i have adjested flaternarray=-1 however below query is not running please suggest ![317bde79-153d-435c-8ee7-b40ee4b6957b.png](https://us1.discourse-cdn.com/cdata/original/1X/6b9c7e85ba7e0bd2043a4bf408824de8b68a8b74.png) ---
**Author:** ![Renukachari\_Kasee](https://avatars.discourse-cdn.com/v4/letter/r/ecccb3/32.png) [@Renukachari\_Kasee](https://community.cdata.com/u/Renukachari_Kasee)\ **Post date:** [January 31, 2025, 1:49am UTC](https://community.cdata.com/t/azure-cosmos-db-dataset-one-column-having-sub-dataset-that-need-to-be-extract/1009/6 "2025-01-31T01:49:44Z")
```auto select c.date,c._ts,c.last_update,c.total_seats,c.organization,z.created_at,z.last_activity_at,z.last_activity_editor,z.plan_type,z.updated_at,z.assignee.id as assignee_id,z.assignee.login as assignee_login,z.assignee.name as assignee_name,z.assignee.node_id as assignee_node_id,z.assignee.type as assignee_typefrom seats_history c join z in c.seats ``` above queryn is working fine in Azure data studio, but not working in Cdata source connector. do i need to do any specific settings please suggset ---
**Author:** ![Ethem\_Q](https://sea3.discourse-cdn.com/cdata/user_avatar/community.cdata.com/ethem_q/32/1997_2.png) [@Ethem\_Q](https://community.cdata.com/u/Ethem_Q)\ **Post date:** [January 31, 2025, 2:22pm UTC](https://community.cdata.com/t/azure-cosmos-db-dataset-one-column-having-sub-dataset-that-need-to-be-extract/1009/7 "2025-01-31T14:22:08Z")
Hi ​ @Renukachari_Kasee Note that SSIS preview will throw an error as it would not recognize the name of the columns for the flattened values. In order for the query to work you will need to enclose the columns in square brackets: ```auto SELECT Id, _ts, [breakdown.0.language] AS Language, [breakdown.0.editor] AS editor …. FROM [platform-engineering].[history] ``` As for the other issue with JOINs can you please provide more information. Are you seeing an error message when you run this query? Would you be able to generate some logs and send them over to the support team so that the team can investigate them for you? Feel free to reach out to the CData support team at [support@cdata.com](mailto:support@cdata.com). ---
**Author:** ![Renukachari\_Kasee](https://avatars.discourse-cdn.com/v4/letter/r/ecccb3/32.png) [@Renukachari\_Kasee](https://community.cdata.com/u/Renukachari_Kasee)\ **Post date:** [January 31, 2025, 6:49pm UTC](https://community.cdata.com/t/azure-cosmos-db-dataset-one-column-having-sub-dataset-that-need-to-be-extract/1009/8 "2025-01-31T18:49:12Z")
Hi Ethem, thank you so much for your response, first query has been resolved. coming to 2nd query when i query the same query in Azure data studio result is coming without any issue. like below. ![a81f4713-c181-41bb-b868-923b53b2e559.png](https://us1.discourse-cdn.com/cdata/original/2X/4/43109b8ef050394219ce76a8b9e1634c28e0ed56.png) same query when i run from cdata source for azure cosmos db it is returning an error like below. ![f1229c50-428b-4c22-a9ab-75c71fd4a6ed.png](https://us1.discourse-cdn.com/cdata/original/2X/9/99f04bf8e1ff24d541061d23256f12b8e2feff19.png) if i exclude assignee related attributes also its not running throughing an error like below. ![d2accd78-0c25-41dd-9abe-2c48b6ecc987.png](https://us1.discourse-cdn.com/cdata/original/2X/0/0e2f55dc5368097eb538f884dd7a20f065f7c988.png) ---
**Author:** ![Renukachari\_Kasee](https://avatars.discourse-cdn.com/v4/letter/r/ecccb3/32.png) [@Renukachari\_Kasee](https://community.cdata.com/u/Renukachari_Kasee)\ **Post date:** [January 31, 2025, 6:53pm UTC](https://community.cdata.com/t/azure-cosmos-db-dataset-one-column-having-sub-dataset-that-need-to-be-extract/1009/9 "2025-01-31T18:53:56Z")
from Cdata staned point. below are the connection settings i am using for this project. any problem with this connection manager to retive join related information. with the below connection i am able to retrive the data from single table information. but when i join the other document. its not working kindly help me here Thanks, Chari ![f0010ab6-d45a-426a-b385-83dcaebed2df.png](https://us1.discourse-cdn.com/cdata/original/2X/6/6a896cc38ec3259851c9a408828ac39a05d604ae.png) ---
**Author:** ![Renukachari\_Kasee](https://avatars.discourse-cdn.com/v4/letter/r/ecccb3/32.png) [@Renukachari\_Kasee](https://community.cdata.com/u/Renukachari_Kasee)\ **Post date:** [January 31, 2025, 10:05pm UTC](https://community.cdata.com/t/azure-cosmos-db-dataset-one-column-having-sub-dataset-that-need-to-be-extract/1009/10 "2025-01-31T22:05:32Z")
![b84449ed-8bbb-4ebb-ba2b-14b3b2183fb5.png](https://us1.discourse-cdn.com/cdata/original/1X/dba6bcbeee38cdf3254b5989ae9214a04549220b.png) Hi Ethem, thanks for your replay that option is working fine with  without join the other documnet. when i join the other documnet columns info is  coming as null and columns are repeting. kindly check and suggest