<< Click to Display Table of Contents >> Navigation:  Collect > Collect Enterprise > Template Designer Page > Formula Builder > Functions > String Functions > JSON Functions

The JSON (JavaScript Object Notation) functions are used to evaluate data structured in a JSON object and/or a JSON array. A JSON object is a collection of unordered key/value pairs (e.g., {"key":"value", "company":"EarthSoft", "number":11}). A JSON array is an ordered collection of values (e.g., [1,2,"three",{"key":4},[5,6],false]). The key for each JSON object must be a string encapsulated in double quotes. JSON object values and JSON arrays can contain the following types: string, number, Boolean, NULL, a JSON array, or another JSON object. Within EQuIS Collect, the functions perform calculations as data are entered in forms on the Mobile app. Within EQuIS Link, the functions provide a means to transform data when loading to a selected format.

 

Below is a descriptive list of each JSON function and its operations. The examples are applicable to either fields in a Collect form or an EQuIS Link data source, unless otherwise noted.

 

JARRAY

The JARRAY function is used to create a JSON (JavaScript Object Notation) array of objects. This function will go through all records and generate a JSON object for each record, combining them into an array of JSON objects. The function takes an even number of parameters with the odd parameters corresponding to the JSON objects key or property name and the following parameter its value. If a key is not a valid string value or the value is null, then it will be ignored and not added to the JSON object. If there are no rows in the table, then the function returns NULL.

JARRAY(<'ParameterName_1'>,<Parameter_1>,<'ParameterName_2'>,<Parameter_2>…<'ParameterName_N'>,<Parameter_N>)

Aggregate = Yes

 

Example

Collect Form1

 

JARRAY('Location',[Form1.FieldA],'Value',[Form1.FieldB]) = [{"Location": "B-30","Value": "0.08"}, {"Location": "B-31","Value": "1"}, {"Location": "B-32","Value": "3"}]

JFILTER

The JFILTER function filters the contents of a JSON (JavaScript Object Notation) array and returns an array containing elements that match the value associated with the filtered property. The first parameter contains the JSON array, the second parameter is the property key, and the third parameter is the property value. Function input parameters can not be NULL or empty. If no element matches the value, an empty array is returned. If the first parameter is not a JSON array, or if the second parameter is not a string, the function will return NULL.

JFILTER(<'JsonArrayParameter'>,<PropertyKey_1>,<'PropertyValue_1'>)

Aggregate = No

 

Example 1

 

JFILTER([FieldA],'code','B-31') = [{"code":"B-31","description":"Columbus","state":"OH"}]

The formula above returns an array of objects where the value of property key "code" equals "B-31".

Example 2

 

JFILTER([FieldA],'state','TN') = [{"code":"B-30","description":"Chattanooga","state":"TN"},{"code":"B-33","description":"Nashville","state":"TN"}]

The formula above returns an array of objects where the value of property key "state" equals "TN".

JFIND

The JFIND function iterates over each element in the JSON (JavaScript Object Notation) array and searches for the array element(s) that match the key/value pair(s) defined in the function. The first parameter defined in the function is the JSON array. Following the JSON array parameter are key/value pairs which are used to return a value associated with a specific key defined in the last parameter.

JFIND(<JsonArrayParameter>,<PropertyKey_1>,<PropertyValue_1>,...<PropertyKey_N>,<PropertyValue_N>,<PropertyKey_X>)

Aggregate = No

 

Example

 

JFIND([FieldA],'code','B-30','state','TN','description') = Chattanooga

The formula above returns "Chattanooga", which is the value of the "description" key where "code" equals "B-30" and "state" equals "TN".

 

JGET

The JGET function gets a JSON (JavaScript Object Notation) object from an array of objects in the JSON ARRAY string defined in the first parameter where all the key/value pairs defined in the following parameters match those defined in that object. It returns the first JSON object whose property fields contain the property values specified. The function uses the key/value pairs defined in the second and third and following pairs of parameters. Returns NULL if a key is null or if the first parameter is not a JSON array or is empty. It also returns NULL if the JSON ARRAY is empty, if an even number of parameters is passed, or if the key/value conditions are not met. Note that this function takes an odd number of parameters greater than or equal to 3.

JGET(<JsonArrayString>,<PropertyField_1>,<PropertyValue_1>,...<PropertyField_N>,<PropertyValue_N>)

Aggregate = No

 

Example

 

JGET([FieldA],'code','B-32','state','ND') = {"code":"B-32","description":"Grand Forks","state":"ND"}

 

The function returns the JSON object where the "code" key is "B-32" and the "state" key is "ND".

JJOIN

The JJOIN function returns a comma delimited list of values associated to a specific property key of the element(s) in a JSON (JavaScript Object Notation) array. An optional Boolean parameter can be defined and is set to TRUE by default, which will ignore subsequent duplicate values. When the optional Boolean parameter is set to TRUE, the comma delimited list returned is displayed in alphabetical order. When the optional Boolean parameter is set to FALSE, each value, including duplicates, is returned in the order of the array.

JJOIN(<JsonArrayParameter>,<PropertyKey_1>,{<OptionalBooleanParameter_1>})

Aggregate = No

 

Example 1

 

JJOIN([FieldA],'code') = B-30,B-31,B-32

The formula above returns a comma delimited list of the "code" key from the FieldA value, ignoring subsequent duplicate values.

 

Example 2

 

JJOIN([FieldA],'code',FALSE()) = B-30,B-31,B-32,B-31

