JSON Array + Virtual List

10 Sep 2017: Dave Graham has made a couple significant improvements to the original demo file that accompanied this article. I recommend reading his comments at the end of this article, and using the techniques featured in his version of the demo file — dg-json-array-plus-virtual-list.zip.

Over the last few postings we’ve been looking at using JSON with Virtual List… and prior to today, our focus has been on JSON structures using name/value pairs. Well today we’re going to switch our attention to basic JSON array structures.

2017-09-02_123654

Demo file: json-array-plus-virtual-list.zip (requires FileMaker 16.02 or later)

Apart from using JSON arrays instead of name/value pairs, today’s demo is quite similar what we saw in JSON + Virtual List, as well as in JSON + Virtual List part 2, and to avoid repetition, what follows will assume the reader is familiar with the material in those articles.

As before we have a straightforward demo that a) generates a small amount of JSON from a standard FileMaker table, and then b) renders it in a virtual list table like so:

2017-09-07_150523

The basic idea:

  1. Loop through the records in the Sales table, encoding data into a JSON object with help from JSONSetElement
  2. Display the virtual list table, using JSONGetElement to decode the data into calculated repeating fields

So, what exactly is a JSON array? For our purposes today, it can be defined as a series of comma-separated values enclosed in brackets, and here is the JSON object with five arrays that appears in today’s demo.

2017-09-07_145210

To the left of each array you can see its name (0,1,2,3,4) corresponding to the first five rows of the virtual list table (where ID number begins with 0, not 1). And since JSON uses a zero-based index, individual array items are parsed like so:

2017-09-07_151503

Explanation: get the zeroth item from array number 3.

Here are field definitions for the virtual list table:

2017-07-28_100543

The definition is identical for all three repeating “cell_” calc fields; only the result type differs.

2017-09-07_151504

Incidentally, there’s a minor bug specific to JSON arrays, and it occurs under very specific circumstances. Note that the data in the highlighted field has been entered as “.8”, i.e., without a leading zero.

2017-09-07_153301

When the “Generate Array” routine is run, note the emptiness of the ID 3 row.

2017-09-07_153451

The virtual list table can’t render it, because the corresponding array is missing.

2017-09-07_154503

Now add the leading zero into the field…

2017-09-07_161031

…and voila, no more pesky problem.

2017-09-07_161413

What the heck is going on? There seems to be a bug in JSONSetElement where numbers greater than zero but less than one are not rendered unless you prepend a leading zero. This occurs not only with manually entered data, but also as the result of calculations, e.g., 2 – 1.2 = .8 (not 0.8).

Now remove the leading zero, and this time run the “Protected” routine…

2017-09-07_161845

…and this time the array generates properly…

2017-09-07_161413

…because the routine prepends zeros to cost and price as a defensive measure (but only when necessary).

2017-09-07_162159

Finally I want to mention an interesting behavior associated with JSONFormatElements when applied to JSON objects containing arrays. Add the word “Orchestra” to “Modesto Philharmonic”…

2017-09-07_163356

…and note that JSONFormatElements now renders that particular array vertically.

2017-09-07_163600

At the risk of stating the obvious, this is merely a formatting quirk and in no way interferes with JSONGetElement’s ability to locate and extract the data.

(Also, since we’re running the “unprotected” version of the routine, and since when these screen shots were taken cost for row 4 contained “.8” and not “0.8”, array number “3” is empty.)

My first thought was that if any array item exceeded a certain number of characters, that would tip the array from horizontal to vertical, but the tipping point seems to apply to the overall length of the array, rather than to an individual item. For example, without removing the “Orchestra” from the customer name, change the cost and price values in that row to 1 and 2 respectively…

2017-09-07_164350

…and the array returns to the horizontal.

2017-09-07_164501

