JSON.SortArray_v2 ( $jsonConfig )

Sorts a JSONArray using a JSONArray

Be the first to rate this function Log in to vote

Kyle Williams - Show more from this author
Augusto Digital
https://www.youtube.com/channel/UCpJlB7cmVNMjbjgY96MEsng

Sample input:

JSONFormatElements ( JSON.SortArray_v2 (
JSONSetElement ( ""
; [ "JSON" ; $$jsonFoundSet ; JSONArray ]

; [ "Sorts[0].SortKey" ; "Totals" ; JSONString ]
; [ "Sorts[0].IsNumeric" ; 1 ; JSONBoolean]
; [ "Sorts[0].SortOrder" ; "Dsc" ; JSONString ]

; [ "Sorts[1].SortKey" ; "hrsScheduled" ; JSONString ]
; [ "Sorts[1].IsNumeric" ; 1 ; JSONBoolean]
; [ "Sorts[1].SortOrder" ; "Asc" ; JSONString ]
)
))

Sample output:

sorted JSON based on your JSON and configuration array settings

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

/*	Author:		Kyle Williams
	Date:		5/5/2025
	Purpose:		Allow a developer to sort a JSONArray using a JSONArray

  Parameters:
    • configJSON  — must contain:
        • "JSON"  → your source object
        • "Sorts" → an array of sort-instructions, each with:
            • SortKey    (string)
            • IsNumeric  (boolean: 1 or 0)
            • SortOrder  ("Asc" or "Dsc")
  Returns:
    • a JSON array of objects, each with an injected ID field

Example Usage:

JSONFormatElements ( JSON.SortArray_v2 ( 
  JSONSetElement ( ""  
    ; [ "JSON"                ; $$jsonFoundSet    ; JSONArray    ]  

    ; [ "Sorts[0].SortKey"    ; "Totals"       ; JSONString ]  
    ; [ "Sorts[0].IsNumeric"  ; 1                 ; JSONBoolean]  
    ; [ "Sorts[0].SortOrder"  ; "Dsc"             ; JSONString ]  

    ; [ "Sorts[1].SortKey"    ; "hrsScheduled"    ; JSONString ]  
    ; [ "Sorts[1].IsNumeric"  ; 1                 ; JSONBoolean]  
    ; [ "Sorts[1].SortOrder"  ; "Asc"             ; JSONString ]  
  )
))

*/

Let ([
	 src = JSONGetElement ( configJSON ; "JSON" )
	;sortCount = ValueCount ( JSONListKeys  ( configJSON ; "Sorts" ) )
	;keys = JSONListKeys ( src ; "" )
	;nKeys = ValueCount ( keys )

// 1) build a ¶-delimited list of   pad1§pad2§…§ID
	;indexed = 

	While ([
		 i = 1
		;list = "" 
	];
		i ≤ nKeys
	;[
	 recKey = GetValue ( keys ; i )
	;recObj  = JSONGetElement ( src ; recKey )

// build a multi-part prefix for each sort rule
	;prefix = 
		While ([
			 j = 0
			;p = "" 
		];
			j < sortCount 
		;[
			 skey  = JSONGetElement ( configJSON ; "Sorts[" & j & "].SortKey" )
			;isNum = JSONGetElement ( configJSON ; "Sorts[" & j & "].IsNumeric" )
			;order = JSONGetElement ( configJSON ; "Sorts[" & j & "].SortOrder" )
			;raw   = JSONGetElement ( recObj ; skey )
			;num   = Floor ( GetAsNumber ( raw ) * 100 )
	/* … inside your prefix‐building While … */

	;pad =
	  Case(
	    isNum = 1 ;
 	     Let ( [
		 v = If ( order = "Dsc" ; 999999 - num ; num )
		;txt = Right ( "000000" & v ; 6 )
      		] ;
			txt
		)

    // — text fields ascending —
	;order = "Asc"
	;raw

    // — text fields descending: invert every char so that an ascending SortValues() gives you a descending order on the original text —
	;order = "Dsc"
		;Let ( [
			 L = Length ( raw )
			;pos     = 1
			;inv_acc = "" 
      ] ;
        While([ 
		 pos = 1
		;inv_acc = ""
		;L = Length ( raw ) 
	] ; 
		pos ≤ L
	;[
		ch       = Middle ( raw ; pos ; 1 )
		;inv_acc  = inv_acc & Char ( 65535 - Code ( ch ) )
		;pos      = pos + 1
	] ;
		inv_acc
        )
      )  ;

    // — just in case you pass something weird —
    raw
  )

			;p = p & pad & "§"
			;j = j + 1
		];
			p
		)

// append “prefix + real ID” as one line
	;line = prefix & recKey
	;list = list & If ( list = "" ; "" ; "¶" ) & line

	;i = i + 1
];
	list
  )


// 2) sort that list
	;sorted = SortValues ( indexed )

// 3) walk the sorted lines and rebuild a JSON array
	;result = 
		While ([
			 k = 1
			;out = ""
 		];
			k ≤ ValueCount ( sorted )
		;[
			thisLine = GetValue ( sorted ; k )
	// strip off the first sortCount “§…§” pieces
			;recKey = 
				Right (
					thisLine
					;Length ( thisLine ) - Position ( thisLine ; "§" ; 1 ; sortCount )
				 )

      // grab the original object, inject ID, append to out[]
			;recObj = JSONGetElement ( src ; recKey )
			;withID = JSONSetElement ( recObj ; "ID" ; recKey ; JSONString )
			;out    = JSONSetElement ( out ; "[" & ( k - 1 ) & "]" ; withID ; JSONRaw )

			;k      = k + 1
		];
			out
		  )
];
// deliver the sorted array
	result
)

Sorts a JSONArray using a JSONArray

Comments

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

JSON.SortArray_v2 ( $jsonConfig )

Comments

Support this website.

Support this website.