JSONSqlQueryResult ( fieldlist ; keylist ; keytypes ; clauses )

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

Average rating: 4.0 (1 vote) 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 */
"" ; /* keytypeslist may be left empty */
"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 ; clauses ) 
# 
#  Version: 		0.92 / 2022-05-31
# 
#  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
#  clauses: 		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
#  
#  ©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

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 ; clauses )

Comments

Support this website.

Support this website.