Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,582 questions
Simply try something like:
@activity('YourActivity').output.rates.USD
Need help in using Lookup for fetching value in JSON
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(83.2, 20%, 18%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESM%3C/text%3E%3C/svg%3E)
Srinivas Mehenderker Nath
86
Reputation points
Hi,
I am trying to use lookup in ADF where I need the value of USD in below JSON which I am getting as response from REST API:
{"success":true,"timestamp":1692866883,"base":"GBP","date":"2023-08-24","rates":{"USD":1.269479}}
I am trying to use the "@activity" function in expression builder to get to the value of USD key value, but this showing as invalid.
Kindly share the right approach to get the value of the JSON key "USD" in above.
Regards
Azure Data Factory
{count} votes
-
AnnuKumari-MSFT 34,546 Reputation points Microsoft Employee Moderator2023-08-25T08:04:47.8733333+00:00 Hi Srinivas Mehenderker Nath ,
Just checking if you got a chance to try the below suggested approach and see if it works ? In case it does, kindly accept the answer by clicking on
Accept answerbutton. Thankyou -
AnnuKumari-MSFT 34,546 Reputation points Microsoft Employee Moderator
2023-08-29T06:05:17.0166667+00:00 It seems you are using lookup transformation in dataflow and not the lookup activity in ADF pipeline. In case you have to perform transformations on the source data, please go ahead with the approach suggested in this post. In case you just need to fetch out the USD column , and do not need to perform any further transformations, then you can try to directly use ADF pipeline.
Use lookup activity and point the dataset to the rest API and then use set variable activity to get the value in USD column using this expression :
@activity('YourActivity').output.rates.USDHere is the details on lookup activity and lookup transformation:
Lookup transformations in mapping data flow
Hope it helps. Please let us know how it goes. Thankyou
Sign in to comment
2 answers
Sort by: Most helpful
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(44.800000000000004, 87%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYN%3C/text%3E%3C/svg%3E)
Yann Nguyen 0 Reputation points2023-08-24T12:58:36.2833333+00:00 -
Srinivas Mehenderker Nath 86 Reputation points
2023-08-24T13:42:49.7833333+00:00 When I try to add this @activity it is not in the expression building list. Is there any way to add this function?
-
Yann Nguyen 0 Reputation points
2023-08-25T08:14:19.15+00:00 As Amira mentionned, in your set variable activity "GetUSDRate" you must connect it to the output of your web activity calling your web API. In this kind of scenario and for commodity, I usually use a copy activity from REST API source to csv sink. In the mapping section, you can visually select your information in the json response.
Sign in to comment -
-
Amira Bedhiafi 32,756 Reputation points Volunteer Moderator2023-08-24T21:55:37.68+00:00 You can extract the value of the "USD" key in the given JSON using the ADF expression language. I assume you've assigned the JSON object to a variable or have it in an output from a previous activity, you would typically use an expression like the following:
@activity('YourPreviousActivityName').output.success.rates.USDHowever, the expression language doesn't allow direct access to JSON properties that use an unsupported character, like a numeric character at the start of the property name (as it is the case with "1692866883" in your JSON). Since you are only interested in the "USD" key, it shouldn't be a problem.
I added the .success becauce it will accesse the "success" property in the JSON object. (same for the others)