JSON2ScriptParameters ( json ; i ; namespace )

Create scriptvariables from key/value pairs from the root of a JSON and repeating variables from rootarrays

Average rating: 5.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:

first input: JSON2ScriptParameters ( $json ; "" ; "" )

The $json can be a field (or a variable) that contains something like:

{"Company": "Apple Computer Inc.","First.Name": "Sofia","Last.Name": "Zhuravetc","Website": "www.apple.com","EmailAddresses": [{"Address": "s.zhuravetc@apple.com","Type": "Work"},{"Address": "sofiaz98@yahoo.com","Type": "Personal"}]}

Sample output:

In the dataviewer the first input would result in values in the following variables:

$Company : Apple Computer Inc.
$First.Name : Sofia
$Last.Name : Zhuravetc
$Website : www.apple.com
$EmailAddresses : [{"Address": "s.zhuravetc@apple.com","Type": "Work"},{"Address": "sofiaz98@yahoo.com","Type": "Personal"}]

A $[namespace]max_repetition variable contains the highest repetitionnumber of the array and is only created when an array is evaluated.

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

/*
##################################################################
#
# JSONParameters function
# Format:	JSON2ScriptParameters ( json ; i ; namespace )
#
# Version:	1.10/ 2023-04-22
#
# Parameters:
# json:	Any valid JSONobject
# i:		repetition for arrayparameters, put in 0 or "", is
#		for recursive use by CF
# namespace:	Namespace to avoid conflicts with other variables
#
# Data returned:
# JSONobject converted into $parameters for use in scripts
#
# Description:
# This function is intended as an equivalent to the Evaluate/Let
# construct, where script variables are loaded from "$var1=15;$var2=...etc".
# It creates $variables from the key list of a JSON object.
# This function evaluates only the root level because contained objects
# can contain identical keys, that could be overwritten during evaluation.
# Evaluate contained objects in subscripts or use namespacing to avoid
# overwriting variables.
#
# Author:
# Menno van Beek
#
# 20210324 1.0	Initial version
# 20210620 1.1	Added support for "dotted" keys for fmp 19.3.1+
# 20210719 1.2	Added support for namespaces
# 20211129 1.3	Handle quoted "numbers" as number instead of text
# 20211207 1.4	Changed evaluationorder of datatype into:
#			.	NaN/TimeStamp/Date/Time/Number/Text
#			.	Added support for "dotted" keys for 'older' fmp <19.3
# 20211222 1.5	Added namespace to $max_repetition for the arraycount
# 20220121 1.6	Variablenames may not contain ,+-*\/^&=≠><()[]{}";:$
# 20220513 1.7	Changed NaN-calculation to rate strings like 015 as NaN
# 20220703 1.8	Fixed arraycheck of value not to remove [ and ] characters
# 20230418 1.9	Added support for $$keys to be used as global variables
# 20230422 1.10	Clear temporary indexnumber variables immediately after use
#
# ©2023 - Menno van Beek - Van Beek Zakelijke Software - the Netherlands
#
################################################################## */