6 thoughts on “JSON Array + Virtual List

  1. apjcs

    Thank you so much for your explorations of virtual lists and JSON. The use of just three repeating fields to cover different data types is brilliant and reaffirms that repeating fields still have a useful role to play. Being able to sort by the actual virtual list fields is also a great feature.

    I have been experimenting myself and as mentioned in other comments on the subject, some JSON functions get really slow with large found counts. I imported the data from one of the previous demos to perform some benchmarks. I found that the time to create the report reduces very considerably if I collect the rows in a text list and convert to JSON outside the loop with a Substitute

    1300 records reduced from 11.465s to 1.744s
    2500 records reduced from 43.138s to 4.259s
    5000 records reduced from 155.086s to 11.096s

    Hopefully, the performance of the FileMaker JSON functions will improve in future versions, but for now it seems that it is much faster to use text functions to create JSON especially when dealing with high record counts.

    Below are the changes I made to gain the speed improvements…

    Set Variable [ $arrayRow ; Value: Quote( $index ) & “:” & “[” & $date & “,” & $type & “,” & $customer & “,” & $cost & “,” & $price & “]” ]
    Set Variable [ $$arrayList ; Value: List ( $$arrayList ; $arrayRow ) ]
    Go to Record/Request/Page [ Next ; Exit after last: On ]
    Set Variable [ $index ; Value: $index + 1 ]
    End Loop
    Set Variable [ $$arrayJSON ; Value: “{” &Substitute ( $$arrayList ; [ “]¶” ; “],” ]) & “}” ]
    Set Variable [ $$arrayJSON ; Value: JSONFormatElements ( $$arrayJSON ) ]
    Go to Record/Request/Page [ First ]

    Thanks again for a great series of articles

    Reply
      1. Paul Jansen

        Kevin,
        My guess is that because the closing } has nothing to identify which JSON entity it is the end of, any JSON validation and processing has to basically count the { and } to work things out.
        There are, I think, other FileMaker text processing functions that suffer from degrading performance in a similar way (I experienced this big time when working with the sync payloads using FMEasySync).
        As always with FileMaker, we just have to decide which is the most efficient tool in the box to use in any given situation!
        It might be worth experimenting with having a field calc to extract individual rows from the Virtual List JSON and then have the display field calcs based on this. It might be a red herring as all the calcs would be unstored, but probably worth a look.
        I was also wondering, when comparing your different approaches to the JSON virtual lists, if the array approach is faster than named JSON entities when decoding the data.
        Some more testing required methinks…
        Regards, PJ

        Reply
  2. Dave Graham

    Kevin,

    I’m not sure if there was a reason for creating a list of JSON objects vs. a nested array, which seems a lot cleaner.

    This:
    [
    [ “2017+07+14”, “Walk-In”, “Coughlin & Gehart”, 136.79, 224.24 ],
    [ “2017+07+16”, “Internet”, “Modesto Philharmonic”, 319.3, 939.12 ],

    ]

    Instead of this:
    {
    “0” : [ “2017+07+14”, “Walk-In”, “Coughlin & Gehart”, 136.79, 224.24 ],
    “1” : [ “2017+07+16”, “Internet”, “Modesto Philharmonic”, 319.3, 939.12 ],

    }

    Also, I always use the JSONSetElement function to create the JSON object/array to avoid the types of issues you are experiencing with numbers.

    I uploaded a modified version of your demo with the JSON tweaks. You’ll see that you no longer need the protected version.

    I also added what I think could be an optimization, but who knows until it’s tested. I created an unstored calc field called “row” in the virtual_list table, which pulls the array index. The other fields (e.g., text, number, date) reference the this field based on the assumption that it will be faster to reference this field than extract a value from the array three times.

    https://www.dropbox.com/s/sn7s2gmijs9t8r1/JSON%20Array%20%2B%20Virtual%20List-NestedArray.fmp12?dl=0

    Reply
    1. Kevin Frank Post author

      Hi Dave,

      Thanks for taking the time to explain that. Your improvements make perfect sense.

      Many of us are taking baby steps with JSON; clearly you’re further along the learning curve.

      Regards,
      Kevin

      Reply
    2. Paul Jansen

      Dave,
      Thank you for the modified version, it is great to share our knowledge and experiences and this article and the comments have certainly helped me.

      It does make sense to use the native functions to create the individual ‘rows’ and to use the native JSON index in the field calcs. The field ‘row’ is also more or less what I was thinking about in my previous comment. I added my modification to use the List function to combine the individual row arrays and ran some tests again on higher record counts.

      1300 records: array JSON 11.518s – List JSON 4.872s
      2500 records: array JSON 35.789s – List JSON 5.668s
      5000 records: array JSON 133.450s – List JSON 8.080s

      Using the List method appears to be considerably faster. This is a shame as it would be much tidier to use the native JSON functions.

      Regards, PJ

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s