Unanswered

How to iterate over json data?

(0) Share

Report

Posted on by benken10

Hi All

I am trying to create a very simple flow, that calls a foreign currency api, and adds the values into a sharepoint list. This is my first time using Power Automate.

I can get it to work, however it is inelegant, I am manually specifying each currency, I want to just be able to iterate over the api result, and it will automatically populate the currency field - ie I can add more currencies to the HTTP request and they will automatically flow into my sharepoint list.

I think I need to use a "apply to each" step, but when I try and use the "Rates" dynamics content, the flow fails with the error that "

The execution of template action 'Apply_to_each_2' failed: the result of the evaluation of 'foreach' expression '@items('Apply_to_each')' is of type 'Object'. The result must be a valid array.

This feels like it should be super easy but I'm missing something 😞 please help. The parse json schema is below, as is my current working Flow.

{

"type": "object",

"properties": {

"base": {

"type": "string"

"date": {

"type": "string"

"rates": {

"type": "object",

"properties": {

"EUR": {

"type": "number"

"USD": {

"type": "number"

}

"success": {

"type": "boolean"

"timestamp": {

"type": "integer"

}

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (0)

Djuuu 65 on at

Like (0)

Report

Could you be so kind and also provide the body if it is not confidential? (from the http step)

Thank you!

Was this reply helpful? Yes No
benken10 6 on at

Like (0)

Report
Yes of course 🙂

{ "statusCode": 200, "headers": { "Transfer-Encoding": "chunked", "Connection": "keep-alive", "CF-Ray": "7c7b806e68aab7f8-AMS", "Access-Control-Allow-Origin": "*", "CF-Cache-Status": "DYNAMIC", "cf-apo-via": "origin,host", "RateLimit-Limit": "250", "RateLimit-Remaining": "234", "RateLimit-Reset": "1336226", "X-Cloud-Trace-Context": "1274d9d94337b84f79cd79720595dd2d", "X-RateLimit-Limit-Day": "250", "X-RateLimit-Limit-Month": "250", "X-RateLimit-Remaining-Day": "244", "X-RateLimit-Remaining-Month": "234", "Report-To": "{\"endpoints\":[{\"url\":\"https:\\/\\/a.nel.cloudflare.com\\/report\\/v3?s=UzmkeFbZ4JMOkD6RLkChEmNuV5BNYNyO1c90IY71QG2D1Q804mxVKcYruScPTt1XWG4HWqQhzes5hZpKopHBRHzRPmY2l6CshU9t66W9tA9Gg0GboMe2S6tvY5GzpCyOM2g%3D\"}],\"group\":\"cf-nel\",\"max_age\":604800}", "NEL": "{\"success_fraction\":0,\"report_to\":\"cf-nel\",\"max_age\":604800}", "Vary": "Accept-Encoding", "alt-svc": "h3=\":443\"; ma=86400, h3-29=\":443\"; ma=86400", "Date": "Mon, 15 May 2023 12:49:34 GMT", "Server": "cloudflare", "Content-Type": "application/json", "Content-Length": "169" }, "body": { "success": true, "timestamp": 1684154944, "base": "GBP", "date": "2023-05-15", "rates": { "USD": 1.250985, "EUR": 1.150343 } } }

Was this reply helpful? Yes No
Djuuu 65 on at

Like (0)

Report

Thank you! Based on the JSON you posted, there is actually only one value & no array. I think that is just a call for one currency - can you please make a http request for more than one curreny and try it again? (or post the http output again here) 🙂

Was this reply helpful? Yes No
benken10 6 on at

Like (0)

Report

It's definitely a call for 2 currencies, if you look at body --> rates there is USD and EUR there. I just have no idea how to extract that in a proper way?

Was this reply helpful? Yes No
Djuuu 65 on at

Like (0)

Report
Alright, can you try this JSON Shema, which gives your an array for the rates - i think this is the solution you are looking for 🙂

{ "type": "object", "properties": { "base": { "type": "string" }, "date": { "type": "string" }, "rates": { "type": "array", "items": { "type": "object", "properties": { "currency": {"type": "string"}, "rate": {"type": "number"} }, "required": ["currency", "rate"] } }, "success": { "type": "boolean" }, "timestamp": { "type": "integer" } }, "required": ["base", "date", "rates", "success", "timestamp"] }

Was this reply helpful? Yes No
benken10 6 on at

Like (0)

Report

Unfortunately I get a an error on the parse JSON step, saying "Action 'Parse_JSON' failed"

Was this reply helpful? Yes No
Djuuu 65 on at

Like (0)

Report

Can you caopy&paste the whole content from the json validation from your screenshot?

Was this reply helpful? Yes No