@JsonFormatArray ( json )

Formats JSON Arrays in two columns: elements hierarchy and values.

Average rating: 3.0 (2 votes) Log in to vote

Alex Zueiv - Show more from this author
Tashkent
https://github.com/zueiv

Sample input:

@JsonFormatArray ( "[{\"language\":\"en\",\"str\":\"Hello World!\"},{\"language\":\"ru\",\"str\":\"Привет Мир!\"}]" )

Sample output:

[
    {
        "language"  : "en",
        "str"       : "Hello World!"
    },
    {
        "language"  : "ru",
        "str"       : "Привет Мир!"
    }
]

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

// @JsonFormatArray ( json )
//
// A recursive function for formatting JSON Arrays.
// Created 2018-08-18 by Alex Zueiv
//
// Calls the original JSONFormatElements() function and then adjusts the
// result to emulate a two-column view (hierarchy of the elements and a
// list of their values) by aligning the values using additional spaces.
//
// This function gets the first element ".[0]" of the passed array to calculate
// the alignments, so all elements of the array at the root level must have
// the same structure, for example:
//
// @JsonFormatArray ( "[{\"language\":\"en\",\"str\":\"Hello World!\"},{\"language\":\"ru\",\"str\":\"Привет Мир!\"}]" )
//
// Then it applies the calculated alignments to the entire array with a single
// call to the function Substitute(). This way it can handle arrays of any size
// almost as fast as the original function.
//
// This function calls it's recursive subroutine by setting isolated global
// variables and passing an empty parameter to itself. If you call it with an
// empty parameter, it returns the same error as the original function unless
// the global variable $$json is defined in your environment. If the function
// parameter is not an array, it returns the result of the original function.


If ( not IsEmpty ( json ) ;

	/* main function */
	Let ( JSN = JSONFormatElements ( json ) ;
		If ( not IsEmpty ( Filter ( "?{" ; Left ( JSN ; 1 ) ) ) ;
			JSN ;
			Let ( [
				path = $$path ; $$path = "" ;
				keys = $$keys ; $$keys = "" ;
				json = $$json ; $$json = "[" & JSONGetElement ( JSN ; 0 ) & "]" ;
//				$$TEST = "" ;
				TQK = UniqueValues ( @JsonFormatArray ( "" ) ) ;
				TQK = Substitute ( Left ( TQK ; Length ( TQK ) - 1 ) ; Char ( 9 ) ; "    " ) ;
				$$path = path ; $$keys = keys
			] ;
				If ( Left ( TQK ; 1 ) = "?" ;
					Let ( $$json = json ; TQK ) ;
					Let ( [
						MX = Ceiling ( Evaluate ( 
							"max(length(" &
								Substitute ( Quote ( TQK ) ; "\¶" ; 
									"\");length(\"" 
								) & 
							"))" 
						) / 4 ) * 4 + 1 ;
						SPS = Right ( Substitute ( SetPrecision ( 1/3 ; MX ) ; 3 ; " " ) ; MX ) ;
						// add leading carriage returns to distinguish the same keys at different hierarchy levels
						QRTQK = Replace ( Substitute ( Quote ( TQK ) ; "\¶" ; "\"¶\"\¶" ) ; 1 ; 1 ; "\"\¶" ) ;
						SBST = Evaluate (
							"let(s=" & Quote ( SPS ) & ";¶let(k=" &
								Substitute ( QRTQK & ¶ ; ¶ ;
									";\";[\""e(k)&\";\""e(k&left(s;length(s)-length(k)))&\"]\"&\¶)&¶let(k="
								) & 
							"\"\";k))" 
						) ;
						$$json = Substitute ( JSN ; Char ( 9 ) ; "    " ) ;
						RSLT = Evaluate ( "substitute($$json" & SBST & ")" ) ;
						$$json = json
					] ;
						RSLT
					)
				)
			)
		)
	) ;

	/* a recursive subroutine that returns a list of tabulated quoted keys (TQK) */
	Let ( [
		PRNT = JSONGetElement ( $$json ; $$path ) ;
		ARRAY = If ( Left ( PRNT ; 1 ) = "[" ; True ) ;
		THIS = GetValue ( $$keys ; 1 ) ;
		THIS = If ( ARRAY and not IsEmpty ( THIS ) ; "[" & THIS & "]" ; THIS ) ;
		CHLD = JSONListKeys ( $$json ; $$path & THIS ) ;
//		$$TEST = List ( $$TEST ; $$path & THIS & ":" & Quote ( CHLD ) ) ;
		FORTH = RightValues ( $$keys ; ValueCount ( $$keys ) - 1 ) ;
		LEVEL = PatternCount ( $$path ; "." )
	] ;
		Case (
			Left ( PRNT ; 1 ) = "?" ;
				List ( PRNT  ; "* Function JSONGetElement()" ; "  Parameter keyOrIndexOrPath = " & Quote ( $$path ) ) ;
			Left ( CHLD ; 1 ) = "?" ;
				List ( CHLD  ; "* Function JSONListKeys()" ; "  Parameter keyOrIndexOrPath = " & Quote ( $$path & THIS ) ) ;
			List (
				If ( not IsEmpty ( THIS ) and not ARRAY ;
					Right ( Substitute ( SetPrecision ( 1/3 ; LEVEL ) ; 3 ; Char ( 9 ) ) ; LEVEL )
					& Quote ( THIS ) & " "
				) ;
				If ( not IsEmpty ( CHLD ) ;
					Let ( [
						path = $$path ; $$path = $$path & THIS & "." ;
						keys = $$keys ; $$keys = CHLD ;
						RSLT = @JsonFormatArray ( "" ) ;
						$$path = path ; $$keys = keys
					] ;
						RSLT
					) 
				) ;
				If ( not IsEmpty ( FORTH ) ;
					Let ( [
						keys = $$keys ; $$keys = FORTH ;
						RSLT = @JsonFormatArray ( "" ) ;
						$$keys = keys
					] ;
						RSLT
					)
				)
			)
		)
	)

)

This CF is intended for developers. It allows you to visually separate the data from the document structure. This makes working with JSON API more comfortable.

Required FM16+ (no runtimes).

The old function (@JsonFormatElemets(); v1) was adopted and replaced here to work with JSON Arrays only, of any size. It is suitable to process arrays of JSON elements generated from DB records.

Comments

		Alex Zueiv, Tashkent May 15, 2018
Version 2.0 of this CF works much faster: http://www.briandunning.com/cf/2130

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

@JsonFormatArray ( json )

Comments

Support this website.

Support this website.