While ( [

/* Clean whitespaces from the start of the json and get first character */
	c1 = Left ( Trim (
		Substitute ( json ; [ Char ( 9 ) ; " " ] ; [ Char ( 10 ) ; " " ] ;
			[ Char ( 13 ) ; " " ] ; [ Char ( 32 ) ; " " ] ;
			[ Char ( 160 ) ; " " ] ; [ "\/" ; "/" ] ) ) ; 1 ) ;
/* Namespace cannot contain [ or ] */
	ns = Substitute ( namespace ; [ "[" ; "_" ] ; [ "]" ; "_" ] ) ;
/* Check if there should be a JSON and list the keys in first level */
	keys = Case ( c1 = "{" or c1 = "[" ; JSONListKeys ( json ; "" ) ) ;
/* Set initial value of iterator */
	x = 0 ;
/* Check app versionnumber */
	appversion = Substitute ( Filter ( Get (ApplicationVersion) ; "1234567890." ) ; "." ; ¶ ) ;
	major = GetAsNumber ( GetValue ( appversion ; 1 ) ) ;
	minor = GetAsNumber ( GetValue ( appversion ; 2 ) ) / 100 ;
	build = GetAsNumber ( GetValue ( appversion ; 2 ) ) / 100000 ;
	hdtks = If ( ( major + minor + build ) ≥ 19,03001 ; 1 ; 0 ) /* handle dotkeys */

] ;

/* Keys must contain values and may not start with ? (≈JSONerror-capture) */
	x < ValueCount ( keys ) and
	Left ( keys ; 1 ) ≠ "?"

; [

/* Set the iterator */
	x = x + 1 ;
/* Get the current keyname from the JSON */
	key = GetValue ( keys ; x ) ;
/* Edit path if key has a dot and can be handled */
	khad = PatternCount ( key ; "." ) ;
	jkey = Case (
			khad and hdtks ; ".['" & key & "']" ;
			khad ; Substitute ( key ; [ "." ; "_" ] ) ;
			key ) ;
/* Get the value from the current key */
	rawval = JSONGetElement ( If ( hdtks or not ( khad ) ; json ; Substitute ( json ; [ Quote ( key ) ; Quote ( jkey ) ] ) ) ; jkey ) ;
/* Check value for an array of values */
	val = If ( Left ( rawval ; 1 ) = "[" and not ( PatternCount ( rawval ; "\":" ) or PatternCount ( rawval ; "\" :" ) ) and GetValue ( JSONListKeys ( rawval ; "" ) ; 1 ) = 0 ;
		Substitute ( rawval ;
			[ If ( PatternCount ( rawval ; "\"" ) ; "\",\"" ; "," ) ; ¶ ] ;
			[ "[" ; "" ] ; [ "]" ; "" ] ; [ "\"" ; "" ] ) ; rawval ) ;
/* Set parameter_repetition in case the current value is an array */
	prp = If ( Exact ( key ; Filter ( key ; "1234567890" ) ) ; GetAsNumber ( key ) + 1 ) ;
/* Load the current value as $variable, try to load as native datatype, otherwise datatype is text */
	dpt = Middle ( Pi ; 2 ; 1 ) ;
	nan = Case (
			IsEmpty ( Filter ( Left ( val ; 1 ) ; "1234567890-." ) ) ; 1 ;
			Exact ( Filter ( val ; "1234567890" ) ; val ) and Left ( val ; 1 ) = "0" ; 1 ;
			ValueCount ( val ) ≠ 1 ; 1 ;
			0 ) ;
/* Substitute illegal characters in key for underscores */
	isGlobal = If ( Left ( key ; 1 ) = "$" ; "$" ) ;
	key = Substitute ( key ; [ "," ; "_" ] ; [ "+" ; "_" ] ; [ "-" ; "_" ] ; [ "*" ; "_" ] ; [ "/" ; "_" ] ; [ "\\" ; "_" ] ; [ "^" ; "_" ] ; [ "&" ; "_" ] ;
					[ "=" ; "_" ] ; [ "≠" ; "_" ] ; [ ">" ; "_" ] ; [ "<" ; "_" ] ; [ "(" ; "_" ] ; [ ")" ; "_" ] ; [ "[" ; "_" ] ; [ "]" ; "_" ] ;
					[ "{" ; "_" ] ; [ "}" ; "_" ] ; [ "\"" ; "_" ] ; [ ";" ; "_" ] ; [ "::" ; "_" ] ; [ ":" ; "_" ] ; [ "$" ; If ( Left ( key ; 1 ) ≠ "$" ; "_" ) ] ; [ "__" ; "_" ] ) ;
	par = Evaluate (
			"Let([$" & isGlobal & ns & key & If ( i ; "[" & i & "]" ) & "=" &
				Case (
					nan ; Quote ( val ) ;
					Exact ( val ; GetAsTimestamp ( val ) ) ; "GetAsTimestamp (" & Quote ( val ) & ")" ;
					Exact ( val ; GetAsDate ( val ) ) ; "GetAsDate (" & Quote ( val ) & ")" ;
					Exact ( val ; GetAsTime ( val ) ) ; "GetAsTime (" & Quote ( val ) & ")" ;
					Exact ( Filter ( val ; "1234567890.-" ) ; val ) ; GetAsNumber ( Substitute ( val ; [ "." ; dpt ] ) ) ;
					Quote ( val ) ) &
				If ( prp and Length ( ns ) ; ";$" & ns & "max_repetition =" & prp ) &
				If ( prp ; ";$max_repetition =" & prp ) & 
				"];1)" ) ;
/* If the current value is an array of values (not objects!), call the function recursively */
	mxr = If ( prp ; Evaluate ( "$max_repetition" ) ) ;
	par = Case ( mxr ; JSON2ScriptParameters ( Evaluate ( "$" & isGlobal & ns & key ) ; mxr ; ns ) ) ; 
/* Clear temporary variables ($n and $$n) immediately after they are created and used*/
	clr = If ( mxr ; Evaluate ( "Let([$" & mxr - 1 & "=\"\";$$" & mxr - 1 & "=\"\"];1)" ) ) 

] ;

	"" // Result is empty when no errors are detected, in case of any error the result is "?"

)

You can download a simple example from this website: https://www.filemakertips.nl

Requirement: FMP18+

This function simply creates variables from a given JSON from the root of a JSON. If the root is an array it will create repeating variables and add variable called $max_repetition or $[namespace]max_repetition if the namespace is given.

This function is only recursive in case an array is evaluated.

Dotted keynames will be properly evaluated (in FMP18 too). For example the key "Last.Name" will evaluate as $Last.Name

This function is not intended for very large chuncks of data, but 100 or 1000 key/value-pairs in the root should not be a problem.

Comments

		Menno van Beek, Van Beek Zakelijke Software Jan 21, 2022
Corrected 2 little error: The name in the recursive call: JSON2ScriptParameters and added a filter for illegal variablenames

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

JSON2ScriptParameters ( json ; i ; namespace )

Comments

Support this website.

Support this website.