How to parse a json array (sub-array)?

Question

Hello ,

I have a incoming json input as below. I am able to parse almost all the data and load into my db. But its the custom_checks section, that I need some assistance please on how to parse them properly. Currently, i have them all being loaded into a single column (in the db) as a string.

Here is the primary issue.

• I can expect to receive 1 or 2 or 4 different custom_types and their respective values. Only one value will be with one custom_type
• The order can be different.
  • ex: 998 (translates to region) can come second
    • 4466 (translates to vehicle) can come first
• All expected custom_types are
  • 998 - Region
    • 4466 - Vehicle
      • 3356 - Mobile
        • 2311 - SubRegion.

My expectation is, to have four columns in the DB so i can map each of these elements from the array to their respective column in sql db. How can this be achievable since there is randomness in this. I tried using the Flatten activity in the dataflow, but not sure how to map the individual custom_types to field as they are not in set order.

{
	"previous": "https://previouslink.com",
	"results": [
		{
			"TM_Sent": false,
			"appt_check": false,
			"custom_checks": [
				{
					"custom_type": 998,
					"Type_value": "0"
				},
				{
					"custom_type": 4466,
					"Type_value": "0"
				}
			],
			"deleted_flag": false,
			"id": "2A457fytg79",
			"tele_url": null
		}
	],
	"next": "https://nextlink.com"
}a

Answer 1

create a data flow under ADF then add a JSON source

Add a Derived Column Transformation after

In the Derived Column Transformation, create four new columns with the following formulas:

• Region: if(custom_checks[indexOf(custom_checks, {custom_type: 998})].Type_value, custom_checks[indexOf(custom_checks, {custom_type: 998})].Type_value, null)
• Vehicle: if(custom_checks[indexOf(custom_checks, {custom_type: 4466})].Type_value, custom_checks[indexOf(custom_checks, {custom_type: 4466})].Type_value, null)
• Mobile: if(custom_checks[indexOf(custom_checks, {custom_type: 3356})].Type_value, custom_checks[indexOf(custom_checks, {custom_type: 3356})].Type_value, null)
• SubRegion: if(custom_checks[indexOf(custom_checks, {custom_type: 2311})].Type_value, custom_checks[indexOf(custom_checks, {custom_type: 2311})].Type_value, null)

Add a Sink to your Data Flow, connecting it to your SQL database table.

In the Sink settings, map the derived columns to the corresponding columns in your SQL table.