Flatting in Azure data factory

Question

Flatting in Azure data factory

mustafa.ispir 21

I have a column which include json values as string format ;

[{"client_key":"82dsa89da0e12da0","roles":[{"role_name":"Deneme 1","group_role_name_key":"83210830da8sd0ad21","role_name_id":"12345","encoded_key":"9dsad798a7sd9"}]},
{"client_key":"7sdac79sad09a798s79","roles":[]},
{"client_key":"djkl21ldjlsajd2121","roles":[]}]

And i want to this output

client_key
82dsa89da0e12da0
7sdac79sad09a798s79
djkl21ldjlsajd2121

First i split this column in derived columns ;

split(replace(replace(replace(group_members,'[',''),']',''),'},{','}|{'),'|')

Then i use flatten to unroll.

And when i parsed it like below

I see null client keys like this.

How can i handle it ?

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2022-07-20T23:33:25.72+00:00

Hello @mustafa.ispir ,

Thanks for the question and using MS Q&A platform.

As per my understanding you would like to flatten the your JSON source using Mapping data flow in ADF. To better assist on your query, could you please confirm what should be your output look like? How do you like to flatten your source data?

From the initial query you mentioned that you want only the client_key as output or do you also want to output other columns from your source as well? If yes, please help provide a sample output structure so that we can assist accordingly.

We look forward to your confirmation.

Thank you
mustafa.ispir 21 Reputation points

2022-07-21T06:36:33.517+00:00

Hi,

Thanks for the answer.
I only need encoded_key(unique key on the table) and client key(which extracted from json format column).
client key come from azure sql database relational table column.
It include data sth like that.

[{"client_key":"82dsa89da0e12da0","roles":[{"role_name":"Deneme 1","group_role_name_key":"83210830da8sd0ad21","role_name_id":"12345","encoded_key":"9dsad798a7sd9"}]},
{"client_key":"7sdac79sad09a798s79","roles":[]},
{"client_key":"djkl21ldjlsajd2121","roles":[]}]

output like :
encoded_key client_key
encoded_key from table 82dsa89da0e12da0
encoded_key from table 7sdac79sad09a798s79
encoded_key from table djkl21ldjlsajd2121

output's encoded keys are come from another column,not from json.
Such as ;

I am looking forward to your reply.
Thank you
mustafa.ispir 21 Reputation points

2022-07-22T07:23:48.73+00:00

encoded_key client_key
8a19da937dd86cb2017ddc48b63629d3 8a19b9b77d6f89ce017d718c60177eb5
8a19da937dd86cb2017ddc48b63629d3 8a19aefd7d4b2754017d4bf407630657

Your answer

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2022-07-20T23:33:25.72+00:00

Hello @mustafa.ispir ,

Thanks for the question and using MS Q&A platform.

As per my understanding you would like to flatten the your JSON source using Mapping data flow in ADF. To better assist on your query, could you please confirm what should be your output look like? How do you like to flatten your source data?

From the initial query you mentioned that you want only the client_key as output or do you also want to output other columns from your source as well? If yes, please help provide a sample output structure so that we can assist accordingly.

We look forward to your confirmation.

Thank you
mustafa.ispir 21 Reputation points

2022-07-21T06:36:33.517+00:00

Hi,

Thanks for the answer.
I only need encoded_key(unique key on the table) and client key(which extracted from json format column).
client key come from azure sql database relational table column.
It include data sth like that.

[{"client_key":"82dsa89da0e12da0","roles":[{"role_name":"Deneme 1","group_role_name_key":"83210830da8sd0ad21","role_name_id":"12345","encoded_key":"9dsad798a7sd9"}]},
{"client_key":"7sdac79sad09a798s79","roles":[]},
{"client_key":"djkl21ldjlsajd2121","roles":[]}]

output like :
encoded_key client_key
encoded_key from table 82dsa89da0e12da0
encoded_key from table 7sdac79sad09a798s79
encoded_key from table djkl21ldjlsajd2121

output's encoded keys are come from another column,not from json.
Such as ;

I am looking forward to your reply.
Thank you
mustafa.ispir 21 Reputation points

2022-07-22T07:23:48.73+00:00

encoded_key client_key
8a19da937dd86cb2017ddc48b63629d3 8a19b9b77d6f89ce017d718c60177eb5
8a19da937dd86cb2017ddc48b63629d3 8a19aefd7d4b2754017d4bf407630657

Share via

Flatting in Azure data factory

Your answer