Azure cosmos DB dataset one column having sub dataset that need to be extract

Hi Team,

and also please help me to create a query for reation between two fields mentioned below

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: 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:

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 [email protected]. 

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_type

from 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

and also i have adjested flaternarray=-1 however below query is not running please suggest

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_type

from 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

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:

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 [email protected].

 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.

same query when i run from cdata source for azure cosmos db it is returning an error like below.

if i exclude assignee related attributes also its not running throughing an error like below.

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

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