Virtual list using JSON

[TJ53]

On a separate thread I asked Bruce Robertson if the new FM16 JSON functions could be used together with the virtual list technique he created. I haven’t experimented with it but I wonder if JSON adapt well to this great technique. Any thoughts? thanks!

[Wim Decorte]

In theory: yes.  The question will come down to whether the JSON building and parsing functions are faster or slower than the regular text functions I guess.

[jbante]

Technically, it could be done. As Wim mentioned, performance is a major constraint. The JSON functions are slow compared to the built-in functions for return-delimited lists, and very slow compared to repeating variables.

[TJ53]

Thank you for the responses, I hadn't thought about the speed factor, it makes a lot of sense.

So ... are the JSON functions slow for building or parsing? I haven't tested them with big chunks of text. I see that in the recent FM16 update the speed of the "sort values" and "unique values" functions have been improved. Is the speed of the JSON functions an issue in a similar way? I haven't read anything about it ... any interesting links about this? thanks!

[jbante]

The JSON functions in FileMaker 16 have constraints the SortValues and UniqueValues do not.

The JSON parsing functions check that the input they're trying to parse is in fact completely valid JSON. FileMaker has to do this to be able to return an error result when appropriate. You can't know if the outer-most structure in a JSON string is correctly terminated without starting at the beginning, checking every inner structure, and getting all the way to the end.  Due to how the JSON format works, this requires stepping through the entire string for every single operation. (This could be resolved by FileMaker using a more efficient internal data structure for JSON in place of the raw string, but performance tests from a few folks suggest that FileMaker isn't doing this.) In comparison, the GetValue function only has to parse as many newline characters as it takes to get to the value you want, and it can ignore everything in a string after that point. With repeating variables, there doesn't need to be any parsing at all (depending on how you do it) — you can just use the whole content of the variable (repetition).

The JSON writing functions have a similar constraint. They validate any JSON you had to start with, and then the JSON writing functions are somewhat fastidious in organizing the contents of their JSON outputs.

You can make parsing nested structures in JSON less slow by parsing out inner structures, then getting the details from the inner structures, rather than pulling from the outer structure for every inner detail. This reduces how much total work FileMaker spends validating the JSON for each read.

When speed is more important than simple code, do this:

Let ( [
    _dictionary0 = JSONGetElement ( $json ; 0 ) ;
    _0a = JSONGetElement ( _dictionary0 ; "a" ) ;
    _0b = JSONGetElement ( _dictionary0 ; "b" ) ;
    
    _dictionary1 = JSONGetElement ( $json ; 1 ) ;
    _1a = JSONGetElement ( _dictionary1 ; "a" ) ;
    _1b = JSONGetElement ( _dictionary1 ; "b" )
] ;
    ...
)

Not this:

Let ( [
    _0a = JSONGetElement ( $json ; "[0].a" ) ;
    _0b = JSONGetElement ( $json ; "[0].b" ) ;
    _1a = JSONGetElement ( $json ; "[1].a" ) ;
    _1b = JSONGetElement ( $json ; "[1].b" )
] ;
    ...
)

And for writing JSON, add as much in each JSONSetElement call as you can, rather than using many separate calls. Do this:

JSONSetElement ( "[]" ;
    [ "[0].a" ; 1 ; JSONNumber ] ;
    [ "[0].b" ; 2 ; JSONNumber ] ;
    [ "[1].a" ; 3 ; JSONNumber ] ;
    [ "[1].b" ; 4 ; JSONNumber ]
)

Not this:

Let ( [
    _json = JSONSetElement ( "[]" ; "[0].a" ; 1 ; JSONNumber ) ;
    _json = JSONSetElement ( _json ; "[0].b" ; 2 ; JSONNumber ) ;
    _json = JSONSetElement ( _json ; "[1].a" ; 3 ; JSONNumber ) ;
    _json = JSONSetElement ( _json ; "[1].b" ; 4 ; JSONNumber )
] ;
    ...
)

While these patterns make FileMaker's JSON functions less slow, it isn't enough to make them competitive with return-delimited lists or repeating variables for speed.

[TJ53]

That's some great information to take into account, thank you!!

[john renfrew]

Jeremy, have you tested against the 16.0.2 update?? `does it improve speed??

[Mike Duncan]

A couple things that come to mind if you go down this path...there is a limit to how many variables you can set with a Let statement, 1000. And if you use the "JSONFormatElements" function, it may re-sort your data unexpectedly. I would consider JSON for passing variables, possibly, over using for virtual lists.

Mike

[jbante]

On 7/15/2017 at 11:39 PM, john renfrew said:

Jeremy, have you tested against the 16.0.2 update?? `does it improve speed??

I have now. The 16.0.2 update does not change the performance characteristics of the JSON functions relative to return-delimited lists or repeating variables.

[john renfrew]

Thanks Jeremy..

[bruceR]

I did not attend Devcon this year. But I see that Kevin Frank posted the following comment recently on a different list.

And actually - even elsewhere in this forum. But I thought it might be good to add his comment within this thread. Anyway, quoting him:

Quote

One nice thing about FileMaker being on a yearly release cycle is that there is always something new to learn and write about… and, having just attended a couple highly informative sessions on the topic of JSON (JavaScript Object Notation) at Devcon 2017 — thank you Todd Geist and Anders Monsen — it is clear to me that JSON + virtual list is a compelling combination.

https://filemakerhacks.com/2017/07/27/json-virtual-list/
 

Edited July 28, 2017 by BruceR
clarification and attribution

[Lee Smith]

Hey Bruce,

Kevin also posted the link here.

Lee