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

Mike Wong 41 Reputation points
2021-09-22T14:46:56.247+00:00

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.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,619 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. HimanshuSinha-msft 19,381 Reputation points Microsoft Employee
    2021-09-22T20:59:48.52+00:00

    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 130616-image.png or upvote 130671-image.png 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