Parsing JSON from Embedded Arrays - Help :)

[Tony Diaz]

I seem to be able to get one array worth to process, but I can't get any deeper than that.

Open the Sandbox3 file:
Select the two red buttons in the upper left to open a couple additional windows.
Press "Test Script" that is just below those two buttons in the upper left. =

In the "Moby Games Local Cache" window, 10 records will be created.

These 10 records are coming from the JSON data in the left most JSON blob field (JSON_Blob_Parse)

Each of the 10 records have mostly single field items, but there are three records within each of the 10 that have a further embedded array.

These are supposed to be created in a related table. This is what I'm having problems with.

The field "Genres_Array" is the data that should be populated into the related table (Moby_Genres_Meta). (Related by the field Game_ID)

I'm not getting

It should have 3 records as:

Moby_IDfk	Name 		Category_ID

2		Action		Basic Genres
2		Arcade		Gameplay		
2		License		Other Attributes

But I'm getting this: 

There's 4 records there, the 4th row, is the first one for the second record of the Moby Games Local Cache table.
The first three rows are the last record being repeated, then the 4th row showing is the last related record of 5 for the second record on Local Cache...

 

So, it's over all, processing the 10 arrays, creating 10 records. But it's only partially creating the embedded arrays in the "Genres_Array" field.

There are two other fields in there with arrays, they're not being done right now.  I'll worry about those after that 😉

 

The script that is doing it is in "New Scripts --> Test Script"

Sandbox3.fmp12

[comment]

Could you minimize the question to the problem you are having? If you have successfully extracted the first record  using:

JSONGetElement ( YourJSON ; "games[0]" )

then you should be able to extract the first genre using:

JSONGetElement ( $record ; "genres[0]" )

and from there it's:

JSONGetElement ( $genre ; "genre_category" )

to get the category ("Basic Genres", in the given example), and:

JSONGetElement ( $genre ; "genre_name" )

to get the name ("Action").

 

[Tony Diaz]

Here's the way the data is formatted.

https://drive.google.com/file/d/1FJCCf0yKNeEoWfVVOVwAILFoZEGhqJ_b/view?usp=sharing

 

The red columns are Single Records, there's 10 red lines.  That's the main Array that's being parsed.

 

