azure data flow - access Nested json data

Question

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": ""
}
}
]
}
}

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.