JSON, Level: Advanced, Version: FM 16 or later

Ordering Elements within JSON Objects

Introduction

If you’ve generated JSON objects via FileMaker, you’re likely aware of the disparity between the order in which you specify the elements, and the order in which they subsequently appear (i.e., alphabetized by key name). As I wrote two years ago (in Thinking About JSON, Part 2)…

Both JSONSetElement and JSONFormatElements will automatically alphabetize key/value pairs within JSON objects. This can be disconcerting if you aren’t expecting it, but eventually you work through the stages of grief and come to accept that it’s just the way things are. The JSON Data Interchange Standard definition at json.org specifies that “An object is an unordered [emphasis mine] set of name/value pairs”, whereas an array is an “ordered collection of values”. In other words, by definition the order of the key/value pairs within JSON objects simply does not, and should not, matter.

Well… okay… you know that, and I know that, and FileMaker knows that, as do the JSON Jedi… but what if you are demonstrating a proof of concept to a client, and to avoid cognitive dissonance and unnecessary explanation you’d like to “doctor the evidence” (so to speak) and order the elements meaningfully?

For example, you’d like them to see this…

2020-04-29_165223

…as opposed to what they would see if you took a standard approach to generating the JSON object:

2020-04-29_170833

Today we’re going to look at a couple hacks to make the first example viable, and the basic idea is the same in both cases:

  • do something to trick FileMaker into ordering the items your way
  • remove the evidence of that trickery

Note: the standard disclaimer applies that there may be (actually there are) edge cases where this could fail. But 99.99% of the time these approaches will work just fine. As with all techniques on this site proceed with caution and use at your own risk.

Hack #1: Stealth Array

Substitute ( 

JSONFormatElements ( 
   JSONSetElement ( "[]" ; 
      [ 0 ; JSONSetElement ( "{}" ; "Table of Contents" ; "This comes first" ; JSONString ) ; JSONObject ] ;
      [ 1 ; JSONSetElement ( "{}" ; "Foreword" ; "This comes second" ; JSONString ) ; JSONObject ] ;
      [ 2 ; JSONSetElement ( "{}" ; "Introduction" ; "This comes third" ; JSONString ) ; JSONObject ] ;
      [ 3 ; JSONSetElement ( "{}" ; "Body" ; "This comes fourth" ; JSONString ) ; JSONObject ] ; 
      [ 4 ; JSONSetElement ( "{}" ; "Appendix" ; "This comes fifth" ; JSONString ) ; JSONObject ] ; 
      [ 5 ; JSONSetElement ( "{}" ; "Index" ; "This comes last" ; JSONString ) ; JSONObject ] 
   )
) ; 

[ "[¶" & Char ( 9 ) & "{¶" & Char ( 9 ) & Char ( 9 ) ; "{¶" & Char ( 9 ) ] ; 
[ "¶" & Char ( 9 ) & "},¶" & Char ( 9 ) & "{¶" & Char ( 9 ) & Char ( 9 ) ; ",¶" & Char ( 9 ) ] ; 
[ "¶" & Char ( 9 ) & "}¶]" ; "¶}" ]  

)

8 May 2020 — actually this can be simplified (as per Paul Jansen’s comment at the end of the article).

Substitute (

JSONSetElement ( "[]" ;
[ "[0].Table of Contents" ; "This comes first" ; JSONString] ;
[ "[1].Foreword" ; "This comes second" ; JSONString ] ;
[ "[2].Introduction" ; "This comes third" ; JSONString ] ;
[ "[3].Body" ; "This comes fourth" ; JSONString ] ;
[ "[4].Appendix" ; "This comes fifth" ; JSONString ] ;
[ "[5].Index" ; "This comes last" ; JSONString ]
)

; [ "},{" ; ",¶" & char(9) ]
; [ "[{" ; "{¶" & char(9) ]
; [ "}]" ; "¶}" ]

)

Explanation:

  • create an array where each element is an object consisting of a single key/value pair (this allows you to control the order, since element order is respected within arrays)
  • substitute out everything you don’t want your audience to see

Hack #2: Numeric Prependectomy

This idea is courtesy of Paul Jansen and uses the While function so requires FM 18 or later.

The result we’d like to display:

2020-04-29_182114

How we get there:

While ( [ 
~json = JSONFormatElements ( JSONSetElement ( "{}" ; 
[ "!000!first" ; "blah" ; JSONString ] ; 
[ "!001!second" ; "blah" ; JSONString ] ; 
[ "!002!third" ; "blah" ; JSONString ] ; 
[ "!003!fourth" ; "blah" ; JSONString ] ; 
[ "!004!fifth" ; "blah" ; JSONString ] ; 
[ "!005!sixth" ; "blah" ; JSONString ] ; 
[ "!006!seventh" ; "blah" ; JSONString ] ; 
[ "!007!eighth" ; "blah" ; JSONString ] ; 
[ "!008!ninth" ; "blah" ; JSONString ] ; 
[ "!009!tenth" ; "blah" ; JSONString ] ; 
[ "!010!eleventh" ; "blah" ; JSONString ] ; 
[ "!011!twelth" ; "blah" ; JSONString ] ; 
[ "!012!thirteenth" ; "blah" ; JSONString ] 
) ) ; 
~count = ValueCount ( JSONListKeys ( ~json ; "" ) ) ; 
~i = 0 ] ; 
~i < ~count ; 
[ ~json = Substitute ( ~json ; "!" & Right ( "00" & ~i ; 3 ) & "!" ; "" ) ; 
  ~i = ~i + 1 ] ; 
~json
)

Explanation:

  • prepend numbers to the keys (and zero pad the numbers so that, for example, 10 doesn’t precede 2)
  • remove the zero-padded numbers from the key names

Here’s what can happen if you don’t zero pad (though of course you’ll be fine if there are less than 11 elements in your object):

2020-04-29_182206

But Is It Still JSON?

Yes, the result of either of these hacks is 100% valid JSON as far as FM is concerned, e.g.,

2020-04-29_172812

…and…

2020-04-29_183438

…which is to say that if you chose to generate a JSON object via either of these techniques, FileMaker will recognize the object as if the hack had never been applied in the first place.