Set Field [ Moby_Games_Cache::Genres_Array ; JSONGetElement ( $record ; "genres" ) ] 
#  Insert Array for Genres
Set Variable [ $GENRESARRAY ; Value: JSONGetElement ( $record ; "genres" ) ] 
If [ not IsEmpty ( $GENRESARRAY ) ] 
	Set Field [ Moby_Games_Cache::Field 12 ; $GENRESARRAY // JSON_Parse_Sandbox::Game_ID ] 
	Set Variable [ $x ; Value: ValueCount ( JSONListKeys ( $GENRESARRAY ; "" ) ) ] 
	Set Field [ Moby_Games_Cache::Field 13 ; $x ] 
	Go to Layout [ “Moby_Genres_Meta” (Moby_Genres_Meta) ; Animation: None ]
		Loop
			Set Variable [ $y ; Value: $y + 1 ] 
			Exit Loop If [ $y > $x ] 
			New Record/Request
			Set Variable [ $array ; Value: JSONGetElement ( $GENRESARRAY ; $x - 1) ] 
			Set Field [ Moby_Genres_Meta::Moby_IDfk ; JSONGetElement ( $record ; "game_id" ) ] 
			// Set Field [ Moby_Genres_Meta::Name ; $GENRESARRAY ] 
			Set Field [ Moby_Genres_Meta::Name ; JSONGetElement ( $array ; "genre_name" ) ] 
			Set Field [ Moby_Genres_Meta::Category ; JSONGetElement ( $array ; "genre_category" ) ] 
		End Loop
		Go to Layout [ “Moby_Games_Cache” (Moby_Games_Cache) ; Animation: None ]
	End If
	Set Field [ Moby_Games_Cache::Moby_Score ; JSONGetElement ( $record ; "moby_score" ) ] 
End Loop
end If

Within each record, there are four fields that are themselves an array: Those are the colored groups.

This sub-array I'm not having success with extracting within it's own loop. 

In the script above:

Set Field [ Moby_Games_Cache::Genres_Array ; JSONGetElement ( $record ; "genres" ) ]
This line gives me the $genres sub array for that individual record.
I'm assigning it to $GENRESARRAY just like the whole larger blob is assigned as $GAMESARRAY at the beginning of the script.

Then I'm doing the same thing, setting a variable, $x, to the amount of keys in $GENRESARRAY. This should happen once each time the larger array loops. It's appears to be happening just 1 and a half times.

...and $GAMESARRAY has 4 fields in it, of which I'm extracting two of them. Name and Category.  This is where it seems to be sort of working, mostly failing...

Then after that loop is done, it should continue on with the rest of the larger $GAMESARRAY. Which it does.

 

 

 

[bcooney]

https://www.geistinteractive.com/2018/03/13/filemaker-json-parsing-generator/
 

a tool that may be of help. 

47 minutes ago, Tony Diaz said:

Set Variable [ $array ; Value: JSONGetElement ( $GENRESARRAY ; $x - 1) ]

This should use the counter, $y, not $x to parse each array element. 

 

[Tony Diaz]

I've tried to get Generator to work..   and the BE plugin is installed.. I still get errors.

[Tony Diaz]

I can pick a file, but then there's never any tables to pick from.

If I enter the name of an existing table, it tells me the file isn't open.

I'm going through the video again now.

7 hours ago, bcooney said:

This should use the counter, $y, not $x to parse each array element. 

Hmm.. yup, I see that. I went over those and still missed that. Interestingly, changing it doesn't change a thing.   The line to get the amount of keys works, the proper chunk of code is in the $GENRESARRAY variable.. 

It's kinda like the layout is acting like a portal showing weird values due to funky relationship values.

 

I can pick the file..

But I never get a list of tables to choose from.

... entering anything, existent or not in that field, and I get

[jbrown]

5 hours ago, Tony Diaz said:

I can pick a file, but then there's never any tables to pick from.

If I enter the name of an existing table, it tells me the file isn't open.

I'm going through the video again now.

Hmm.. yup, I see that. I went over those and still missed that. Interestingly, changing it doesn't change a thing.   The line to get the amount of keys works, the proper chunk of code is in the $GENRESARRAY variable.. 

It's kinda like the layout is acting like a portal showing weird values due to funky relationship values.

 

I can pick the file..

But I never get a list of tables to choose from.

... entering anything, existent or not in that field, and I get

Howdy. you have to enable file access: File > Manage > Security > Advanced Settings  > File Access.

Either disable "require full access" (not recommended), or give access to the Generator file.

[Tony Diaz]

Okay, that takes care of that problem.
Though, Generator does help with the repetitive bits of setting up script steps and matching them up with their fields/tables.. (which is actually a pretty cool thing in itself!) but it does come across as over-complicated if you don't know whats going on first. Not looking a gift horse in the mouth at all, but at least in my case, it made -a lot- more sense what it's doing after I understand the steps needed to do what it's doing in the first place. It makes setting up the -next- one an absolute breeze ... because I don't have to do it one field at a time. Thank you for that! Between Generator and the Tool that I've got based on an earlier thread/reply from @comment .. I can both break down the data I'm working with easily and use Generator beside it to set up those repetitive steps. (declare variable to parse, write the line, goto the Table, create, Field, create/copy script Line, Assign/change  values, wash, rinse, repeat. ) ... that's just too cool.

But for working it out the first time, I did the fields/script lines/etc. all one at a time as I got one field working at a time.

---------

As for the issue of this thread, it appears to be the counter variable ($i / $y).

$i being the counter in the first loop, and $y is the counter in the sub-loop (embedded array).

$i maxes out at 10 as JSONListKeys returns 10 lines. 
So it does all those fields with flat values.
It then reaches the first JSON key that represents the first embedded array, and that JSONListKeys count is 3.
Using the -proper- incrementing value, this works, it goes through and creates the 3 related records for this field.
Reaches the end of that array, and exits the loop. ($x = 3 at this point)

... the rest of the fields in the initial loop finish, the loop exits ..

... and the whole thing starts over on the second of 10 records.

... the first part happens, (Lines 1-31, the inner loop starts again and the sub-array, which for this second record, it's got 4 keys. One more than the previous time around.  But .. it appears $y is still.. ($y = 3) it will exit the loop at line 34 on this second time around. 

Looks like I should insert $y=0 at line 42. 
Set Variable [ $y ; Value: 0 ]
$i will of course, never pass 10 before the entire script is done with, so that one will never have the same issue. As such, it all works with the added reset of $y to 0 at the end of the sub-loop. 

Now I can  go add in the other 3.. 

Plus the other piece of script to check if some of these values exist in the first place, to not create duplicate records in the value list side of things.

[comment]

If you are using nested loops, you must reset the inner loop's counter before or after calling the inner loop. Otherwise each instance of the inner loop will continue the count from the last value of the previous instance.

--
P.S. 

Quote

A common identifier naming convention is for the loop counter to use the variable names i, j, and k (and so on if needed), where i would be the most outer loop, j the next inner loop, etc. 

https://en.wikipedia.org/wiki/For_loop#Loop_counters

Edited July 17, 2020 by comment