The formula above returns a comma delimited list of the "code" key from the FieldA value in the order of the array, including duplicates.

JOBJECT

The JOBJECT function creates a JSON (JavaScript Object Notation) object from the parameters, where the first parameter is defined as the key and the second parameter is defined as the value. If the key is NULL and the value is NOT NULL, the key/value pair will not be included in the JSON object. If the key is NOT NULL and the value is NULL, the key/value pair will be included in the JSON object.

JOBJECT(<Parameter_1>,<Parameter_2>)

Aggregate = Yes

 

Example 1

Collect Form1

 

JOBJECT([Form1.FieldA],[Form1.FieldB]) = {"B-30":"["Groundwater"],"B-31":["Soil Sampling"],"B-32":["Groundwater","Surface Water"]}

This formula is returning a JSON object that contains populated keys and associated values, where FieldA is the key and FieldB is the value. The "B-32" key is associated with two values, so the output includes both values in an array.

 

Example 2

Collect Form1

 

JOBJECT([Form1.FieldA],[Form1.FieldB]) = {"B-30":"["Groundwater"],"B-32":["Groundwater","Surface Water"]}

This formula is returning a JSON object that contains populated keys and associated values, where FieldA is the key and FieldB is the value.  The "Soil Sampling" value does not have a populated key and is not included in the JSON object.

 

Example 3

Collect Form1

 

JOBJECT([Form1.FieldA],[Form1.FieldB]) = {"B-30":[null],"B-31":["Soil Sampling"],"B-32":["Groundwater","Surface Water"]}

This formula is returning a JSON object that contains populated keys and associated values, where FieldA is the key and FieldB is the value. Key "B-30" does not have an associated value, which will return NULL and is included in the JSON object.

JREMAP

The JREMAP function allows users to remap the text of the key properties of a JSON (JavaScript Object Notation) array of objects and returns an updated JSON array of objects with new key properties. The first parameter is a JSON array. Each pair of parameters, following the first parameter, list the key as seen in the JSON and then to what value the key's value should be remapped.

JREMAP(<JSONArray>,<Parameter_1>,<RemapParameter_1>...<Parameter_N>,<RemapParameter_N>)

Aggregate = No

 

 

Example 1

 

JREMAP([FieldA],'loc','sys_loc_code','city','city','state','state') = [{"sys_loc_code":"B-30","city":"Chattanooga","state":"TN"},{"sys_loc_code":"B-31","city":"Columbus","state":"OH"},{"sys_loc_code":"B-32","city":"Grand Forks","state":"ND"}]

The formula above remaps the "loc" key to "sys_loc_code" and maps the "city" and "state" keys to be included in the function output.

 

Example 2

 

JREMAP([FieldA],'loc','sys_loc_code','state','state') = [{"sys_loc_code":"B-30","state":"TN"},{"sys_loc_code":"B-31","state":"OH"},{"sys_loc_code":"B-32","state":"ND"}]

The formula above remaps the "loc" key to "sys_loc_code" and maps the "state" key to be included in the function output. The "city" key was not mapped, therefore it was excluded.

 

Example 3 (Dynamic Selector)

 

JREMAP([FieldA],'loc','code','state','description') = [{"code":"B-30","description":"TN"},{"code":"B-31","description":"OH"},{"code":"B-32","description":"ND"}]

The formula above remaps the "loc" key to "code" and the "state" key to "description". The "code" and "description" keys can be used in conjunction with the Dynamic Selector.

 

Example 4 (Dynamic Selector)

 

JREMAP([FieldA],'loc','code') = [{"code":"B-30"},{"code":"B-31"},{"code":"B-32"}]

The formula above remaps the "loc" key to "code". When used with the Dynamic Selector, which requires the keys defined as "code" and "description", the selector list would only display the "code" values. While the "description" key exists in the JSON array, it was not defined in the remap and therefore it was not returned.

 

Example 5 (Dynamic Selector)

 

JREMAP([FieldA],'loc','code','description','description') = [{"code":"B-30","description":"Chattanooga"},{"code":"B-31","description":"Columbus"},{"code":"B-32","description":"Grand Forks"}]

The formula above remaps the "loc" key to "code" and remaps the "description" to "description". When used with the Dynamic Selector, which requires the keys defined as "code" and "description", the selector list would properly display all "code" and "description" values.

JSON

The JSON (JavaScript Object Notation) function returns the value of a property inside of a JSON object or JSON array. The first parameter contains the JSON string representing an array or object. If any of the parameters are NULL or if the first parameter is an empty JSON, then the function returns NULL. The second and following parameters define how to find the requested value. They can be either a property name or an index of an array.

JSON(<Parameter_1>,<Parameter_2>,{<OptionalParameter_1>…<OptionalParameter_N>})

Aggregate = No

 

Example – Collect Form1

 

Example 1

JSON([FieldA],'weather','0','description') = “cloudy”

This formula is extracting the latitude weather property from the JSON object in FieldA. This property is itself a JSON array and therefore the following '0' tells it to get the first element of that array, which is a JSON object. The final 'description' parameter value tells the function to extract the description field value from this object.

 

Example 2

JSON([FieldA],'main','temp') = 22

This formula is looking at FieldA, in this case a weather service field using https://api.openweathermap.org, and extracting particular information, in this case temperature, from the main object.

 

Service Field

CONCAT('https://api.openweathermap.org/data/2.5/weather?lat=',[42],'&lon=',[-82],'&APPID=[PLACE_API_KEY_HERE]&units=metric')