14 thoughts on “Ordering Elements within JSON Objects”

  1. In fact, the element order can be important not only for a visual effect. There is at least one API that requires the elements order to exact match their reference:

    https://developer.authorize.net/api/reference/index.html

    > FIRST TIME USER? Click here for API Endpoints & Authentication details

    > A Note Regarding JSON Support
    > The Authorize.Net API, which is not based on REST, offers JSON support through a translation of JSON elements to XML elements. While JSON does not typically require a set order to the elements in an object, XML requires strict ordering. Developers using the Authorize.Net API should force the ordering of elements to match this API Reference.

    I even wrote a CF to address this problem:

    https://www.briandunning.com/cf/2303

  2. Good stuff! I think you can also accomplish this with `List()`, but you have to be careful to not set duplicate keys (that applies to the methods above too, I believe). Something like:

    “`
    Substitute (
    List (
    JSONSetElement ( “” ; “first” ; “blah” ; JSONString ) ;
    JSONSetElement ( “” ; “second” ; “blah” ; JSONString ) ;
    JSONSetElement ( “” ; “third” ; “blah” ; JSONString ) ;
    JSONSetElement ( “” ; “fourth” ; “blah” ; JSONString ) ;
    JSONSetElement ( “” ; “fifth” ; “blah” ; JSONString ) ;
    JSONSetElement ( “” ; “sixth” ; “blah” ; JSONString )
    );
    [ “¶{” ; “¶”] ;
    [ “}¶” ; “,”]
    )
    “`

    1. Hi Josh, thanks for that idea. My goal was formatted, as opposed to raw, JSON. Your code results in…

      {“first”:”blah”,”second”:”blah”,”third”:”blah”,”fourth”:”blah”,”fifth”:”blah”,”sixth”:”blah”}

      …which might be fine under certain circumstances (especially if the example is this basic).

      If I wrap the above in JSONFormatElements the custom order is lost.

      — Kevin

      1. Kevin,

        You could use my CF, mentioned above (https://www.briandunning.com/cf/2303) to produce a formatted JSON structure with a custom objects sort order. Simple apply the format function before removing serial numbers:

        @JSONFixElement ( 0 ; JSONFormatElements ( fixedJSON ) )

        I’ve included a working usage example in the updated version of my CF. I’ve also replaced the function While() with a FilterValues() trick. Now in works in FM 16+.

        Note also the link to another my wrapper CF in the updated description (https://www.briandunning.com/cf/2130). It allows to align both object names and values. I often use it to debug cURL responses. It should be useful for your goal.

        If you consider to use my CF, I’d suggest to to rename it to something shorter, like JFx().

  3. Kevin, an alternative approach to the stealth array might be:

    substitute (

    JSONSetElement ( “[]” ;
    [ “[0].Table of Contents” ; “This comes first” ; JSONString] ;
    [ “[1].Foreword” ; “This comes second” ; JSONString ] ;
    [ “[2].Introduction” ; “This comes third” ; JSONString ] ;
    [ “[3].Body” ; “This comes fourth” ; JSONString ] ;
    [ “[4].Appendix” ; “This comes fifth” ; JSONString ] ;
    [ “[5].Index” ; “This comes last” ; JSONString ]
    )

    ; [ “},{” ; “,¶” & char(9) ]
    ; [ “[{” ; “{¶” & char(9) ]
    ; [ “}]” ; “¶}” ]

    )

    1. I like that a lot. It had not occurred to me to simplify my approach in this way. Thank you for the lesson Obi Wan!

        1. Kevin,

          How annoying that FileMaker undoes the ordering with JSONListKeys. I guess another hack is needed to get the keys in the order we set them!

          Your articles often encourage me to dig deeper and increase my understanding and knowledge; so thank you ever so much for all the time you invest in writing them.

  4. Kevin this is great article – just had this issue with working with Auth.net they require specific order – i couldn’t figure out how to set this with the nested objects – so what I did was to do insert text and add a pattern of what I want the json object to be with @tags@ so that i substitute out with real data, then remove returns and spaces with a CF.

  5. Hi Kevin,

    Thanks for this article – was hoping I would never have to worry about this but have encountered this issue today with a particular API I’m using. I’m having an issue with implementing this that I hope you can help with. I’m building up an array by looping through a found set of records and my calculation looks like this:

    Let([

    ~json = Substitute ( JSONSetElement ( “” ;

    [ “[0].standard”; Images::Standard; JSONString ] ;
    [ “[1].filename”; Images::fileName ; JSONString ];
    [ “[2].image”; Base64EncodeRFC ( 3548 ; Images::Photo ); JSONString ]

    )

    ; [ “},{” ; “,¶” & Char(9) ]
    ; [ “[{” ; “{¶” & Char(9) ]
    ; [ “}]” ; “¶}” ]

    )

    ];

    JSONSetElement( $images ; $counter ; ~json ; JSONObject )
    )

    The result of the ~json varible works to keep the specified order, but it looks like it’s been overridden with the last line:

    JSONSetElement( $images ; $counter ; ~json ; JSONObject )

    I can’t work out a syntax to solve this so far after many failed attempts – wondering if you can see a possible solution here?

    Many thanks,
    Steve

    1. Hi Steve,

      There may be several things going on… one of them is that after you’ve created a fake object (i.e., via substitution trickery), you cannot subsequently apply JSONSetElement (or JSONFormatElements) to it because that will blow out the custom order. I’m going to post a couple screen shots. These are from one of my demos, but should convey the general idea.

      Assuming you’re trying to create an array w/ one custom-ordered object per entry, e.g.,

      There are of course various ways you could skin this particular cat… here’s one:

      If this isn’t what you had in mind, please post an example of how the output JSON should look.

      Kevin

      1. Thanks Kevin – that really helps. One final part of the puzzle is how would you then join this line items JSON array to a parent JSON object that you have already assembled, e.g. for your invoice it might look like this:

        {
        “custid” : “C02839”,
        “date” : “12/4/2017”,
        “invid” : 6362,
        “total” : 542.5
        }

        and I then need to add the line items $images JSON:

        [
        {
        “C”:”P01823″,
        “B”:”1″,
        “A”:”17.5″
        },
        {
        “C”:”P01957″,
        “B”:”10″,
        “A”:”52.5″
        },
        {
        “C”:”P01858″,
        “B”:”8″,
        “A”:”14.5″
        }
        ]

        to together without using the JSONSetElement function again but still generate valid JSON?

        1. Assuming the parent JSON object is in a variable called $parent, and you’d like to merge $images into it as the value of a new key/value pair where the key will be named “lineitems”… a lazy approach would be to assume there’s only a single “}”. That may not be a safe assumption, but it appears to be true based on your example. In that case… (this is off the top of my head so you may need to massage it a bit):

          Set Variable [ $parent ; Substitute ( $parent ; “¶}” ; “,¶\”lineitems\” : ” & $images & “¶}” ]

          A non-lazy approach — which would work in the case of either one “}” or multiple pre-existing “}” in $parent — would be to use the Replace function to overwrite the final two characters of $parent with

          “,¶\”lineitems\” : ” & $images & “¶}”

          …again, this is off the top of my head and may need fine tuning.

Leave a Reply to Josh Willing Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.