FileMaker 18 Data API > Create Record - JSON visualization and building

Lounge (Discussions)
1

Hi All,

Below is a visualization of the JSON used to create...
a single parent record (fieldData) and 2 portals (portalData) of related records
...using the FileMaker 18 Data API > Create Record call.

Two questions...

  1. Does the nesting of Objects (green borders) and Arrays (red borders) look correct enough? *Please note that the borders are done somewhat differently to how we did it here Tony White Designs: JSON Overview (because it was easier!).

  2. What is your preferred method(s) for building this JSON?

image

2

Assuming your empty values are for text fields due to the use of quotes, how about something like this:

JSONSetElement ( "" ; 
[ "fieldData.Insp_ReportNumber" ; "" ; JSONString ];
[ "fieldData.Insp_Contractor_1_Name" ; "" ; JSONString ];
[ "fieldData.Insp_Code" ; "FS" ; JSONString ];

[ "portalData.Inspection__Inspect_line__id[0].Inspection__Inspect_line__id::Active" ; 1 ; JSONNumber ];
[ "portalData.Inspection__Inspect_line__id[0].Inspection__Inspect_line__id::Locked" ; "" ; JSONString ];

[ "portalData.Inspection__Inspect_line__id[1].Inspection__Inspect_line__id::Active" ; 1 ; JSONNumber ];
[ "portalData.Inspection__Inspect_line__id[1].Inspection__Inspect_line__id::Locked" ; "" ; JSONString ];

[ "portalData.Inspection__InspectCode__code[0].Inspection__InspectCode__code::Code" ; "FS" ; JSONString ]
)
2 Likes
3

Hi @skywillmott

Thanks, your code works…produces the input provided.

I should have said "What is your preferred method(s) for building this JSON in a generalized way?”

Given that the portals will have a variable number of rows...

  • Would you loop portals (or GTRR) and use one JSONSetElement per row/record, or would you build JSON sub-blocks using the List Function, an un-stored field, etc.? Other methods?

  • Would you build the JSON in sub-blocks ( fieldData, portalData.TableOccurrence1, portalData.TableOccurrence2 ) and then assemble the blocks. Would that scale better? Worth doing?

  • Would you sanitize your JSON building code against a Table Occurrence and/or Field name change, of just be disciplined about name changes?

4

Two easy ways to do it, depending partially on your version of FileMaker.

  1. Pre-19 - a calculated field that combines the record data into a JSON format. Then use List ( ), and substitute the carriage returns for a comma, and wrap in "{ }".
  2. Execute DataAPI script step. It returns JSON. It doesn't get any simpler, and faster than that.
1 Like
5

Amen.
If you are going to integrate FM with FM then there really is no simpler way than this.

If you want to integrate some outside app then look for the existing wrappers, I've worked with a few of them and generally they are excellent and have already solved this problem.

2 Likes
6

The FileMaker 19 Execute Data API script step is very cool.

Does the FileMaker 19 Execute Data API script step work with FileMaker Pro 19 and FileMaker Server 18?

Project is on FileMaker 18 at the moment.

Got it working on the FileMaker 18 versions that I have to work with...scripting with a mix of JSONSetElement, Custom Functions, and old school Text Functions.

On to the next piece.

The future looks bright.

7

The script step does not require FMS at all, but the client needs to be 19.

Not sure why you felt you needed text functions to create JSON? Mind sharing that?

2 Likes
8

FileMaker Pro 19 only required. Cool. Thanks.

I have heard reports that the JSONSetElement has to parse the JSON on each call and that the performance can slow down (in a nonlinear fashion?) as the JSON grows.

This is the a similar idea to using the List function and a stored field to build JSON from a list of records.

That said, I am not sure if using JSONSetElement vs. Text functions speed has been tested and reported conclusively.

The JSON that I am building is derived from 3 tables…a parent table and 2 child tables…one of which is in an external file.

Currently I am scripting from the parent file context and walking the 2 portals row by row and building up text line by line using the List function.

After the List is built, a substitute is applied and the text is wrapped with “{}“ or “[]“ to make it into a JSON Object or Array respectively.

On the next line, I use the JSONFormatElements for debugging, and to make sure that the JSON is valid. These JSONFormatElements lines could be turn off later when everything is tested as working

There are 3 pieces to the final JSON: 1 fieldData Object and 2 portalData Arrays.

All three of these are built separately and then combined. This is more modular and seems likely to be faster.

The fieldData object is built in a single call to JSONSetElement, using the [] syntax, which I hope (and expect) requires 1 or 0 parsing operations (as I am targeting an empty object).

The 2 arrays are built up using text functions as I have heard this method might be faster.

Each line is built using a Custom Function that produces the Key and the JSON encoded Value.

After the the List is built, a Substitute is applied and the text is wrapped with a “[]“ to make it into a JSON Array.

Finally the 1 JSON Object and 2 JSON Arrays are combined into a single JSON Object using a single call to the JSONSetElement:

JSONSetElement ( "{}" ; 
[ "fieldData" ; $fieldData.obj ; JSONObject ] ;
[ "portalData" ; "" ; JSONObject ] ;
[ "portalData.Inspection___Inspect_Line__id" ; $portalData.Inspection___Inspect_Line__id.array ; JSONArray ] ;
[ "portalData.Inspection___InspectCode__code" ; $portalData.Inspection___InspectCode__code.array ; JSONArray ]
)

Text functions vs JSONSetElement?

I am not sure how many rows there would have to be before there would be a performance difference.

Might be a case of premature optimization?

