JSON

1. JPARSE

Deserializes the specified JSON string into object or array.

JPARSE(json_string, separator_optional)

Replace:

json_string parameter with a JSON format string you need to parse;

separator_optional optional parameter to make function convert string to array by splitting it.

JPARSE('{"name" : "John Smith", "kids": [{"name": "Jim"}, {"name":"Nicky"}]}') will return a proper object.

Let String__c = '["Feb", "Jan", "Dec"]'.

JPARSE({Stub__c.String__c}) returns an array consisting of following elements: 'Feb', 'Jan', 'Dec'.

Let Area__c = 'Plane; Train; Car'.

JPARSE({Stub__c.Area__c}, ';') returns an array consisting of following elements: Plane, Train, Car.

2. JPARSEXML

Deserializes the specified XML string into object.

JPARSEXML(xml_string)

Replace xml_string parameter with a string (in XML format) you need to parse.

JPARSEXML('<person><address>10 South Riverside Plaza</address><age>9</age><names><first>Jon</first><last>Smith</last></names></person>') returns an object person={address=10 South Riverside Plaza, age=9, names={first=Jon, last=Smith}}.

3. JOBJECT

Builds an object from provided key names and values.

JOBJECT(key1, value1, key2, value2, ...)

Replace:

key1, key2 parameters with strings that are key names

value1, value2 parameters with strings that are key values

If omit some value, it will be stored as null.

JOBJECT() will return an empty object {}.

JOBJECT(key1, value1, key2, value2) will return an object {“key1“:”value1”, “key2“:”value2”}

JOBJECT(key1, value1, key2) will return an object {“key1“:”value1”, “key2“:null}

JOBJECT(
  truevalue, true,
  falsevalue, false,
  evaluatedtruevalue, 1 = 1,
  evaluatedfalsevalue, 1 = 0,
  emptyvalue, ,
  emptystringvalue,'',
  nullvalue2, null,
  numbervalue, 17,
  negativevalue, -456.45,
  jsonvalue, JOBJECT(my2key1, my2value1, my2key2, my2value2)
)

will return an object

{
  "jsonvalue":{
    "my2key2":"my2value2",
    "my2key1":"my2value1"
  },
  "negativevalue":-456.45,
  "numbervalue":17,
  "nullvalue2":null,
  "emptystringvalue":"",
  "emptyvalue":null,
  "evaluatedfalsevalue":false,
  "evaluatedtruevalue":true,
  "falsevalue":false,
  "truevalue":true
}

Object values depend on data passed to the function:

blank space and null will give null value
logical expression and true will give true value. False is got in the same way.
if value is numeric (or has numeric result of calculation), it will be given as a number, without quotes.
to make empty string value, pass ''.
to make a nested object, call JOBJECT function in the proper place.

Empty keys will be ignored

4. JSTRING

Serializes objects into JSON string.

JSTRING(json_object)

Replace json_object parameter with an object you need to convert into string.

JSTRING(JEACH(SPLIT('12,14,15', ','), {$JEach}), true)) returns the following string: '["12","14","15"]'.

5. JGET

Gets a value from JSON content using a path provided.

JGET(json_object, path)

Replace:

json_object parameter with an object you want to use;

path parameter with the path to find a field in the object (or pass index for arrays).

