Answered

Parsing JSON different within a loop

(0) Share

Report

Posted on by helspaul

Hi,

I've constructed a flow which on a weekly basis gets JSON from an API, initialises a variable of the JSON, parses that JSON, and then for each row in the data populates a new line in a SQL database. This works as expected and populates all rows to my database

The API has a record limit so i'd like to loop through and get multiple pages. Ideally I'd do this until the record count = 0 but I thought i'd start off simple and just loop through a set number of times. I initalise two variables, then start a Do Until loop. Here I call my API, write the JSON to one of the variable then parse the JSON. The sample JSON used to configure the parser is exactly the same as the non looping version.

When I get to writing the data to the database my flow fails, It says all records are null, there is no data and nothing can be written. I can see the JSON data in the Raw Output view of the run

Do i need to do something different to my JSON call, variable, parser or writer because I'm using them in a loop?

Thanks for any help

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (1)

Sort by

SudeepGhatakNZ 14,396 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Are you able to provide your sample data and the JSON Schema?

Was this reply helpful? Yes No
helspaul 10 on at

Like (0)

Report
Copy link

Link copied!
Thanks for your response. I've attached
projects.json - a sample of the data pulled using postman. I've limited the pagesize as some of the data is sensitive
schema.json - this is what power automate generated when given an extract of data from postman
The single flow and looping flow point to the same URL and were given the same sample data to generate the schema. Thanks for assisting me with this

Examples.zip

Was this reply helpful? Yes No
SudeepGhatakNZ 14,396 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@helspaul

I see nothing wrong with your JSON schema or the data. What is the error you are getting?

Was this reply helpful? Yes No
helspaul 10 on at

Like (0)

Report
Copy link

Link copied!

Thanks for replying.

When I run a straight forward 'Apply to Each' no error

When I run the same inputs inside a 'Do Until' I get

ExpressionEvaluationFailed. The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@body('Parse_JSON_2')?['properties']?['projects']' is of type 'Null'. The result must be a valid array.

The 'Do Until' seems to work. But the 'Apply To each' fails. I can see from the Run History that the output of the JSON Parser is identical. I've kept the variable names the same so in theory i'm using them in the same way.

Not sure why there should be any difference in behaviour

Was this reply helpful? Yes No
Chriddle 8,697 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

properties is not a property of your object.

body('Parse_JSON_2')~~?['properties']?~~['projects']

Whenever you get an error message like this (or don't expect objects being null)

the result of the evaluation of 'foreach' expression '@body('Parse_JSON_2')?['properties']?['projects']' is of type 'Null'.

remove the question marks.

With body('Parse_JSON_2')['properties']['projects'] the error message is more meaningfull.

Was this reply helpful? Yes No
Verified answer

SudeepGhatakNZ 14,396 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
@helspaul ,

I see the issue now.

Apply to each works on arrays. Your JSON contains an object

{
"projects":[]
}

To work with arrays, you can access the array from the provided JSON object using outputs('Compose_3')['projects']. Once you have the array, you can use it in apply to each and extract properties by specifying item()['name'], etc. There's no need to parse JSON explicitly in this case.

Was this reply helpful? Yes No
helspaul 10 on at

Like (0)

Report
Copy link

Link copied!

Thank you - that has done the job 👍

Was this reply helpful? Yes No
helspaul 10 on at

Like (0)

Report
Copy link

Link copied!

Sorry, follow up question if I may

What syntax do I need to access the tags? I need to produce a lookup table which has Project ID and Tag ID. One project can have multiple tags and i've been trying to use 'apply to each' within the current 'apply to each' but with no success. Thank

Was this reply helpful? Yes No