Any hard numbers out there?

9

That's for parsing... not for building the JSON.
Most performance issues with building JSON come not from any JSON functions but from the immutability of FM variables when trying to add something to the same variable over and over. That is solved by not using 'set variable' but 'insert calculated result'.

Parsing can get slow if you keep traversing the same JSON over and over. The trick there is to extract sub-elements (elements from an array) and only parse those further.

4 Likes
10

Interesting....

If I am understating you correctly, you are saying that (under the hood) the “Set Variable" script step sets an [immutable variable] while the “Insert Calculated Result targeting a $var script step sets a [mutable variable] and therefore, the "insert calculated result” script step is more performant when a $var is being written to more than once, for example in a loop.

  • Change log note: The “Insert Calculated Result is able to target a $var (or $$var) as of FileMaker 16.

Regarding the parsing…yes, we have been parsing sub-elements and naming them with a dot syntax (to reflect the hierarchy) and using an .obj or .array suffix. Simpler to build, easier to read, and faster!

11

Correct.

Not sure I understand the naming convention here... once I extract a piece from JSON I typically don't need to store it such a manner that I need to be able to see its original hierarchy. Can you elaborate with an example?

2 Likes
12

Thank you for the confirmation on immutable vs. mutable distinction.
Interesting refactoring opportunity (once solidly on FM 16 and above).

Here is a variable naming convention example for:
JSONSetElement ( json ; keyOrIndexOrPath ; value ; type )

image

The name of a var that provide the "value" to the function is based on the "keyOrIndexOrPath", with a prefix of (or $$) and a suffix of ".obj" or ".array"

Verbose code for sure. Easy to read. Easy to code generate using AppleScript or Grep.

13

Can we clarify this? My understanding is that it's not actually faster to use Insert Calculated Result unless you're appending data to a variable. However, it is still more space-efficient, which incidentally can improve performance after many iterations.

Basically the difference is, Set Variable copies the whole variable each time, whereas Insert Calculated Result appends to the original var.

So if you're trying to write "hello" one letter at a time...

With Set Variable:
1: h
2: he
3: hel
4: hell
5: hello

and iterations 1-4 are still stored in memory somewhere, at least in older FM versions, you could use this space complexity to crash FM! but that's fixed now I believe.

With Insert Calculate Result:
1: h
2: e
3: l
4: l
5: o

i.e. Set Variable had to write and store 10 extra characters in memory.

Right?

1 Like
14

Not just appending, but anything that keeps changing the same variable. Building JSON is a good example of such a scenario. Every time you do Set Variable on a variable that already exists, FM has to make a full copy of the variable to replace the old one. (Butchering the definition of immutable here but it's close enough).

2 Likes
15

It would still have to write the same amount of data for Insert Calculated Result (ICR), because with “Select” enabled it overwrites the whole variable. Yes there is a space advantage because the old copy is not occupying memory any more.

Building an array of objects (by appending) is the only situation where it’s substantially faster to use ICR, because “select” is off.

I’ll re-test this later So I can put some numbers on it.

2 Likes
16

@mrwatson-de did some pretty cool benchmarking on this

https://community.claris.com/en/s/question/0D50H00006dsly8SAA/use-insert-calculated-result-to-concatenate-strings-groundbreaking-performance-leap

2 Likes
17

Yes! I am eternally grateful to him for his post, a brilliant insight.

I have confirmed my assertion. I understand variable mutability, and I suspect that Insert Calculated Result may be more space-efficient in general. However, when it comes to overwriting data and "Select" is enabled on ICR when building JSON with JSONSetElement(), both perform the same because both are writing the same amount of data to memory. When you say Set Variable has to copy the variable, I believe that refers to what I posted above: as I was building up "hello" with Set Variable, FM had to re-copy the beginning of the string on each loop, leading to having to write an extra 10 characters (15 chars total) to memory compared to ICR which wrote 5 characters total.

Here is a script I used to demonstrate this, showing that the performance gain is seen when appending data, rather than overwriting it.

Screen Shot 2020-07-07 at 6.36.35 AM
Screen Shot 2020-07-07 at 6.36.35 AM1046Ă—861 79.4 KB

tl;dr, If you build your JSON by appending text, then Insert Calculated Result is a big win, but if you build your JSON with JSONSetElement, the gain is negligible.

EDIT: I added one more test where I first set $json to outside the loop and then each iteration adds a new element of "hello". Again the performance was about the same for both since "Select" is enabled:

Screen Shot 2020-07-07 at 6.59.13 AM
Screen Shot 2020-07-07 at 6.59.13 AM868Ă—206 27.2 KB

2 Likes
18

Thanks for the test!

1 Like
19

That is true, if you run them exactly the same. To get faster performance and take advantage of the difference in processing, you can't process them the same. If you tweak the setting of the variable slightly, you can get huge performance gains. Essentially, write each iteration as a separate JSON object, then substitute "}{" with ",". You end up with a JSON object and it happens fast. This won't be as easy as JSONSetElement and SetVariable, but it all depends on the scenario.

Screen Shot 2020-07-07 at 10.33.28 AM

2 Likes
20

@jormond it appears you are talking about building one big Object {}, not Array?

i.e. you convert {"name": "Josh"}{"age": 30}{"city": "Los Angeles"}
into {"name": "Josh","age": 30,"city": "Los Angeles"}?

I will agree with you there, although I would never build an object big enough to justify doing that... I would perhaps build an array that big, in which case I'd use the method I outlined above with Insert Calculated Result

Also, if you use this method, you gotta watch out for key collision.