JGET(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza"}'), 'address') returns Jon's address value 10 South Riverside Plaza.

Let String field value is 'June, July, August'.

JGET(JPARSE({Stub__c.String__c}, ','), 2) returns 'August'.

JGET(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza", "kids": ["Andrew","Jane", "Peter"]}'), JPARSE('["kids", 2]')) will return 'Peter' as "kids" list has element with index 2.

6. JPUT

Puts a value into JSON content using a path provided.

JPUT(json_object, path, value)

Replace:

json_object parameter with an object you want to use;

path parameter with the path to find a field in the object to update;

value parameter with the value a field will be updated with.

You can also pass index as a path if you operate with an array.

If value needs to be added as a last element of an array pass -1 as a path.

JPUT(JPARSE('{"name" : "Jon Smith"}'), 'address', '10 South Riverside Plaza') will add Jon's address property and value to JSON object.

JPUT(JPARSE('["Z", "Y", "X"]'), 0, 'A') returns an array with following elements: A, Y, X.

7. JREMOVE

Removes a field from JSON content using a path provided.

JREMOVE(json_object, key_or_jsonobject)

Replace:

json_object parameter with an object you want to use;

key_or_jsonobject parameter with the key to find a field you need to remove or with array element to be removed from JSON array.

If you need to remove multiple values from object you can pass array of object keys as a key_or_jsonobject

Same is true for JSON arrays: if you need to remove several elements from JSON array pass an array of elements to be removed.

JREMOVE(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza", "age" : 9}'), 'address') removes an address field from the JSON content.

JREMOVE(JPARSE('["Z", "Y", "X"]'), 'X') returns an array with following elements: Z, Y.

JREMOVE(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza", "age" : 9}'), SPLIT('name,age', ',')) returna a JSON object with address property only.

JREMOVE(JPARSE('["1a", "2b", "3c", "4d"]'), SPLIT('1a,2b', ',')) returns an array with following elements: 3c, 4d.

8. JCLEAR

Clears a whole JSON object or nested object using a path provided.

JCLEAR(json_object, key_or_jsonobject_optional)

Replace:

json_object parameter with an object you want to use;

key_or_jsonobject_optional optional parameter with the key to find a field you need to remove or with path to nested object as array of elements.

Both

JCLEAR(JPARSE('{"personnames":{"first":"Jon","last":{"a":"Smith","b":"Ryan"}}, "address": "10 South Riverside Plaza", "age" : 9}'))

and

JCLEAR(JPARSE('{"personnames":{"first":"Jon","last":{"a":"Smith","b":"eee"}}, "address": "10 South Riverside Plaza", "age" : 9}'),null)

will return an empty object {}.

JCLEAR(JPARSE('{"personnames":{"first":"Jon","last":{"a":"Smith","b":"Ryan"}}, "address": "10 South Riverside Plaza", "age" : 9}'),'personnames') returns an initial object where nested object 'personnames' will be empty: {address=10 South Riverside Plaza, age=9, personnames={}}

JCLEAR(JPARSE('{"personnames":{"first":"Jon","last":{"a":"Smith","b":"Ryan"}}, "address": "10 South Riverside Plaza", "age" : 9}'),JPARSE('["personnames","last"]')) returns an initial object where 2nd-level nested object 'last' will be empty: {address=10 South Riverside Plaza, age=9, personnames={first=Jon, last={}}}

9. JSIZE

Returns a number of elements in JSON content.

JSIZE(json_object)

Replace json_object parameter with an object or array you want to get size of.

JSIZE(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza"}')) returns 2 as a number of key-value pairs in JSON content provided.

JSIZE(SPLIT('Name, Address, Age, Hair color'), ',') returns 4.

10. JEXIST

Checks if an element exists in JSON.

JEXIST(json_object, path)

Replace:

json_object parameter with an object you want to use ;

path parameter with the path to find a field you need to check (or index if you work with JSON array).

JEXIST(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza"}'), 'Jon Smith') returns true as Jon Smith node is present in JSON content.

JEXIST(JPARSE('["one" , "two", "three", "four"]'), 2) returns true as element with index 2 is present in list.

JEXIST(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza", "appearance": {"eyes":"blue", "hair":"dark"}}'), JPARSE('["appearance", "hair"]')) returns true as "appearance" object has key "hair".

JEXIST(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza", "kids": ["Andrew","Jane", "Peter"]}'), JPARSE('["kids", 2]')) returns true as "kids" list has element with index 2.

11. JMERGE

Merges 2 JSON objects into one.

JMERGE(json_object, json_object)

Replace both json_object parameters with objects you want to merge.

JMERGE(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza"}'), JPARSE('{"appearance": {"eyes":"blue", "hair":"dark"}}')) will return object {address=10 South Riverside Plaza, appearance={eyes=blue, hair=dark}, name=Jon Smith}.

12. JEACH

Executes specified function for each element of the list.

JEACH(json_array, function, exclude_nulls_boolean)

Replace:

json_array parameter with an array you want to use;

function parameter with expression to be executed for each array element;

optional exclude_nulls_boolean parameter with true to have null values be excluded from the resulting array, or with false, if include null values.

You can get current element by using the merge field "{$JEach}" and current element index by using the merge field "{$JEachIndex}".

Let String__c = 'Andrew,Alex,Helen,Ann,Robert'.

JOIN(JEACH(JPARSE({Stub__c.String__c}, ','), IF(STARTS({$JEach}, 'An'), {$JEach}, null), true), ',') returns the following string 'Andrew, Ann'.

Let String__c = 'Andrew,Alex,Helen,Ann,Robert'.

'[' + JOIN(JEACH(JPARSE({Stub__c.String__c}, ','), {DBL_QUOTE} + TEXT({$JEachIndex}) + : + ' ' + {$JEach} + {DBL_QUOTE}), ',') + ']' returns the following string '["0: 'Andrew","1: Alex","2: Helen","3: Ann","4: Robert"]'.

13. JEACHMAP

Executes specified functions for each key and/or value of the JSON object.

JEACHMAP(json_object, key_function, value_function)

Replace:

json_obect parameter with an object you want to use;

key_function parameter with expression to be executed for each key of the object;

value_function with expression to be executed for each value of the object.

If you don't need to trigger function execution for object keys pass {$JEachKey} as a key_function parameter, if no function needs to be run for object values - pass {$JEach} as value_function parameter.

You can get current element by using the merge field "{$JEach}" and you can get current element index by using the merge field "{$JEachIndex}".

JEACHMAP(JPARSE({$Environment}), {$JEachKey}, IF(INSTANCEOF({$JEach}, Decimal), MULT({$JEach}, 2), {$JEach})) doubles all numeric values of the $Environment context object.

JEACHMAP(JPARSE({$Environment}), IF({$JEach} = true, UPPER({$JEachKey}), {$JEachKey}), {$JEach}) converts to uppercase only those object keys which values are equal to true.

14. JVAR

Defines formula local variables.

JVAR(var1_name, va1_value_function, var2_name, va2_value_function, ..., value_function)

Replace:

var1_name, var2_name , etc. parameters with variables names;

va1_value_function, va2_value_function, etc parameters with an expression to be executed as the value for the variables;

value_function with an expression to be executed as the result of the function.

To get access to the variable use the merge field "{$JVar.var1_name}", where "var1_name" is the variable name.

JVAR('var1', 1, 'var2', 2, {$JVar.var1} + {$JVar.var2})- the result of this expression will be "3".

15. JFOR

Executes specified function for each element of the list.

JFOR(from_decimal, to_decimal, expression, exclude_nulls_boolean_optional)

Replace:

from_decimal parameter with a number to start loop from;

to_decimal with a number to stop loop at;

expression with expression to be executed for each iteration;

optional exclude_nulls_boolean_optional parameter with true to have null values be excluded from the result, or with false, if include null values.

JFOR(1, 10, TEXT(ADDDAYS({$System.Date}, {$JeachIndex} ,false)), false) will return list of dates from current date + 1 day to current date + 10 days.

Last updated 8 months ago