Splitting a JSON object

(0) Share

Report

Posted on by Hidayat_Md

Hello all,

I am trying to extract the exchange rate from CurrencyConverterAPI.com.

Is there a way I can split this from a JSON object into a key/value pair and store it in an excel table?

My desired output would be an excel table created and stored in onedrive/sharepoint.

Regards

Hidayat

Here is my flow:

Below is the sample output of the parse JSON.

I need the content of the rates.

{
 "success": true,
 "timestamp": 1636638677,
 "base": "EUR",
 "date": "2021-11-11",
 "rates": {
 "AED": 4.21072,
 "AFN": 105.088368,
 "ALL": 122.482133,
 "AMD": 545.149901,
 "ANG": 2.065012,
 "AOA": 684.248748,
 "ARS": 114.831972

 }
}

Below is the compose output after executing parse JSON..

{
 "AED": 4.21072,
 "AFN": 105.088368,
 "ALL": 122.482133,
 "AMD": 545.149901
}

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

Ellis Karim 11,681 Super User 2025 Season 2 on at

Like (1)

Report
What we can try and do is to transform the JSON data through a number of steps to a Key/Value pair that we can use to populate an Excel file:
(1) Stating with the output from the HTTP action:

(2) Extract the rates property:

(3) Transform each entry into an item, bounded by '{' and '}' . To use the replace function we need to work with string data type:

replace(replace(replace(string(outputs('ComposeRates')),'{"', '[{"'),'}','}]'),',"','},{"')

(4) Transform each item into a Key and Value pair:

replace(replace(outputs('ComposeItems'),':',',"Value":') ,'{','{"Key":')

(5) The data is still a string. Transform it to a JSON object:

json(outputs('ComposeKeyValues'))

(7) Finally output the JSON data to an Excel file:

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

Was this reply helpful? Yes No
Hidayat_Md 54 on at

Like (0)

Report
Hi @ekarim2020 ,

Super, the solution works perfectly!!!
Truly a resident rock star. 😀

Just an additional side question, is it possible to do filtering in the compose?
Would need to filter on 3 rates instead of getting 6 rates from the http.

{ "success": true, "timestamp": 1636638677, "base": "EUR", "date": "2021-11-11", "rates": { "AED": 4.21072, "AFN": 105.088368, "ALL": 122.482133 } }

Hidayat

Was this reply helpful? Yes No
Ellis Karim 11,681 Super User 2025 Season 2 on at

Like (1)

Report

To filter based on the currency code we can add two new actions and modify the Apply to each loop:

(1) We can use an array to list the currency codes we require to be filtered. The currency codes must match exactly the codes returned by the API. You can add or remove currencies without affect the logic of the flow:

(2) We use the Filter array action to filter the currency codes. The Filter Array action will loop through all the items in the ComposeJSON action, checking to see if the currency code is contained in our varCurrencyRatesRequired array:

(3) Finally we update the Apply to each loop to use the results returned by the Filter array action and write these to Excel:

Here is some sample output:
Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

Was this reply helpful? Yes No
Hidayat_Md 54 on at

Like (0)

Report

Thank you so much @ekarim2020 !! 👍👍👍👍

The solution works as expected. I have much to learn on PA. 🕺

Regards
Hidayat

Was this reply helpful? Yes No