Community!
I’m trying to iterate (for each) using logic apps and I want to be able able to set the KEY (Currency Code) as a variable and the Value (Currency Exchanged) as a variable.
I’m doing a parse json action using the body of the api call
{
"type": "array",
"items": {
"type": "object",
"properties": {
"AED": {
"type": "number"
},
"AFN": {
"type": "number"
},
"ALL": {
"type": "number"
},
"AMD": {
"type": "number"
},
"ANG": {
"type": "number"
},
"AOA": {
"type": "number"
},
"ARS": {
"type": "number"
},
"AUD": {
"type": "number"
}}}
Then I have the body of the parse JSON
[{
"AED": 2.473902,
"AFN": 47.309894,
"ALL": 60.509834,
"AMD": 260.86733,
"ANG": 1.213412,
"AOA": 620.003118,
"ARS": 642.565513,
"AUD": 1
}]
I just can’t for the life of me get the for each to work, does anyone have any ideas?
2
Another way to achieve it dynamically is to convert the JSON to XML and do it that way.
To break it down step by step …
Compose Data
This is merely your JSON …
{
"AED": 2.473902,
"AFN": 47.309894,
"ALL": 60.509834,
"AMD": 260.86733,
"ANG": 1.213412,
"AOA": 620.003118,
"ARS": 642.565513,
"AUD": 1
}
Compose XML
We convert the JSON to XML. You have to create a root element though, it’s not a straight conversion.
This is the expression …
xml(json(concat('{ "root": ', string(outputs('Compose_Data')), '}')))
Compose Currencies
We now need to build up the array of all currency codes. We do that by applying an XPath expression to extract them all.
xpath(outputs('Compose_XML'), '/root/*')
For each
The expression to loop through each currency is …
outputs('Compose_Currencies')
Compose Element
The data we receive back is formatted as base64 so we need to convert the item to a string first. This expression does that …
xml(decodeBase64(item()['$content']))
Compose Currency Code
This will extract the name of the element which is the currency code.
xpath(outputs('Compose_Element'), 'name(*)')
Compose Currency Value
This will extract the value of the element.
first(xpath(outputs('Compose_Element'), concat('/', outputs('Compose_Currency_Code'), '/text()')))
That will do it for you.
This is the JSON definition of that flow …
{
"definition": {
"$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
"contentVersion": "1.0.0.0",
"triggers": {
"manual": {
"type": "Request",
"kind": "Http"
}
},
"actions": {
"Compose_Data": {
"type": "Compose",
"inputs": {
"AED": 2.473902,
"AFN": 47.309894,
"ALL": 60.509834,
"AMD": 260.86733,
"ANG": 1.213412,
"AOA": 620.003118,
"ARS": 642.565513,
"AUD": 1
},
"runAfter": {}
},
"Compose_XML": {
"type": "Compose",
"inputs": "@xml(json(concat('{ "root": ', string(outputs('Compose_Data')), '}')))",
"runAfter": {
"Compose_Data": [
"Succeeded"
]
}
},
"Compose_Currencies": {
"type": "Compose",
"inputs": "@xpath(outputs('Compose_XML'), '/root/*')",
"runAfter": {
"Compose_XML": [
"Succeeded"
]
}
},
"For_each": {
"type": "Foreach",
"foreach": "@outputs('Compose_Currencies')",
"actions": {
"Compose_Currency_Code": {
"type": "Compose",
"inputs": "@xpath(outputs('Compose_Element'), 'name(*)')",
"runAfter": {
"Compose_Element": [
"SUCCEEDED"
]
}
},
"Compose_Element": {
"type": "Compose",
"inputs": "@xml(decodeBase64(item()['$content']))"
},
"Compose_Currency_Value": {
"type": "Compose",
"inputs": "@first(xpath(outputs('Compose_Element'), concat('/', outputs('Compose_Currency_Code'), '/text()')))",
"runAfter": {
"Compose_Currency_Code": [
"SUCCEEDED"
]
}
}
},
"runAfter": {
"Compose_Currencies": [
"SUCCEEDED"
]
}
}
},
"outputs": {},
"parameters": {
"$connections": {
"type": "Object",
"defaultValue": {}
}
}
},
"parameters": {
"$connections": {
"value": {}
}
}
}
Assumption 1: There are 8 currency codes (AED, AFN, ALL, AMD, ANG, AOA, ARS, AUD) fixed in your JSON schema.
Assumption 2: The iteration using For each is indeed required 😊
Solution: You need to iterate array [ "AED", "AFN", "ALL", "AMD", "ANG", "AOA", "ARS", "AUD" ].
See full code below – to simplify this example, I assumed that there is only 1 object in the parsed JSON array received from the API call. If required, it is easy to adapt the solution to deal with multiple objects in the array, using an additional For each loop.
{
"type": "Foreach",
"foreach": [
"AED",
"AFN",
"ALL",
"AMD",
"ANG",
"AOA",
"ARS",
"AUD"
],
"actions": {
"Set_variable_CurrencyExchanged": {
"type": "SetVariable",
"inputs": {
"name": "CurrencyExchanged",
"value": "@outputs('Parse_JSON')['body']?[0]?[item()]"
},
"runAfter": {
"Set_variable_CurrencyCode": [
"Succeeded"
]
}
},
"Set_variable_CurrencyCode": {
"type": "SetVariable",
"inputs": {
"name": "CurrencyCode",
"value": "@item()"
}
}
},
"runAfter": {
"Parse_JSON": [
"Succeeded"
]
}
}
Result of the first iteration:
Result of the second iteration:
– and so on.
If you want it to be fully dynamic, you can use the Json Properties to Name Value Pair Array operation in the Advanced Data Operations connector.
It will turn this …
{
"AED": 2.473902,
"AFN": 47.309894,
"ALL": 60.509834,
"AMD": 260.86733,
"ANG": 1.213412,
"AOA": 620.003118,
"ARS": 642.565513,
"AUD": 1
}
… into this …
[
{
"propertyName": "AED",
"propertyType": "Float",
"propertyValue": 2.473902
},
{
"propertyName": "AFN",
"propertyType": "Float",
"propertyValue": 47.309894
},
{
"propertyName": "ALL",
"propertyType": "Float",
"propertyValue": 60.509834
},
{
"propertyName": "AMD",
"propertyType": "Float",
"propertyValue": 260.86733
},
{
"propertyName": "ANG",
"propertyType": "Float",
"propertyValue": 1.213412
},
{
"propertyName": "AOA",
"propertyType": "Float",
"propertyValue": 620.003118
},
{
"propertyName": "ARS",
"propertyType": "Float",
"propertyValue": 642.565513
},
{
"propertyName": "AUD",
"propertyType": "Integer",
"propertyValue": 1
}
]
… you can then loop over that array and do whatever you need with the data.
2
So what I ended up doing was using a simple JavaScript code using Inline Code and it worked perfectly!
// Input JSON data
var data = workflowContext.actions.Parse_JSON.outputs.body
// Initialize array to store key-value pairs
var keyValuePairs = [];
// Check if data is not empty
if (data.length > 0) {
var currencies = data[0]; // Since there's only one object in the array
for (var key in currencies) {
if (currencies.hasOwnProperty(key)) {
keyValuePairs.push({ key: key, value: currencies[key] });
}
}
}
// Return the key-value pairs
return keyValuePairs;