Azure Data Factory - Handling complex JSON Structure using Data Flow Activity

Question

Hello,

I am trying to extract a JSON response from an API call. JSON response as follows

It has 12 pages.Each page has 100 order list.32917 and QC6257 are dynamic values. How to do column mapping in sink-synapse since object value coming as dynamic.

{
"responseCode": 0,
"responseMessage": "Success",
"totalOrders": 1151,
"totalPages": 12,
"currentPage": 2,
"orderList": [
{
"32917": {
"orderLocation": "M0456",
"orderId": "M0438257789",
"orderNo": "32917",
"extenalOrderNo": "32917",
"paymentMethod": "Prepaid",
"status": "Delivered",
"items": [
{
"lineno": "110354",
"extLineno": "110354",
"uom": "Each",
"uomqty": "1.00",
"conversion": "1.00",
"orderQty": "1.00",

				}  
			],  
			"paymentItems": [],  
			"codcharge": "0.00"  
			  
		}  
	},	  
	{  
		"QC6257": {  
			"orderLocation": "M09",  
			"orderId": "M095680",  
			"orderNo": "QC6257",  
			"extenalOrderNo": "QC6257",  
			"paymentMethod": "Prepaid",  
			"items": [  
				{  
					"lineno": "1",  
					"extLineno": null,  
					"internalLineNo": "1",  
					"uom": "Each",  
					"uomqty": "1.00",  
					"conversion": "1.00",  
					"orderQty": "1.00",  
					  
				}  
			],  
			"paymentItems": [],  
			"codcharge": "0.00"  
			  
		}  
	},

Answer 1

Hi @Jayanthi P ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

I understand you are trying to fetch data from JSON source which has dynamic keys using Mapping dataflow. I tried to replicate your case , however, it doesn't seem to be achievable.

Since the keys (eg: '32917','QC6257') in the orderlist json are dynamic, you need to flatten the json using these keys. Looking at the json complexity and length , it would be a tough job to select each and every keys to flatten the data.

More efficient way to handle this scenario would be to use rule based mapping in Flatten transformation, which I tried to use as well. I am trying to fetch all the column names which are of integer type like below:

However, the challenge here is the dynamic keys are present inside the array 'orderList[]' . So the rule based mapping would not apply to the sub columns . Again, if we think of flattening the data inside orderList[] array, we need to manually select the subcolumns which is hectic job in your case.

You can consider writing custom code in Java,C# or python to convert all the dynamic keys present within the item of array as static value and export the output JSON then use the same as source JSON in dataflow to flatten the static key.

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  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
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators