Azure Data Factory - How can i Remove a Property from a JSON result in ADF

Question

I am retrieving JSON output from an API call in an ADF Web Activity.
Then i am saving the JSON to SQL Server via a stored procedure call.

Sometimes the JSON payload is over 800K and too large to save to SQL -- get the payload is too large error message.

One of the JSON properties is the culprit for this size, and i really don't need that property -- so all i need to do is remove that from the JSON before i send to SQL server and it should be fine.

But i am having trouble removing a property from the JSON prior to saving to SQL.
Here is a simplified sample JSON -- i just need to remove the AppliedWkt property

[
{
"OrderId": "0032da1q-0000-0000-0000-000000000000",
"TargetDate": "2020-11-16T00:00:00",
"AppliedWkt": "abcdefghijklmnop",
"Services": [
{
"Id": "00000000-0000-0000-0000-000000000000",
"Operation": {
"Id": 45,
"Type": 1,
"Name": "Split",
"DisplayName": "Ground",
"Culture": "en-US"
}
}
]
}
]

In other integration tools, i typically am just mapping source JSON prioperties to destination JSON properties and can skip right over any source property i do not want in my ending JSON. It doesn't seem quite that simple via ADF.

Answer 1

Hello @Trent Byberg ,

Thanks for the question and using MS Q&A platform.

Alternatively, is there a possibility you could make use of the **Copy Data Activity ** or Mapping Data Flow to meet your requirement - in place of Web Activity

You'll be able to specify the column mapping for both source and sink - You will

In both scenarios,

Source : REST/HTTP Connector connecting to the API Endpoint
Sink : SQL

Alternatively, if the above does not meet your requirement. Please provide additional context on your implementation regarding Stored Proc etc.

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