JSON.ArraySort ( array ; nestedPath ; fmDataType ; direction ; sortLocale )

Sort a json array.

Be the first to rate this function Log in to vote

Josh Willing - Show more from this author
Willing Apps

Sample input:

JSON.ArraySort("[5, 3, 8, 1, 2]"; ""; "number"; "asc"; "")

Sample output:

[1,2,3,4,5]

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

/**
 * =====================================
 * JSON.ArraySort ( array ; nestedPath ; fmDataType ; direction ; sortLocale )
 *
 * RETURNS:
 *		Array sorted by values at nestedPath, interpreted as fmDataType
 *
 * PARAMETERS:
 *		array | json array
 *		nestedPath? | if the array is an array of objects or arrays, the nested path. e.g. "my.inner.date"
 *		fmDataType? | "text"(default if left empty), "number", "date", "time", "timestamp"
 *		direction? | "asc"/"ascending"(default if left empty), or "desc"/"descending"/"dsc"
 * 		sortLocale? | optional locale as defined here: https://help.claris.com/en/pro-help/content/sortvalues.html
 *
 * DEPENDENCIES: none
 *
 * NOTES:
 *		- this is an "unstable sort", and items with the same sort value may be reversed when sorting descending.
 *			- E.g., sorting this by "date" "descending", will return the array in reverse order
 *				input: [
 *					{"id":1, "date":"1/2/2023"},
 *				 	{"id":2, "date":"1/2/2023"},
 *					{"id":3, "date":"1/2/2023"}
 *				]
 *				output: [
 *					{"id":3, "date":"1/2/2023"},
 *				 	{"id":2, "date":"1/2/2023"},
 *					{"id":1, "date":"1/2/2023"}
 *				]
 *		- strings that contain \n chars will probably be converted to \r. This is a quirk of JSONGetElement ¯\_(:|)_/¯. E.g.:
 *				input: [ "aaa\nbbb", "ccc\nddd" ]
 *				output: [ "aaa\rbbb", "ccc\rddd" ]
 *
 * HISTORY:
 *		2025-03-12	Joshua Willing	josh@willingapps.com - created
 *		2025-03-13	Joshua Willing	josh@willingapps.com
 *			- support "dsc"
 *			- prevalidate json is an array
 *			- handle numbers with decimals
 *			- convert CRs and LFs to spaces before sorting as text (not perfect but reasonable choice)
 *		2025-03-18	Joshua Willing	josh@willingapps.com
 *			- THANK YOU steve_ssh for all the code in this revision!
 *			- use arithmetic to encode/decode the array index for numeric sorting
 *			- use dwindling list optimization in last While
 *			- minify input array before repeatedly reading from it
 *
 * =====================================
 */

