Azure data Factory - passing in Json array/Object into web connector

Question

Azure data Factory - passing in Json array/Object into web connector

Mike Wong 41

Hello,

I'm currently building a pipeline where I am calling a REST API using the web connector and then using the Copy Activity to save the data to a SQL DB. The API URL I want to use has the following structure:

https://api-url.api.com/param?start=0&limit=10&sort=[{"param1": "subparam1", "param2": "subparam2"}]

I can ping this endpoint fine in Python although having trouble building this in the Add Dynamic content section. I am currently trying:

@{concat(
'https://api-url.api.com/param?start',
variable1,
'&limit=',
variable2,
'&sort=[{"param1": "subparam1", "param2": "subparam2"}]'})

although this keeps erroring out. I've tried passing in the last part as follows:

@json (string(&sort=[{"param1": "subparam1", "param2": "subparam2"}])
string(&sort=[{"param1": "subparam1", "param2": "subparam2"}])
&sort=[[{{"param1": "subparam1", "param2": "subparam2"}}]]
&sort=[{""param1"": ""subparam1"", ""param2"": ""subparam2""}]
&sort=[{{"param1": "subparam1", "param2": "subparam2"}}]
&sort=[[{"param1": "subparam1", "param2": "subparam2"}]

However, I keep getting an error. I presume it's because of the way Data Factory is parsing this as a string.

Any explanation or help would be great.

Corey Edwards 1 Reputation point

2022-03-31T21:59:59.61+00:00

Was there a solution to this? I am trying to pass a JSON array to the body of a web call in ADF.
Mike Wong 41 Reputation points

2022-03-31T22:15:44.617+00:00

Hello mate,

For my particular use case, I had to make my array URL safe and then it worked as intended.
Corey Edwards 1 Reputation point

2022-03-31T22:47:35.17+00:00

Hey Mike, thanks for the reply. Can you tell me how you did this? I am reading the JSON data from BLOB, however when I use it in the BODY, it wraps it in square brackets. Sounds easy, but I cannot work out how to remove the square brackets. They arent present when I look at the data in BLOB.

Corey Edwards 1 Reputation point

2022-03-31T21:59:59.61+00:00

Was there a solution to this? I am trying to pass a JSON array to the body of a web call in ADF.
Mike Wong 41 Reputation points

2022-03-31T22:15:44.617+00:00

Hello mate,

For my particular use case, I had to make my array URL safe and then it worked as intended.
Corey Edwards 1 Reputation point

2022-03-31T22:47:35.17+00:00

Hey Mike, thanks for the reply. Can you tell me how you did this? I am reading the JSON data from BLOB, however when I use it in the BODY, it wraps it in square brackets. Sounds easy, but I cannot work out how to remove the square brackets. They arent present when I look at the data in BLOB.

Answer 1

HimanshuSinha-msft 19,201 Microsoft Employee

Hello @Mike Wong ,
Thanks for the ask and using Microsoft Q&A platform
Can you please this expression and it should work ?

@markus.bohland@hotmail.de ('https://api-url.api.com/param?start=0&limit=10&sort=[{','''''param1''''' ,': ','''''subparam1''''',': ','''''param2''''',': ','''''subparam2''''','}]')

Please do let me know how it goes .
Thanks
Himanshu

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

Mike Wong 41 Reputation points

2021-09-23T08:12:37.37+00:00

Hello Himanshu,

Unfortunately, this doesn't appear to have worked. I am getting the following error:

Failure happened on 'Source' side. ErrorCode=RestSourceCallFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The HttpStatusCode 400 indicates failure. Request URL: https://api-url.com/param/param_again?start=0&limit=10&sort=[%7B''param1'':''subparam1'',''param2'':''subparam2''%7D] Response payload:<!doctype html><html lang="en"><head><title>HTTP Status 400 – Bad Request</title><style type="text/css">body {font-family:Tahoma,Arial,sans-serif;} h1, h2, h3, b {color:white;background-color:#525D76;} h1 {font-size:22px;} h2 {font-size:16px;} h3 {font-size:14px;} p {font-size:12px;} a {color:black;} .line {height:1px;background-color:#525D76;border:none;}</style></head><body><h1>HTTP Status 400 – Bad Request</h1></body></html>,Source=Microsoft.DataTransfer.ClientLibrary,'

Doesn't look like it's parsing the curly braces as a string and is percent encoding instead?

And another question around param1, subparam1, param2, and subparam2, these look like two ' put together instead of ". Would ADF parse these as two ' or "?

Thank you for your help btw!

Azure data Factory - passing in Json array/Object into web connector

1 answer

Activity