Parsing JSON into Variables (?!?!)

[Tony Diaz]

I'm just not getting my head wrapped around this.  .. parsing JSON 'properly' as opposed to doing it like scraping.

My incoming data is as such:

{"ISBN:9780830631292": {"publishers": [{"name": "Windcrest"}], "pagination": "xi, 451 p. :", "identifiers": {"lccn": ["88039223"], "openlibrary": ["OL2059910M"], "isbn_10": ["0830631291"], "goodreads": ["3532499"]}, "classifications": {"dewey_decimal_class": ["005.4/469"], "lc_classifications": ["QA76.76.O63 C365 1989"]}, "title": "Inside ProDOS 16", "url": "https://openlibrary.org/books/OL2059910M/Inside_ProDOS_16", "notes": "Bibliography: p. 442-445.\nIncludes index.", "number_of_pages": 451, "cover": {"small": "https://covers.openlibrary.org/b/id/9397291-S.jpg", "large": "https://covers.openlibrary.org/b/id/9397291-L.jpg", "medium": "https://covers.openlibrary.org/b/id/9397291-M.jpg"}, "subjects": [{"url": "https://openlibrary.org/subjects/prodos", "name": "ProDOS"}], "publish_date": "1989", "key": "/books/OL2059910M", "authors": [{"url": "https://openlibrary.org/authors/OL731337A/Campbell_J._L.", "name": "Campbell, J. L."}], "by_statement": "John Campbell.", "publish_places": [{"name": "Blue Ridge Summit, PA"}]}}

So I want to get that first key value from it. Using my script steps from scraping:

Set Field [ ISBN::webPage Content ; $text ] 
Set Variable [ $prefix ; Value: "{\"ISBN:" ] 
Set Variable [ $suffix ; Value: "\": {" ] 
Set Variable [ $start ; Value: Position ( $text ; $prefix ; 1 ; 1 ) ] 
If [ $start ] 
Set Variable [ $key ; Value: Let ( [ start = $start + Length ( $prefix ) ; end = Position ( $text ; $suffix ; start ; 1 ) ] ; Middle ( $text ; start ; end - start ) ) ] 
Set Variable [ $key ; Value: Trim( Substitute( TrimAll($key; 0;0 ) ; [Char(10); ""] ; ["'"; ""] )) ] 
Set Field [ ISBN::ISBN_Title ; $key ] 
End If

That works if the key name is unique. But if it's not, the proper way is to drill down the values. So, what I would really like to do is use JSONListKeys to get that value.

I'm having trouble trying to turn this stuff into variables

Another thread finally got me through JSON arrays.  (Thanks @comment !)

Now, what to do with them.

So I know that JSONListKeys will get me the answer.

