Using repeat step on nested JSON response

[Evaldas]

Hello,
I am not sure whether it’s my head that is being frazzled and I’m missing something obvious, or is it actually something that requires more tinkering with, but I am struggling with inserting nested JSON data in database. I have used Set Value to bind array data needed for extraction, which works fine and displays data as intended, however, since the data is nested and returned as increment key array, I am unable to repeat database insert without targeting array values directly ([0],[1], etc), I have searched the forums and there seemed to be quite a few similar cases, yet, they were different enough for my the solutions that I’ve tried not to work. I have tried repeat stacking, but since original repeat returns no values, there’s not use of it. I have tried targeting only array_list.values through the repeat action, but yet again, no results. I am sure there’s an easy way to repeat that information, but I can’t seem to get my head round it.

Set Value and repeat results can be seen here, as you can tell, repeat action recognises the amount of array items there are, however, as mentioned before i am unable to define schema as they’re generated in an increment keys.

With server action looking like this:

Any help is much appreciated!

[Apple]

@Evaldas

since the data is nested and returned as increment key array, I am unable to repeat database insert without targeting array values directly ([0],[1], etc)

Please explain this again, but slowly

Also expand a JSON item so people can look into it

Also show your Database insert so people can see what you’re trying to do

Your text is too confusing

[Evaldas]

Apologies if I have crammed too much into short paragraph.

Basically I am trying to insert details of this JSON information into database. Json path to nested values looks like this
jsonData.GetMemberMessageResponse.MemberMessage.MemberMessageExchange

Expanded JSON item looks like this:

My database insert looks like this:

array_list is just a set value of JSON path to the …MemberMessageExchange to make it easier for debugging.

However, repeat action returns only the key count, and not the values.

Altho, the fact that it returns correct amount of keys, makes me think that I’m rather close

[bpj]

It is returning nothing because you have nothing set to output in the repeat. The repeat itself can be set to include or exclude fields. Include means it will only output those that you specify - no field chosen means nothing gets output.

The other steps, inside the repeat, are not set to output either hence the empty items in the repeat.

Have you looked in the DB to see if the data is actually being inserted?

[Evaldas]

Oh okey, I see… I’ll have a look into output fields
Database insert actions inserts correct amount of records but with empty columns, my best guess this is a result of incorrect data binding…

[Evaldas]

As expected, since I haven’t had a chance of working with objects before, it took me longer than it should have had. The solution was very simple and the one that makes sense… I had to call the value first so the database insert action knows what to insert {{$value.Item.ItemID}}

[Hyperbytes]

You seem to be making this unnecessarily complex.

Assuming the API call returns the JSON response you are working with, then simply access the response directly via the picker which should show the reponse schema ( you did remember the generate the schema?)
Create your repeat, navigate with the picker to the reponses returned by the API call and pick the MemberMessageExchange array as the base of your repeat
Once done you should see the items in the array available in the picker from you to manipulate in whatever way you want.
No need for intermediate set to steps.

[Evaldas]

Hey, unfortunately I’m unable to get the data directly from API call as the data returned is in XML format, hence the JS script that converts it from XML to JSON, therefore I have to repeat javascript response, and datapicker doesn’t know what values are what. i’ve tried using Set Value to acquire array and build schema there, however the schema starts with object key and I was unable to map it, not even with $key or $value (guess it’s because schema builder takes them as string )

[Hyperbytes]

just an idea but have you tried “create array list” under Data Management which allows you to build a schema?

[Apple]

@Evaldas

Hey, unfortunately I’m unable to get the data directly from API call as the data returned is in XML format, hence the JS script that converts it from XML to JSON, therefore I have to repeat javascript response, and datapicker doesn’t know what values are what

True, easiest option (but not the most reliable) would be to use that PHP script that converted XML to JSON so you could let Wappler infer the schema for that. Maybe you could modify that script to accept a $_POST instead of $_GET

Or, if you’re already good with the current solution, that’s ok!

[Evaldas]

Hey, actually binding values directly through $value expression worked out well! I have tried mapping structure inside of “create array” element, but resulted to the same thing

@Apple ended up using the JSON parsing through that xml to JSON script as it uses post method rather than get, php script can only handle 3 or 4 nested response at a time, where post response returns 200 records at the time, with possibility of paging them too! So worked out better in my case, however, appreciate all the effort you have put with the PHP script