Need help in using Lookup for fetching value in JSON

Srinivas Mehenderker Nath 86 Reputation points
2023-08-24T10:20:30.4033333+00:00

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
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,444 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Yann Nguyen 0 Reputation points
    2023-08-24T12:58:36.2833333+00:00

    Simply try something like:

    @activity('YourActivity').output.rates.USD


  2. Amira Bedhiafi 14,481 Reputation points
    2023-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.USD
    

    However, 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)

    0 comments No comments