Set Variable [ $key ; Value: JSONListKeys ( ISBN::webPage Content ; // What the heck to I put here? ) ] 
Set Field [ ISBN::ISBN_MSRP ; $key2 ] 

I want to make it be $key so that I can then use JSONGetElement on $key&".title"

Using JSONListKeys on $key isn't absolutely necessary because I know what those possibilities are, but where there is an array involved, most of the time there is only one value so I can just say JSONGetElement on $key & .publishers[0]name and get what I want. But if there's more, being able to properly form that for additional fields. But one thing at a time.

JSONGetElement( ISBN::webPage Content;  $key & ".publish_date" ) .. is bogus syntax. But that's all I can come up with.

 

[comment]

So what exactly are you trying to extract from this JSON?

[Guest tdiaz]

Title, ISBN 10, 13, publisher, pages, pagination, OL numbers.. 

The publishers, authors and chapters may have arrays in them, that's the one where I know I want the [0] one at least, but the rest... not sure what to to do there, other than it sounds like a related table to put additional pairs from any arrays..

[comment]

This is not a good example, because none of the arrays that may contain multiple objects does.

 

Getting the single values is trivial. For convenience, start by setting a $innerJSON variable to:

JSONGetElement ( $json ; JSONListKeys ( $json ; "" ) ) 

Then extract the title by:

JSONGetElement ( $innerJSON ; "title" )

pages by:

JSONGetElement ( $innerJSON ; "number_of_pages" )

and so on.

 

Back to the arrays: you are right* in thinking that the proper procedure would be to set a $publishers variable to:

JSONGetElement ( $innerJSON ; "publishers" )

and another variable to TitleID (or the ISBN?), then go to the Publishers table and create a related record for each value in the list obtained by:

JSONListKeys ( $publishers ; "" )

 

---
(*) At least partially: you want to put all publishers in the related table, not just "additional" ones.

 

Edited May 28, 2020 by comment

[Wim Decorte]

20 minutes ago, comment said:

Back to the arrays: you are right* in thinking that the proper procedure would be to set a $publishers variable to:

JSONGetElement ( $innerJSON ; "publishers" )

and another variable to TitleID (or the ISBN?), then go to the Publishers table and create a related record for each value in the list obtained by:

JSONListKeys ( $publishers ; "" )

 

 

JSONListKeys on an array will give you a list of the indexes however, not the values.

And in this particular JSON, "publishers" is an array of JSON objects.  So to get the name of the publishers you'd have to iterate over the array and ask for the name key of each array element:

 

[0].name

[1].name

... and so on

[comment]

24 minutes ago, Wim Decorte said:

JSONListKeys on an array will give you a list of the indexes however, not the values.

Yes, of course. Not sure where the "however" comes in. You loop over the keys to get the values.

Oh, I see: when I said "each value in the list" I meant "value" in the Filemaker sense - i.e. an item in a return-separated list. Not "value" in JSON sense - i.e. the right-hand side of a name/value pair.

You need to do:

 ValueCount ( JSONListKeys ( $publishers ; "" ) )

so in that sense the keys are values.

 

[bcooney]

This blog and its demo might be helpful. Might even be the same api. 
http://filemakerandapis.com

[Tony Diaz]

So, I've worked out this on the other bits so far.

Sample Data:

{"ISBN:9780980200447": {"publishers": [{"name": "Litwin Books"}], "pagination": "80p.", "identifiers": {"google": ["4LQU1YwhY6kC"], "lccn": ["2008054742"], "openlibrary": ["OL22853304M"], "isbn_13": ["9780980200447", "9781936117369"], "amazon": ["098020044X"], "isbn_10": ["1936117363"], "oclc": ["297222669"], "goodreads": ["6383507"], "librarything": ["8071257"]}, "table_of_contents": [{"title": "The personal nature of slow reading", "label": "", "pagenum": "", "level": 0}, {"title": "Slow reading in an information ecology", "label": "", "pagenum": "", "level": 0}, {"title": "The slow movement and slow reading", "label": "", "pagenum": "", "level": 0}, {"title": "The psychology of slow reading", "label": "", "pagenum": "", "level": 0}, {"title": "The practice of slow reading.", "label": "", "pagenum": "", "level": 0}], "links": [{"url": "http://johnmiedema.ca", "title": "Author's Website"}, {"url": "http://litwinbooks.com/slowreading-ch2.php", "title": "Chapter 2"}, {"url": "http://www.powells.com/biblio/91-9781936117369-0", "title": "Get the e-book"}], "weight": "1 grams", "title": "Slow reading", "url": "https://openlibrary.org/books/OL22853304M/Slow_reading", "classifications": {"dewey_decimal_class": ["028/.9"], "lc_classifications": ["Z1003 .M58 2009"]}, "notes": "Includes bibliographical references and index.", "number_of_pages": 92, "cover": {"small": "https://covers.openlibrary.org/b/id/5546156-S.jpg", "large": "https://covers.openlibrary.org/b/id/5546156-L.jpg", "medium": "https://covers.openlibrary.org/b/id/5546156-M.jpg"}, "subjects": [{"url": "https://openlibrary.org/subjects/books_and_reading", "name": "Books and reading"}, {"url": "https://openlibrary.org/subjects/reading", "name": "Reading"}], "publish_date": "March 2009", "key": "/books/OL22853304M", "authors": [{"url": "https://openlibrary.org/authors/OL6548935A/John_Miedema", "name": "John Miedema"}], "by_statement": "by John Miedema.", "publish_places": [{"name": "Duluth, Minn"}], "ebooks": [{"checkedout": false, "formats": {}, "preview_url": "https://archive.org/details/slowreading00mied", "borrow_url": "https://openlibrary.org/books/OL22853304M/Slow_reading/borrow", "availability": "borrow"}]}}

 

# Populate ISBN::JSON_Blob from URL query
Insert from URL [ Select ; With dialog: Off ; Target: ISBN::JSON_Blob ; "https://openlibrary.org/api/books?bibkeys=ISBN:" & ISBN::ISBN_Search & "&jscmd=data&format=json" ]
Set Variable [ $key1 ; Value: JSONListKeys ( ISBN::JSON_Blob ; "" ) ]
Set Field [ ISBN::ISBN_Key1 ; $key1 ]
#
# Populate ISBN::webPage Content with page source of web viewer.
Set Variable [ $source ; Value: GetLayoutObjectAttribute ( "OLJSON" ; "content" ) ]
Set Field [ ISBN::webPage Content ; $source ]
Set Variable [ $prefix ; Value: "{\"ISBN:" ]
Set Variable [ $suffix ; Value: "\": {" ]
Set Variable [ $start ; Value: Position ( $source ; $prefix ; 1 ; 1 ) ]
If [ $start ]
Set Variable [ $key2 ; Value: Let ( [ start = $start + Length ( $prefix ) ; end = Position ( $source ; $suffix ; start ; 1 ) ] ; Middle ( $source ; start ; end - start ) ) ]
Set Variable [ $key2 ; Value: Trim( Substitute( TrimAll($key2; 0;0 ) ; [Char(10); ""] ; ["'"; ""] )) ]
Set Field [ ISBN::ISBN_Key2 ; $key2 ]
End If
#
# ——————————————————————————————————————————————————————————————
# PARSE JSON INTO VARIABLES
# ——————————————————————————————————————————————————————————————
#
Set Variable [ $isbn_10 ; Value: JSONGetElement ( ISBN::JSON_Blob ; $key1 & ".identifiers.isbn_10" ) ]
Set Variable [ $isbn_13 ; Value: JSONGetElement ( ISBN::JSON_Blob ; $key1 & ".identifiers.isbn_13" ) ]
Set Variable [ $openlibrary ; Value: JSONGetElement ( ISBN::JSON_Blob ; $key1 & ".identifiers.openlibrary" ) ]
#
# ——————————————————————————————————————————————————————————————
# SET INTO FIELDS
# ——————————————————————————————————————————————————————————————
#
If [ $isbn_10 ]
Set Variable [ $isbn_10 ; Value: Trim( Substitute( TrimAll($isbn_10; 0;0 ) ; [Char(10); ""] ; ["'"; ""] ; ["["; ""] ; ["]"; ""] ; ["\""; ""] )) ]
Set Field [ ISBN::ISBN_10 ; $ISBN_10 ]
End If
#
If [ $isbn_13 ]
Set Variable [ $isbn_13 ; Value: Trim( Substitute( TrimAll($isbn_13; 0;0 ) ; [Char(10); ""] ; ["'"; ""] ; ["["; ""] ; ["]"; ""] ; ["\""; ""] )) ]
Set Field [ ISBN::ISBN_Title ; $ISBN_13 ]
End If
#
If [ $openlibrary ]
Set Variable [ $openlibrary ; Value: Trim( Substitute( TrimAll($openlibrary; 0;0 ) ; [Char(10); ""] ; ["'"; ""] ; ["["; ""] ; ["]"; ""] ; ["\""; ""] )) ]
Set Field [ ISBN::ISBN_OpenLibrary ; $openlibrary ]
End If

So, next is the array...

19 hours ago, comment said:

(*) At least partially: you want to put all publishers in the related table, not just "additional" ones.

Yes, but I do want the first in the main table also. I suppose that can be done via calculation in the field though. Like it were viewing a portal with one row.

Or I can always just look for the [0] entry (e.g. ISBN:9780980200447.table_of_contents[0].name )

Can you give me a starter example?

As for the related table, I'm presuming that relationship should be based on multiple values if I want to put all the arrayed values into a single related table. (authors, publishers, table of contents.. ) etc. each have possible arrays. Or a single related table for each array type with just the relation based on the initial key value.

 

 

[comment]

These are general database structure questions, unrelated to to the problem of how to parse JSON to populate such structure with data.

There is no good reason to put all arrayed values in a single related table. They don't share the same fields and they do not represent the same entity. Arguably you could combine authors and publishers into one table of contributors and use a Role field to tell them apart. But I cannot imagine a situation where you would search a table and expect to see results comprising of both authors and chapter titles.

The relationship between the parent table of titles and its child tables should be based on a meaningless TitleID and nothing else. This can be an auto-entered serial number, a UUID or even the ISBN itself.

I also see no good reason to duplicate the values of the first child record in the parent table. A calculation would be necessary if you wanted to calculate something using these values. For display only, you can simply place fields from the child table on the parent layout. These will display data from the first related record in the child table (first, according to the sort order defined for the relationship).

 

[Tony Diaz]

Fair enough on the relationship setup. While it seems like "more" is more complex, I see where it's actually not. Just needed to talk it out.  Right now, table has a meaningless ID and the ISBN, though I'm just going to use the meaningless one for the relation so I don't have to worry about the circumstance where I have a record that is a duplicate.  My DB is for tracking individual items / lots so I could have multiple of the same identifier, that I want to track separately. My FM experience over all these years has been flat files. Single table, with lots of records, kind of like a front end for an Excel file.

Anyhow..  arrays. Lets see what I can come up with.. how to apply it in FM compatible syntax.

[Wim Decorte]

Talking about Publishers:

3 hours ago, Tony Diaz said:

Yes, but I do want the first in the main table also. I suppose that can be done via calculation in the field though. Like it were viewing a portal with one row.

If you were to go with a calculation on the main table to show the first (main) publisher then it would become an unstored calculation by default since it references a related record.

And that's how solutions become do slow over time; same argument as in the other thread.  It's pretty much a given that you'll want to search on that publisher field on the main table, which is a search on an unstored calc, meaning that you'll make FM calculate that field for all records so that it can perform that search.

If you do yourself one big favor in all these small lessons: stop thinking 'calculated field'.  Break the habit of thinking of calc field first.

In this case, showing the first publisher is a simple as putting the related field on the layout.  Don't even need a portal.  Unless you have a sort on the relationship, the first related record is the oldest.

And you can search directly on that field.  If you are not familiar with related searches then experiment a bit with it until you are comfortable with it.

 

[Tony Diaz]

{"ISBN:9780980200447": {"info_url": "https://openlibrary.org/books/OL22853304M/Slow_reading", "bib_key": "ISBN:9780980200447", "preview_url": "https://archive.org/details/slowreading00mied", "thumbnail_url": "https://covers.openlibrary.org/b/id/5546156-S.jpg", "details": {"identifiers": {"amazon": ["098020044X"], "google": ["4LQU1YwhY6kC"], "librarything": ["8071257"], "goodreads": ["6383507"]}, "table_of_contents": [{"level": 0, "type": {"key": "/type/toc_item"}, "title": "The personal nature of slow reading"}, {"level": 0, "type": {"key": "/type/toc_item"}, "title": "Slow reading in an information ecology"}, {"level": 0, "type": {"key": "/type/toc_item"}, "title": "The slow movement and slow reading"}, {"level": 0, "type": {"key": "/type/toc_item"}, "title": "The psychology of slow reading"}, {"level": 0, "type": {"key": "/type/toc_item"}, "title": "The practice of slow reading."}], "weight": "1 grams", "covers": [5546156], "local_id": ["urn:sfpl:31223095026424"], "lc_classifications": ["Z1003 .M58 2009"], "latest_revision": 22, "ocaid": "slowreading00mied", "contributors": [{"role": "Cover Photographs", "name": "C. Ekholm"}], "source_records": ["marc:marc_loc_updates/v37.i01.records.utf8:4714764:907", "marc:marc_loc_updates/v37.i24.records.utf8:7913973:914", "marc:marc_loc_updates/v37.i30.records.utf8:11406606:914", "ia:slowreading00mied", "marc:marc_openlibraries_sanfranciscopubliclibrary/sfpl_chq_2018_12_24_run04.mrc:135742902:2094"], "title": "Slow reading", "languages": [{"key": "/languages/eng"}], "subjects": ["Books and reading", "Reading"], "publish_country": "mnu", "by_statement": "by John Miedema.", "oclc_numbers": ["297222669"], "type": {"key": "/type/edition"}, "physical_dimensions": "7.81 x 5.06 x 1 inches", "revision": 22, "publishers": ["Litwin Books"], "description": "\"A study of voluntary slow reading from diverse angles\"--Provided by publisher.", "physical_format": "Paperback", "last_modified": {"type": "/type/datetime", "value": "2019-07-16T22:44:09.608703"}, "key": "/books/OL22853304M", "authors": [{"name": "John Miedema", "key": "/authors/OL6548935A"}], "publish_places": ["Duluth, Minn"], "pagination": "80p.", "classifications": {}, "created": {"type": "/type/datetime", "value": "2009-01-07T22:16:11.381678"}, "lccn": ["2008054742"], "notes": "Includes bibliographical references and index.", "number_of_pages": 92, "isbn_13": ["9780980200447", "9781936117369"], "dewey_decimal_class": ["028/.9"], "isbn_10": ["1936117363"], "publish_date": "March 2009", "works": [{"key": "/works/OL13694821W"}]}, "preview": "borrow"}}

From the above JSON blob, :

ISBN:9780980200447.details.table_of_contents
                         $key & ".details.table_of_contents"

Gives me:

JSONcListKeys shows me the array has 5 elements.

How do I determine the number of elements and loop to extract title from each of them?

ISBN:9780980200447.details.table_of_contents[x].title
                         $key & ".details.table_of_content[" & i & "].title"

Which then I'll set a field in the related table with that value.

set field ISBN_ToC::Title ..

[comment]

Start by setting a $toc variable to:

JSONGetElement ( $json ; "ISBN:9780980200447.details.table_of_contents" )

Then have your script do:

Set Variable [ $parentID; Value:Parent::ParentID ]
Set Variable [ $n; Value:ValueCount ( JSONListKeys ( $toc ; "" ) ) ]
Freeze Window
Go to Layout [ Child ] 
Loop
  Set Variable [ $i; Value:$i + 1 ]
  Exit Loop If [ $i > $n ]
  New Record/Request
  Set Field [ Child::ParentID; $parentID ]
  Set Variable [ $record; Value:JSONGetElement ( $toc ; $i - 1) ] 
  Set Field [ Child::Level; JSONGetElement ( $record ; "level" ) ] 
  Set Field [ Child::Title; JSONGetElement ( $record ; "title" ) ]
End Loop
Go to Layout [ original layout ]

I have left out the "type" because in the given example it does not seem to contain anything useful.

Don't forget to reset $i if you intend to use it again for parsing another array.

 

 

Edited May 30, 2020 by comment

[Tony Diaz]

Yeah, type is kind of self implied by the table::field at this point.

Disregard the $i not found bit below. Apparently I needed to watch a stupid movie on Netflix so I could think right when I came back to the computer.

Interestingly, though, it's complaining to me about $i not being found.

What am I blatantly missing here?

 

Ummm...  it's -right there- being set.

 

Edited May 30, 2020 by Tony Diaz

[Tony Diaz]

3 hours ago, comment said:

Don't forget to reset $i if you intend to use it again for parsing another array.

..which actually appears to be needed before it's even needed.

Setting $i to zero before the loop starts.. is an effective reset and then $i exists as something to add 1 to.

 

As for the other part not working, the If [ $ToCTtl ] does not like that big long string. If I set that with some other variable ( If [ $amazon ] since I know $amazon works afterwards for it's loop, then I get the records created in ISBN_ToC as expected. 

If is being used the same exact way, except the string assigned to it is much longer.

Edited May 30, 2020 by Tony Diaz

[comment]

27 minutes ago, Tony Diaz said:

Setting $i to zero before the loop starts.. is an effective reset and then $i exists as something to add 1 to.

$i does not need to exist. Setting $i to $i + 1 creates it if didn't exist before. As it happens, in this particular case you may find it more convenient to do:

Set Variable [ $parentID; Value:Parent::ParentID ]
Set Variable [ $i; Value:0 ]
Set Variable [ $n; Value:ValueCount ( JSONListKeys ( $toc ; "" ) ) ]
Freeze Window
Go to Layout [ Child ] 
Loop
  Exit Loop If [ $i ≥ $n ]
  New Record/Request
  Set Field [ Child::ParentID; $parentID ]
  Set Variable [ $record; Value:JSONGetElement ( $toc ; $i ) ] 
  Set Field [ Child::Level; JSONGetElement ( $record ; "level" ) ] 
  Set Field [ Child::Title; JSONGetElement ( $record ; "title" ) ]
  Set Variable [ $i; Value:$i + 1 ]
End Loop
Go to Layout [ original layout ]

because it both resets the $i before the loop and starts the loop with $i being 0 instead of 1, which conforms to JSON's zero-based numbering scheme.

In most other cases, starting the loop by setting $i to $1 + 1 is preferable and works perfectly fine. In fact, you'll find that some developers prefer to combine these two:

  Set Variable [ $i; Value:$i + 1 ]
  Exit Loop If [ $i > $n ]

into one:

  Exit Loop If [ Let ( $i = $i + 1 ; $i > $n ) ]

I prefer the more explicit version myself, but this works too.

The tool that's giving you the warning is not Filemaker and apparently it is not aware of Filemaker's ability to create variables implicitly.

 

50 minutes ago, Tony Diaz said:

why it's still not giving me anything

I am afraid I couldn't debug a script from a (partial!) screenshot, even if I wanted to.

 

[Tony Diaz]

2 minutes ago, comment said:

$i does not need to exist. Setting $i to $i + 1 creates it if didn't exist before. As it happens, in this particular case you may find it more convenient to do:

That's what I thought too. But if I enter just that:

 Set Variable [ $i; Value:$i + 1 ]

I get the error as the screen shot shows.

If I remove $i + 1 from there then it does not complain.

Or.. if I add the value ahead of the loop it does not complain.

I got the bit on the partial screen shot stuff undone - that's because as I noted, it's not liking something about the length of the string being used to qualify $ToCTtl ..  or maybe it's because there's [ ] or { } brackets in there?  That doesn't make any sense.

 

[comment]

4 minutes ago, Tony Diaz said:

But if I enter just that:

 Set Variable [ $i; Value:$i + 1 ]

I get the error as the screen shot shows.

As I said, this "error" is not coming from Filemaker. You have some third-party tool (MBS plugin?) installed. In Filemaker, it is not an error to refer to a non-existing variable.  A non-existing variable evaluates to an empty string - and adding 1 to an empty string results in 1.

 

[Wim Decorte]

1 minute ago, comment said:

As I said, this "error" is not coming from Filemaker. You have some third-party tool (MBS plugin?) installed.

Yep, it's MBS.

As @comment says: it's not an error; it's actually fairly common practice.

An alternative would be to explicitly set $i to 0 outside of the loop, move lines 49 and 50 to just before the End Loop and change the exit condition to be >= instead of >, and to change line 53 to just use $i instead of $i - 1

1 hour ago, Tony Diaz said:

As for the other part not working, the If [ $ToCTtl ] does not like that big long string.

That IF is something that I personally consider bad programming practice.  If I see it in code reviews I will call it out and make the dev change the habit.  It's too implicit.  The variable is a string and the result of an IF is a boolean.  By throwing a string at FM and expecting FM to interpret a string as boolean you're invoking all sorts of data type casting rules that FM may have to convert a string into a boolean.  You may get unexpected results if the string starts with certain characters.

IMHO it is much better to be explicit and tell FM exactly what you need to have checked.  In this case you want to know if that variable holds a value so use 

IF[ not isempty( $ToCTtl )]

or

IF[ length( $ToCTtl ) > 1 ]

of handle the fact that it isempty with some error reporting back to the calling script:

IF[.isempty( $ToCTtl ) ]

  Exit Script[ "whatever you want to tell the calling script"]

ELSE

  the normal parsing routine

END IF

[Tony Diaz]

Ah, I see what you mean. Okay. Come to think of it, I didn't remember seeing those messages before I added the MBS Plugin. I just hadn't done very much with scripting either, yet.  MBS may be parsing the script but it's not 100% doing so in the same way the FileMaker actually uses the scripting.

Plus I probably had that lumped in with the If [ $ToCTtl ] fails to return a true state issue that seems to be going on too.

3 minutes ago, Wim Decorte said:

That IF is something that I personally consider bad programming practice. 

Fair enough. Yeah, it's obvious it's having issues with the presence of certain characters in the string. It did feel kind of open ended / half done. But it makes total sense. 

I switched it to use not isempty and it works as expected. I also changed all the others I have too, and will do them that way from now on. I really have not had all that much experience with FM scripting, and combined with some of the literal boolean going on 'isempty" or isn't empty, but not isempty .. I don't think it in that order. I'm picking up..