Case (
	JSONGetElementType ( array ; "" ) <> JSONArray ;
		"? * must be array" ;
	not IsEmpty ( fmDataType ) and IsEmpty ( FilterValues ( fmDataType ; "text¶number¶date¶time¶timestamp" ) ) ;
		"? * invalid fmDataType. Use 'text', 'number', 'date', 'time', or 'timestamp'" ;
	not IsEmpty ( direction ) and IsEmpty ( FilterValues ( direction ; "asc¶ascending¶desc¶descending¶dsc" ) ) ;
		"? * invalid direction. Use 'asc' or 'desc'" ;

Let ([

	// This is our compromise: Settle on a max precision allowed.
	~MAX_PRECISION_ALLOWED = 10 ;

	// normalize params
	// minify json to improve performance. This will also pre-parse json in fm22+.
	array = JSONGetElement ( array ; "" ) ;
	fmDataType = If ( IsEmpty ( fmDataType ) ; "text" ; fmDataType ) ;
	direction = If (
			direction = "desc" or direction = "descending" or direction = "dsc" ;
			"desc" ;
			"asc"
	) ;

	~isText = fmDataType = "text" ;
	~countElements = ValueCount ( JSONListKeys ( array ; "" ) ) ;

	// Stuff needed for text sorting
	~zeros = "00000000000" ;
	~paddedIndexLength = Length ( ~zeros ) ;

	// Stuff needed for numeric sorting
	~precisionNeededForIndex = Ceiling ( Log ( ~countElements ) ) + 1 ;
	~multiplierForIndex = 10 ^ ~precisionNeededForIndex ;
	~multiplierForValue = 10 ^ ~MAX_PRECISION_ALLOWED ;
	~fullMultiplier = ~multiplierForIndex * ~multiplierForValue ; // Some huge power of 10; enough to hold the index separate from value, sans collision


	~list = While ([
			~sortDataType = If ( fmDataType = "text" ; 1 ; 2 ) ;
			~sortDataType = If ( direction = "desc" ; ~sortDataType * -1 ; ~sortDataType ) ;

			~idx = 0 ;
			~list = "" ;
			~res = "[]"
		] ;
			~idx < ~countElements ;
		[
			~val = JSONGetElement ( array ; "[" & ~idx & "]" & nestedPath ) ;
			~val = Case (
					fmDataType = "number" ;
						GetAsNumber ( ~val ) ;
					fmDataType = "date" ;
						GetAsNumber ( GetAsDate ( ~val ) ) ;
					fmDataType = "time" ;
						GetAsNumber ( GetAsTime ( ~val ) ) ;
					fmDataType = "timestamp" ;
						GetAsNumber ( GetAsTimestamp ( ~val ) ) ;
					// else treat as "text". Remove newlines
						Substitute ( ~val ;
							[ Char(13) ; "¥" ] ;
							[ Char(10) ; "¥" ]
						)
				) ;

			~valueCombinedWithIndex = If ( fmDataType = "text" ;
				// for text, pad the index with leading zeros and append to the text value
				~val & "." & Right ( ~zeros & ~idx ; ~paddedIndexLength ) ;
				// for numbers, hide the index out in far away digits
				Let ([
					~indexEncoded = ~idx / ~fullMultiplier ;
					~valueWithTruncatedPrecision = Truncate ( ~val ; ~MAX_PRECISION_ALLOWED )
				];
					~valueWithTruncatedPrecision + ~indexEncoded
				)
			) ;

			~list = List ( ~list ; ~valueCombinedWithIndex ) ;
			~idx = ~idx + 1
		] ;
			SortValues ( ~list ; ~sortDataType )
		) ;

	~result = While (
		[
			~i = 0 ;
			~res = "[]" ;
			// move ~list into this While's scope
			~list = ~list
		] ;
			not IsEmpty ( ~list ) ;
		[
			~i = ~i + 1 ;
			~row = GetValue ( ~list ; 1 ) ;
			~list = Replace( ~list ; 1 ; Length( ~row ) + 1 ; "" ) ;
			// Decode index--we stored it via concatenation for text and arithmetic for numbers
			~srcIdx = If ( ~isText ;
				GetAsNumber ( Right ( ~row ; ~paddedIndexLength ) ) ;
				Mod ( ~row * ~fullMultiplier ; ~multiplierForIndex )
			) ;
			~jType = JSONGetElementType ( array ; ~srcIdx ) ;
			~val = JSONGetElement ( array ; ~srcIdx ) ;
			~res = JSONSetElement ( ~res ; ~i-1 ; ~val ; ~jType )
		] ;
			~res
		)
];
	~result
)

) // end if

Sort an array by the value at `nestedPath` (leave empty if the array does not contain objects/arrays). Specify `fmDataType` to interpret the value at `nestedPath` as a specific filemaker data type, e.g. text/date/time/timestamp/number. Specify asc/desc for direction. And specify the sortLocale if needed to sort text for different locales.

This functions should gracefully handle both comma and periods as decimal separators. Please see the CF header for other caveats.

Examples:

Input: JSON.ArraySort("[5, 3, 8, 1, 2]"; ""; "number"; "asc"; "")
Output: [1,2,3,4,5]

Input: JSON.ArraySort("[{\"user\": {\"age\": 35}}, {\"user\": {\"age\": 21}}, {\"user\": {\"age\": 42}}]"; "user.age"; "number"; "asc"; "")
Output: [{"user":{"age":21}},{"user":{"age":35}},{"user":{"age":42}}]

Comments

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

JSON.ArraySort ( array ; nestedPath ; fmDataType ; direction ; sortLocale )

Comments

Support this website.

Support this website.