JSON Variables
|
Variable |
Action or Value |
Example |
|
[PROP(JSON|el#|property|...)] |
Traverses a string-based JSON object to retrieve the requested property. JSON: This is the actual JSON object. Typically you would refer to a variable here but for samples we use static objects as well. el#: For JSON arrays you need to use an array index to determine which element number to return. This is an integer value that is zero-based. property: The name of the property to return. Use el# and property as many times as you need to get to the property you want. For example, if you had a User object that had an array of teams as a property and you want to get the name of the 2nd team in the list it might look like this: [PROP({id: 1, teams: [{id:5, name:"This Team"},{id: 6, name: "That Team"}]}|teams|1|name)] which would return the value "That Team". The hardest thing to do is to visualize the JSON data to be able to extract the element number and properties in the proper way to code them into the call. Here are a couple of third party utilities that allow you to paste in example JSON code and see the data in a structured way: https://jsonformatter.curiousconcept.com/ * These are 3rd party links and are in no way affiliated with DocMgt. |
[PROP({id: 1, name: "This User"}|id)] = 1 [PROP({id: 1, name: "This User"}|name)] = "This User" [PROP({id: 1, name: "This User", teams: ["Team One","Team Two"]}|0)] = "Team1 One" [PROP({id: 1, teams: [{id:5, name:"This Team"},{id: 6, name: "That Team"}]}|teams|1|name)] = "That Team" [PROP([RESTVALUE]|teams|1|name)] = "That Team" (if RESTVALUE is a variable holding the above JSON data) [PROP([RESTGET(http://api.tvmaze.com/search/shows?q=the+golden+girls)]|0|show|name)] = "The Golden Girls" |
|
[JSONWHERE(JSONArray|prop|val)] |
Returns subset of JSON array by matching property values. Only checks top level JSON object's properties. JSONArray - Array of JSON items. prop - Property to check for match val - Value to match on. This supports using *. For example: *smith This is useful with COUNT and other variables to get a count of the items in the array where a certain property matches a value. You can combine multiple property comparisons by making successive JSONWHERE calls. [JSONWHERE([JSONWHERE([arr]|id|1)]|name|smith)] will return all items where id=1 and name=smith. |
Assuming this array named [arr]: [{"id": 1, "name":"smith"},{"id": 2, "name":"jones"}] [JSONWHERE([arr]|name|smith)] = [{"id": 1, "name":"smith"}] [JSONWHERE([arr]|id|1)] = [{"id": 1, "name":"smith"}] [JSONWHERE([arr]|name|*s*)] = [{"id": 1, "name":"smith"},{"id": 2, "name":"jones"}] |
|
[JSONUNIQUE(JSONArray|prop)] |
Returns a JSON array where duplicate items are removed from the array based on the property specified. JSONArray - Array of JSON items. prop - Property to check for duplicates. |
Return array where the name is unique. [JSONUNIQUE([{"name": "Brad"},{"name": "John"},{"name": "Brad"}]|name)] = [{"name": "Brad"},{"name": "John"}] |
|
[JSONSORT(JSONArray|prop|numeric|desc)] |
Sorts a JSON array by the specified property values. Only checks top level JSON object's properties. JSONArray - Array of JSON items. prop - Property to sort by. numeric - Boolean of whether to sort the values using numeric sorting. true - sort as numbers false - sort as strings (default) desc - Boolean of whether to sort the values descending (largest first). true - sort descending false - sort ascending (default) |
Assuming this array named [arr]: [{"id": 1, "name":"smith"},{"id": 2, "name":"jones"}] [JSONSORT([arr]|name)] = [{"id": 2, "name":"jones"},{"id": 1, "name":"smith"}] [JSONSORT([arr]|name||true)] = [{"id": 1, "name":"smith"},{"id": 2, "name":"jones"}] [JSONSORT([arr]|id|true)] = [{"id": 1, "name":"smith"},{"id": 2, "name":"jones"}] [JSONSORT([arr]|id|true|true)] = [{"id": 2, "name":"jones"},{"id": 1, "name":"smith"}] |
|
[JSONRECORD(id|fields|lifields)] |
Returns a JSON object of the current or specified Record. id = ID of the Record for which to return JSON. Leave blank to use the active Record. fields = The names of the header fields to return. Separate multiple field names by ^. lifields = The names of the line item fields to return. Separate multiple field names by ^. |
[RECORD(|InvoiceNum^Amount|Cost^Description^Qty] |