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)] = "Team 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|valto)] |
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 valto - If using a range match (date or number) then this is the TO value in the range. 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. Put an exclamation mark in front of the value to reverse the match and return everything that does not match instead. This works together with the * wildcard, so !John* returns every item whose name does not begin with John. Negation works for text, numbers and dates alike. It also reverses a range, so giving a TO value as well returns the items that fall outside that range rather than inside it. One thing to be aware of is that an item which does not have the property at all is never returned, even by a reversed match, because only items that actually carry the property are considered. An exclamation mark on its own with no value after it is treated as an ordinary character rather than as a reversed match. |
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"}] [JSONWHERE([arr]|id|1|2)] = [{"id": 1, "name":"smith"},{"id": 2, "name":"jones"}] [{"name": "smith", "amount": 100},{"name": "jones", "amount": 200},{"name": "brown", "amount": 300}] [JSONWHERE([arr]|name|!smith)] = [{"name": "jones", "amount": 200},{"name": "brown", "amount": 300}] [JSONWHERE([arr]|name|!s*)] = [{"name": "jones", "amount": 200},{"name": "brown", "amount": 300}] [JSONWHERE([arr]|amount|!100)] = [{"name": "jones", "amount": 200},{"name": "brown", "amount": 300}] [JSONWHERE([arr]|amount|!100|200)] = [{"name": "brown", "amount": 300}] returning the items outside the range |
|
[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 ^. |
[JSONRECORD(|InvoiceNum^Amount|Cost^Description^Qty)] |
|
[JSONRECORDOBJ(prop^Field^datatype^emptymode|...)] |
Converts the Record into a JSON object using a field mapping, so the property names in the JSON can differ from the field names on the Record. Every pipe-separated segment is one mapping, and the parts of a single mapping are separated by a caret. prop: The name of the property as it will appear in the JSON. Spaces are allowed. Field: The Record field to read the value from. Leave this blank to use the property name as the field name. datatype: How the value is written. Text is the default and writes a quoted string. Number writes an unquoted number and understands currency symbols, thousands separators and parentheses for negatives, so $1,234.50 becomes 1234.50. Boolean writes true or false. Raw writes the value as JSON rather than as a string, which is how a value that already holds an object or an array gets nested. emptymode: Set to OMIT to leave the property out of the object entirely when the field is empty. Set to BLANK to set the property to a blank value "". Leave it off or not filled in and an empty field is written as null. The variable always acts on the Record in context, so the alternate Record bracket syntax selects the alternate Record just as it does for any other variable. This variable reads header fields only. To include line items, build them with the Build JSON action and nest the result as a Raw property. |
[JSONRECORDOBJ(Vendor^VendorName)] = {"Vendor":"Acme Supply"} [JSONRECORDOBJ(Vendor^VendorName|Amount^Total^Number)] = {"Vendor":"Acme Supply","Amount":1234.50} [JSONRECORDOBJ(Approved^IsApproved^Boolean)] = {"Approved":true} [JSONRECORDOBJ(Notes^Comments^Text] = {} if the Comments field is empty [JSONRECORDOBJ(Notes^Comments)] = {"Notes":null} if the Comments field is empty [JSONRECORDOBJ(Notes^Comments^blank)] = {"Notes":""} if the Comments field is empty [JSONRECORDOBJ(PO_Num)] = {"PO_Num":"PO-1001"} using the property name as the field name [JSONRECORDOBJ(Invoice Number^PO_Num)] = {"Invoice Number":"PO-1001"} showing that spaces are allowed [JSONRECORDOBJ(Vendor|Amount)] = {"Vendor":"Acme Supply","Amount":"1234.50"} using the short form for both [JSONRECORDOBJ(Vendor|Amount^^Number)] = {"Vendor":"Acme Supply","Amount":1234.50} short form but typed as a number [JSONRECORDOBJ(Vendor|Amount|InvoiceDate)] = {"Vendor":"Acme Supply","Amount":"1234.50","InvoiceDate":"5/10/2022"} all three in short form |