JSONSqlQueryResult ( fieldlist ; keylist ; keytypes ; fromwhereclause )

Execute SQL-statement and format directly into JSON, without dependencies (for fmp 18+)

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

Menno van Beek - Show more from this author
Van Beek Zakelijke Software
https://www.vbzs.nl

Sample input:

JSONSqlQueryResult (
List (
GetFieldName ( SalesLines::Number ) ;
GetFieldName ( SalesLines::Description ) ;
GetFieldName ( SalesLines::Price ) ;
GetFieldName ( SalesLines::Amount ) ) ;
"" ; /* keylist may be left empty, see description in function */
List ( JSONNumber ; JSONString ; JSONNumber ; JSONNumber ) ; /* keytypeslist may be left empty. Do not quote() the keytypes, only use JSONString/JSONNumber or 1/2. */
"FROM \"SalesLines\" WHERE \"SalesLines\".\"SalesID\"=123456" )

Sample output:

[
{
"Amount" : 10,
"Description" : "item",
"Number" : 1,
"Price" : 10
},
{
"Amount" : 27,
"Description" : "items",
"Number" : 3,
"Price" : 9
}
]

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

/* 
################################################################## 
# 
#  JSONSqlQueryResult function
#  Format: 		JSONSqlQueryResult ( fieldlist ; keylist ; keytypes ; fromwhereclause ) 
# 
#  Version: 		0.93 / 2022-11-19
# 
#  Parameters:
#  fieldlist: 		Fields to query, must be FQFN (TO::Name)
#  keylist:  		Keynames for the JSON, if empty, fieldnames will be used
#  keytypes: 		JSONString or JSONNumber for each key. If empty fieldlist will be used
#  fromwhereclause: 	FROM tablename WHERE clause for the query
# 
#  Requirement: 	FileMaker 18+ because of the use of While()
#  
#  Dependencies: 	None
# 
#  Data returned: 
#  JSONarray with the result of the given query
# 
#  Description: 
#  The result of an SQL-query will be poured directly into a JSON-array
#  The function requires at least a fieldlist with fully qualified 
#  fieldnames and a the clause containing the from and where clauses.
#
#  Notes:
#  If the keylist-parameter is empty the function will use the fieldlist to construct the 
#  keylist. A complexer query probably needs a keylist depending on weather columns 
#  are used more than once or not or when JOINed tables have identical
#  columnnames.
#  If the keytypes-parameters is empty, the function can determine the keytypes 
#  from the FQFN's in the fieldlist and result in JSONString and JSONNumber only.
# 
#  Author:
#  Menno van Beek
# 
#  20220529 0.91	Beta-version, still need some 'robustification'
#  20220531 0.92	Removed useless fm-version-check
#  20221119 0.93	Corrected typo clause -> fromwhereclause
#  
#  ©2022 - Menno van Beek - Van Beek Zakelijke Software - the Netherlands
# 
################################################################## 
*/

Let ( [ 

/* If keytypes is empty, the function will automatically choose for strings or numbers as output */
	types = Case ( 
		IsEmpty ( keytypes ) ; 
		While ( [ 
			n = ValueCount ( fieldlist ) ; 
			i = 0 ; 
			r = "" 
		] ; i < n ; [ 
			 i = i + 1 ; 
			d = FieldType ( "" ; GetValue ( fieldlist ; i ) ) ; 
			t = GetValue ( Substitute ( d ; [ " " ; ¶ ] ) ; 2 ) ; 
			r = List ( r ; If ( t = "Number" ; JSONNumber ; JSONString ) ) 
		] ; r ) ; 
		keytypes ) ;  
/* Function will use FQFN to create columnnames in the query */
	columnlist = "\"" & Substitute ( fieldlist ; [ "::" ; "\".\"" ] ; [ ¶ ; "\",''||\"" ] ) & "\"" ; 
	keylist = Case ( 
		IsEmpty ( keylist ) ; 
		While ( [ 
			n = ValueCount ( fieldlist ) ; 
			i = 0 ; 
			r = "" 
		] ; i < n ; [ 
			i = i + 1 ; 
			d = GetValue ( Substitute ( GetValue ( fieldlist ; i ) ; "::" ; ¶ ) ; 2 ) ;
			r = List ( r ; d ) 
		] ; r ) ;  
		keylist ) ; 
/* Temporary deviders to use in sql created with ByteOrderMark, after running these will all be converted into ¶ */
	BOM = Char ( 65279 ) ; 
	comma = BOM & "<comma>" & BOM ; 
	return = BOM & "<return>" & BOM ; 

/* Collect the data */
	query = ExecuteSQL ( "SELECT " & columnlist & fromwhereclause ; comma ; return ) ; 
/* Remove ¶ from result and devide records with ¶, the columns stay devided with BOM<comma>BOM for now */
	result = Substitute ( query ; [ ¶ ; "\r" ] ; [ return ; ¶ ] ) ; 
/* Build the JSON result, object after object */
	JSON = While ( [ 
			n = ValueCount ( result ) ; 
			i = 0 ; 
			r = "" 
		] ; 
			i < n 
		; [ 
			i = i + 1 ; 
		/* Devide columns with ¶ */
			record = Substitute ( GetValue ( result ; i ) ; [ comma ; ¶ ] ) ; 
		/* Create an object for each separate record */
			object = While ( [ 
				n = ValueCount ( keylist ) ; 
				i = 0 ; 
				r = "" 
			] ; 	
				i < n 
			; [ 
				i = i + 1 ; 
				k = GetValue ( keylist ; i ) ; 
				/* Get the columndata and restore all ¶ */
				v = Substitute ( GetValue ( record ; i ) ; [ "\r" ; ¶ ] ) ; 
				t = GetValue ( types ; i ) ; 
				/* Create recordobject */
				r = JSONSetElement ( r ; [ k ; v ; t ] ) 
			] ; 
				r 
			) ; 
		/* Add each object to the result in an array */
			r = JSONSetElement ( r ; [ "[" & i - 1 & "]" ; object ; JSONObject ] )
		] ; 
			r 
		)

] ; 

	JSON

)

This function is intended for straightforward SQL-statements and to have the result poured directly into JSON-format. The function requires fmp 18+ and does not depend on any other custom-function.

Supply at least a list of "Fully Quallified Field Names" and a FROM and WHERE clause. The function will then create the keynames from the fieldlist, but you can also set your own keylist.
The same for the keytypes. Provide a list with only JSONString and JSONNumber values or leave it empty to have the function choose by checking the fieldtypes.

Comments

		Lazarus Sismanis, Decision Group Nov 19, 2022
Couldn't get the 'keytypes' to work when trying to select number fields as JSONString. Especially problematic when the number field is a UUID(Number).. Also, in the code, 'fromwhereclause' needs to be replaced with 'clauses'. If these are fixed, this is a great custom function!

		Menno van Beek, Van Beek Zakelijke Software Nov 19, 2022
Thanks for the feedback, I corrected "clause" into "fromwhereclause". On the list of keytypes: the keytypes should not be quoted. Enter the list of keytpes as: List ( JSONNumber ; JSONString ) or List ( 2 ; 1 ) or jusr leave it empty to use the native fieldtype (which will always be JSONString if it is not a Number-field)

		Lazarus Sismanis, Decision Group Nov 19, 2022
Thank you so much! Got it working as described.

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

JSONSqlQueryResult ( fieldlist ; keylist ; keytypes ; fromwhereclause )

Comments

Support this website.

Support this website.