padJSON ( JSON ; rowLabels ; columnLabels )

Pad out JSON string to two-dimensional array with null values

Average rating: 3.8 (6 votes) Log in to vote

Michael Kupietz - Show more from this author
Michael Kupietz FileMaker Consulting
http://www.kupietz.com

Sample input:

"{\"A\":{\"1\":\"Hi\",\"3\":"there\"},"B":{\"1\":\"Hello\",\"4\":\"you\"}}"

Sample output:

{\"A\":{\"1\":\"Hi\",\"2\":\"\",\"3\":there\"\",\"4\":\"\"},\"B\":{\"1\":\"Hello\",\"2\":\"\",\"3\":\"\",\"4\":\"you\"},\"C\":{\"1\":\"\",\"2\":\"\",\"3\":\"\",\"4\":\"\"},\"D\":{\"1\":\"\",\"2\":\"\",\"3\":\"\",\"4\":\"\"},\"E\":{\"1\":\"\",\"2\":\"\",\"3\":\"\",\"4\":\"\"}}

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

Let (
        JSON = If ( JSON = "" ; "{}" ; JSON ) ;
        If (
                ValueCount ( rowLabels ) > 1 ;
                Let (
                        [
                                rowCount = ValueCount ( rowlabels ) ;
                                theRightRowValues = RightValues ( rowLabels ; rowCount - 1 ) ;
                                fileMakerFixRows =
                                    Left ( theRightRowValues ; Length ( theRightRowValues ) - 1 ) ;
                                nextJSON =
                                    If (
                                        rowCount > 1 ;
                                        padJSON ( JSON ; fileMakerFixRows ; columnLabels ) ;
                                        JSON
                                        )
                        ] ;
                        padJSON ( nextJSON ; ( GetValue ( rowLabels ; 1 ) ) ; columnLabels )
                        ) ;
                Let (
                        [
                                colCount = ValueCount ( columnlabels ) ;
                                theRightColValues = RightValues ( columnLabels ; colCount - 1 ) ;
                                fileMakerFixCols =
                                    Left ( theRightColValues ; Length ( theRightColValues ) - 1 ) ;
                                nextJSON =
                                    If (
                                        colCount > 1 ;
                                        padJSON ( JSON ; rowlabels ; fileMakerFixCols ) ;
                                        JSON
                                        )
                        ] ;
                        JSONSetElement (
                                nextJSON ;
                                "." & rowLabels & "." & GetValue ( columnLabels ; 1 ) ;
                                JSONGetElement ( json ; "." & rowLabels & "." & GetValue ( columnLabels ; 1 ) ) ;
                                "JSONtext"
                                )
                        )
                )
        )

I needed a function to pad out JSON strings into full 2-dimensional arrays*, without overwriting existing data. For example, if:

$$JSON={"A":{"1":"Hi","3":"there"},"B":{"1":"Hello","4":"you"}}

then

padJSON ( $$JSON ; "A¶B¶C¶D¶E" ; "1¶2¶3¶4" ) =

{"A":{"1":"Hi","2":"","3":there"","4":""},"B":{"1":"Hello","2":"","3":"","4":"you"},"C":{"1":"","2":"","3":"","4":""},"D":{"1":"","2":"","3":"","4":""},"E":{"1":"","2":"","3":"","4":""}}

which is a full 5x4 array with existing keys for every permutation of the label lists "A¶B¶C¶D¶E" and "1¶2¶3¶4".

My reasons for needing this function are my own. I'm involved in some very dark JSON doings.

Note that this function has been needlessly complicated by the fact that FileMaker's value list functions insert garbage carriage returns... IE, rightvalues("A¶B¶C",1) is "C¶". So I I had to add the variables "theRightRowValues", "FileMakerFixRows", "theRightColValues", and "FileMakerFixCols" to work around this, marring the pristine beauty of my formerly elegant custom function. Oh well, it's a living.

(*Please note I am referring to an abstract tabular array of data, not a literal JSON array, which requires brackets. We apologize for any confusion caused. The parties responsible have been sacked.)

Comments

		Michael Kupietz, Michael Kupietz FileMaker Consulting Jul 7, 2017
There was a glitch in the first version I posted. It would return a JSON error on the last row if you passed it a null value "" in the JSON field instead of a valid JSON string. Fixed now. For now, it will probably still choke if you pass it invalid JSON, but at least if you pass it nothing it will reply with an valid empty, initialized JSON array. This way you can auto-enter initialized JSON arrays into text fields on creation.

		19752, Japan Jul 12, 2017
Terminologically, you don't use brackets [], so it is not array. https://www.w3schools.com/js/js_json_arrays.asp

		Michael E. Kupietz, FileMaker Consultant, San Francisco, CA Jul 12, 2017
It's not a JSON array. The data is a two-dimensional array, rows and columns of tabular data. In terms of the JSON structure, yes, it's stored in an ordinary JSON object, not a JSON array. I've added a clarification to that effect.

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

padJSON ( JSON ; rowLabels ; columnLabels )

Comments

Support this website.

Support this website.