How to use ADF copy activity to map complex JSON to table

Question

How to use ADF copy activity to map complex JSON to table

Kumar, Sunil 26

I would like to insert the data from copy activity (REST API as source) to Snowflake table (Sink). While using the mapping provided by copy activity is giving option to map all the elements in the nested JSON to columns in table.

In the below example, i wanted to map the nested JSON (batters) to a single column in table instead of mapping all the inner elements of "batters".
I tried different options to map "batters" to a varchar/variant column in Snowflake however getting empty data.
Any help would be great!

{
	"id": "0001",
	"type": "donut",
	"name": "Cake",
	"ppu": 0.55,
	"batters":
		{
			"batter":
				[
					{ "id": "1001", "type": "Regular" },
					{ "id": "1002", "type": "Chocolate" },
					{ "id": "1003", "type": "Blueberry" },
					{ "id": "1004", "type": "Devil's Food" }
				]
		},
	"topping":
		[
			{ "id": "5001", "type": "None" },
			{ "id": "5002", "type": "Glazed" },
			{ "id": "5005", "type": "Sugar" },
			{ "id": "5007", "type": "Powdered Sugar" },
			{ "id": "5006", "type": "Chocolate with Sprinkles" },
			{ "id": "5003", "type": "Chocolate" },
			{ "id": "5004", "type": "Maple" }
		]
}

Amira Bedhiafi 41,116 Reputation points Volunteer Moderator

2023-08-26T21:49:20.75+00:00
When you define the source in the ADF Copy Activity, ensure that you're referencing the REST API endpoint that returns the desired JSON data.

Then, define your Snowflake table sink. Ensure that you have a VARCHAR or VARIANT column where you want the entire batters object to land.

In the mapping configuration of the Copy Activity, you will typically see options to auto-map each field in the JSON to corresponding columns in the Snowflake table.

You don't want ADF to auto-resolve each nested field for the' batters' field (like the individual batter entries). Instead, you want it to take the entire batters object and map it to your chosen column.

To do this, you can use the following expression in the mapping for the desired column: @item().batters. This will effectively grab the entire batters object.

As for the alterations, since you're inserting a complex object into a single column, consider transforming the batters object into a string representation of the JSON. I assume this will make it easier to store and later query in Snowflake.

After setting up the above configuration, execute the pipeline. The data should now land in Snowflake with the entire batters object in a single column.

In Snowflake side, If you've stored the data in a VARCHAR column and later need to query the nested elements, you can use Snowflake's built-in functions to parse and query JSON data.
Kumar, Sunil 26 Reputation points

2023-08-28T05:31:38.8333333+00:00

Hi Amira,

Thank you so much for your detailed response.
When i am calling the same REST API endpoint in Postman, I am getting the desired JSON data. I have used the same details while defining the "Linked Service/DataSet" and i am able to get the desired JSON data.
Yes, I was trying to do something similar by not using the auto-map column feature of ADF and instead manually enter some expression to get hold of the objects.
When i tried to use the @item().batters, getting an error "The expression 'item().batters' cannot be evaluated because property 'batters' cannot be selected."
Can you please help me understand where is this item reference pointing to? is it the collection reference? if yes, what value should i be selecting there!

Thank you,
Sunil
ShaikMaheer-MSFT 38,556 Reputation points Microsoft Employee Moderator

2023-09-01T10:11:40.06+00:00

Hi Kumar, Sunil, Just checking if below answer helps. If yes, please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries. Thank you.

Answer accepted by question author

0 additional answers

Your answer

Amira Bedhiafi 41,116 Reputation points Volunteer Moderator

2023-08-26T21:49:20.75+00:00

When you define the source in the ADF Copy Activity, ensure that you're referencing the REST API endpoint that returns the desired JSON data.

Then, define your Snowflake table sink. Ensure that you have a VARCHAR or VARIANT column where you want the entire batters object to land.

In the mapping configuration of the Copy Activity, you will typically see options to auto-map each field in the JSON to corresponding columns in the Snowflake table.

You don't want ADF to auto-resolve each nested field for the' batters' field (like the individual batter entries). Instead, you want it to take the entire batters object and map it to your chosen column.

To do this, you can use the following expression in the mapping for the desired column: @item().batters. This will effectively grab the entire batters object.

As for the alterations, since you're inserting a complex object into a single column, consider transforming the batters object into a string representation of the JSON. I assume this will make it easier to store and later query in Snowflake.

After setting up the above configuration, execute the pipeline. The data should now land in Snowflake with the entire batters object in a single column.

In Snowflake side, If you've stored the data in a VARCHAR column and later need to query the nested elements, you can use Snowflake's built-in functions to parse and query JSON data.
Kumar, Sunil 26 Reputation points

2023-08-28T05:31:38.8333333+00:00

Hi Amira,

Thank you so much for your detailed response.
When i am calling the same REST API endpoint in Postman, I am getting the desired JSON data. I have used the same details while defining the "Linked Service/DataSet" and i am able to get the desired JSON data.
Yes, I was trying to do something similar by not using the auto-map column feature of ADF and instead manually enter some expression to get hold of the objects.
When i tried to use the @item().batters, getting an error "The expression 'item().batters' cannot be evaluated because property 'batters' cannot be selected."
Can you please help me understand where is this item reference pointing to? is it the collection reference? if yes, what value should i be selecting there!

Thank you,
Sunil
ShaikMaheer-MSFT 38,556 Reputation points Microsoft Employee Moderator

2023-09-01T10:11:40.06+00:00

Hi Kumar, Sunil, Just checking if below answer helps. If yes, please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries. Thank you.

Answer 1

ShaikMaheer-MSFT 38,556 Microsoft Employee Moderator

Hi Kumar, Sunil,

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

One possible way, I can think of is use web activity to get your json from API. Then have a variable in pipeline and using set variable activity, take all the batters key data as string into variable. Use copy activity, use additional column field to add send this variable data as additional column. and then map only that additional column with your snowflake table column.

Hope this helps. Please let me know how it goes. Thank you.

Please consider hitting Accept Answer button. Accepted answers help community as well.

ShaikMaheer-MSFT 38,556 Reputation points Microsoft Employee Moderator

2023-08-30T09:28:43.4733333+00:00

Hi Kumar, Sunil, Just checking if above answer helps. If yes, please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries. Thank you.
Kumar, Sunil 26 Reputation points

2023-09-18T20:40:12.2866667+00:00

Hi Maheer,
Sorry for my late response.

Yes, I have tried this option before reaching out to help on this forum.
I was able to load that JSON API response into a variable however when i was trying to map it to the column in Snowflake, then i was getting following error. The response received was in proper JSON format only.
The JSON which i gave in the original question was a sample only and the steps you mentioned worked, however in my scenario the JSON was little complex.

Later i moved to an approach when i was saving the API response in a JSON file and then using the external stage from Snowflake to access that file and load the whole content.
Once the content is loaded in Snowflake table, i used lateral flatten to flatten the structure as per the requirements.

I wasn't sure what was going wrong when i was trying to use the copy data activity to directly map the additional column (i.e., the variable with the JSON API response) to the Snowflake column.

Thank you so much for your response on this thread!
I watch all your data engineering related videos and learn a lot from it!

Share via

How to use ADF copy activity to map complex JSON to table

0 additional answers

Your answer