azure data flow - access Nested json data

Question

azure data flow - access Nested json data

Padamata Chaitanya Krishna Sairam 21

This is my Json data. I need to access the short_desc conditionally validating if rule_result is False and and rule_id is in 4k-5k range using azure data flow

{
"ID": 33333,
"validation_results": {
"all_rules_pass_fail_result": "False",
"error": "",
"all_rules_result_detail": [
{
"rule_result": "False",
"error": "",
"rule": {
"rule_ID": "4004",
"short_desc": "High job level request",
"skip_if_fast_track": "Normal",
"operator_1": ">",
"operator_2": "",
"operator_3": "",
"data_type_1": "str",
"data_type_2": "",
"data_type_3": "",
"data_field_left_2_name": "",
"data_field_left_3_name": "",
"data_field_right_1_name": "",
"data_field_right_2_name": "",
"data_field_right_3_name": "",
"const_right_1": "Level 06",
"const_right_2": "",
"const_right_3": "",
"linked_1_rule": "false",
"linked_2_rule": "false",
"linked_rule_operator_1": "",
"linked_rule_operator_2": ""
}
}
]
}
}

ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2021-07-19T07:10:52.993+00:00
Hi @Padamata Chaitanya Krishna Sairam ,

Welcome to Microsoft Q&A Platform. Thank you for posting your query here.

Could you please help on below clarifications to understand your query better and help on resolution.

Are you trying load above json data in to some target. If yes, Could you please share your target details as well?

When you say "need to access the short_desc conditionally validating if rule_result is False and and rule_id is in 4k-5k range", you want to load only thoes json objects as rows in your target?

Thank you.
Padamata Chaitanya Krishna Sairam 21 Reputation points

2021-07-19T07:20:35.737+00:00
Are you trying load above json data in to some target. If yes, Could you please share your target details as well?

I need all the short_desc from json files fetched by the pipeline.

I'm using azure dataflow to parse the json here. I want all short_desc into derived column and then add other data, store in a txt file for further processing.

short_desc is needed when the rule_result is False and rule_id is in the range of 4k-5k.

When you say "need to access the short_desc conditionally validating if rule_result is False and and rule_id is in 4k-5k range", you want to load only thoes json objects as rows in your target?

Yes you are correct. I want those json objects short_desc only to be loaded in the txt file.

Thanks for the response @ShaikMaheer-MSFT
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2021-07-20T06:58:45.817+00:00

Hi @Padamata Chaitanya Krishna Sairam ,

Thank you for sharing details. Kindly check below posted answer for same and Please Accept Answer. Accepting answer will help community as well. Thank you.
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2021-07-22T05:33:06.23+00:00

Hi @Padamata Chaitanya Krishna Sairam ,

Following up to check is below provided answer helps. If yes. please Accept Answer. Accepting answer helps community as well. Thank you.

Answer accepted by question author

0 additional answers

Your answer

ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2021-07-19T07:10:52.993+00:00

Hi @Padamata Chaitanya Krishna Sairam ,

Welcome to Microsoft Q&A Platform. Thank you for posting your query here.

Could you please help on below clarifications to understand your query better and help on resolution.

Are you trying load above json data in to some target. If yes, Could you please share your target details as well?

When you say "need to access the short_desc conditionally validating if rule_result is False and and rule_id is in 4k-5k range", you want to load only thoes json objects as rows in your target?

Thank you.
Padamata Chaitanya Krishna Sairam 21 Reputation points

2021-07-19T07:20:35.737+00:00

Are you trying load above json data in to some target. If yes, Could you please share your target details as well?

I need all the short_desc from json files fetched by the pipeline.

I'm using azure dataflow to parse the json here. I want all short_desc into derived column and then add other data, store in a txt file for further processing.

short_desc is needed when the rule_result is False and rule_id is in the range of 4k-5k.

When you say "need to access the short_desc conditionally validating if rule_result is False and and rule_id is in 4k-5k range", you want to load only thoes json objects as rows in your target?

Yes you are correct. I want those json objects short_desc only to be loaded in the txt file.

Thanks for the response @ShaikMaheer-MSFT
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2021-07-20T06:58:45.817+00:00

Hi @Padamata Chaitanya Krishna Sairam ,

Thank you for sharing details. Kindly check below posted answer for same and Please Accept Answer. Accepting answer will help community as well. Thank you.
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2021-07-22T05:33:06.23+00:00

Hi @Padamata Chaitanya Krishna Sairam ,

Following up to check is below provided answer helps. If yes. please Accept Answer. Accepting answer helps community as well. Thank you.

Answer 1

Hi @Padamata Chaitanya Krishna Sairam ,

Thank you for posting query in Microsoft Q&A Platform.

I found solution for this issue.

We need to use Flatten Transformation to flatten arrays in our json and then use filter transformation along with our condition to take only desired objects/rows from our source data.

Please check below details example for same.

Step1: Source Transformation

Step2: Flatten Transformation to flatten "all_rules_result_detail" array

Step3: Filter Transformation to filter data where rule_result is False and and rule_id is in 4k-5k range. Expression used is all_rules_result_detail.rule_result == 'False' && (toInteger(all_rules_result_detail.rule.rule_ID) >= 4000 && toInteger(all_rules_result_detail.rule.rule_ID) <= 5000)

Step4: Load your data in to Target using Sink Transformation. Make sure to disable to auto mapping our "Mappings" and manual map columns

Hope this will help. Thank you.

Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.
Want a reminder to come back and check responses? Here is how to subscribe to a notification.

Padamata Chaitanya Krishna Sairam 21 Reputation points

2021-07-22T06:29:12.25+00:00

Thanks for your answer @ShaikMaheer-MSFT

You have inspired many with your YouTube tutorials on Azure. Thank you
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2021-07-22T06:31:09.313+00:00

Hi @Padamata Chaitanya Krishna Sairam ,

Thank you for accepting answer and for your kind words too.

Share via

azure data flow - access Nested json data

0 additional answers

Your answer