Calculation Engine JSON Functions

The following are functions for evaluating or manipulating JSON formatted string to be used in the OAS Calculation Engine. Most of these functions rely on locating nodes or values within a JSON structure using the JSONPath query syntax. A useful JSONPath evaluation tool can be found HERE and used to test your query syntax.

For the purposes of these examples, we will assume a source Tag containing a JSON structure, similar to the following, which is usually passed in as the first argument to all of these functions:

{
    "store": {
        "book": [ 
            {
                "category": "reference",
                "author": "Nigel Rees",
                "title": "Sayings of the Century",
                "price": 8.95
            },
            { 
                "category": "fiction",
                "author": "Evelyn Waugh",
                "title": "Sword of Honour",
                "price": 12.99
            },
            {
                "category": "fiction",
                "author": "Herman Melville",
                "title": "Moby Dick",
                "isbn": "0-553-21311-3",
                "price": 8.99
            },
            { 
                "category": "fiction",
                "author": "J. R. R. Tolkien",
                "title": "The Lord of the Rings",
                "isbn": "0-395-19395-8",
                "price": 22.99
            }
        ],
        "bicycle": {
            "color": "red",
            "price": 19.95
        }
    }
}

JSON Append

Append a value to a JSON Array located in the JSON structure using a JSONPath query. Returns the new JSON structure including the appended value.

Parameters:

• A valid JSON structure
• JSONPath query to locate the array node
• Value to append to the array

Examples:

// appends new book to the array
JSONAPPEND([Tag.Value], "$.store.book", "{'title':'New Book'}")
// appends new book with title stored in Tag2
JSONAPPEND([Tag.Value], "$.store.book", "{'title':'" & [Tag2.Value] & "'}") 
// appends new book with whole structure in Tag2
JSONAPPEND([Tag.Value], "$.store.book", [Tag2.Value]) 

JSON Array Length

Locate a JSON Array within a JSON structure and return the length as an integer. Returns 0 if the array does not exist.

Parameters:

• A valid JSON structure
• JSONPath query to locate the array node

Examples:

JSONLENGTH([Tag.Value], "$.store.book")  // returns 4

JSON Query

Locate any node or value within a JSON structure given a JSONPath query.

Parameters:

• A valid JSON structure
• JSONPath query to locate the node

Examples:

// returns the book array
JSONQUERY([Tag.Value], "$.store.book")
// returns the first book record
JSONQUERY([Tag.Value], "$.store.book[0]")
// returns the 3rd book's title
JSONQUERY([Tag.Value], "$.store.book[2].title") 
// returns array of prices for each book
JSONQUERY([Tag.Value], "$.store.book..price") 

JSON Remove

Locate and remove a node from a JSON structure given a JSONPath query.

Parameters:

• A valid JSON structure
• JSONPath query to locate the node

Examples:

// removes the whole book array
JSONREMOVE([Tag.Value], "$.store.book") 
// removes the 4th item in the book array
JSONREMOVE([Tag.Value], "$.store.book[3]") 
// removes the title from 4th book
JSONREMOVE([Tag.Value], "$.store.book[3].title") 

JSON Replace

Locate a node with a JSONPath query and completely replace it with a supplied valid JSON string.

Parameters:

• A valid JSON structure
• JSONPath query to locate the node
• String containing valid JSON

Examples:

// replace book array with new JSON structure
JSONREMOVE([Tag.Value], "$.store.book", "{'key':'value'}") 
// replace the 4th item with supplied Tag2.Value
JSONREMOVE([Tag.Value], "$.store.book[3]", [Tag2.Value]) 

JSON Set

Locate a node with a JSONPath query and set the value for a given key.

Parameters:

• A valid JSON structure
• JSONPath query to locate the node
• Key of item to set
• Value to set, either a valid string, number, boolean, or JSON structure

Examples:

// sets new value for bicycle.color
JSONSET([Tag.Value], "$.store.bicycle", "color", "blue")
// sets key from Tag2 to new value
JSONSET([Tag.Value], "$.store.bicycle", [Tag2.Value], "new value")
// both key and value pulled from Tags
JSONSET([Tag1.Value], "$.store.bicycle", [Tag2.Value], [Tag3.Value]) 

JSON Validate

Validate a string by testing that it can be parsed as properly formatted JSON. Returns true if the string passes validation. Alternatively, you can use a tag of JSON Data Type which validates JSON formatting and returns bad quality if parsing fails.

Parameters:

• String to be parsed

Examples:

JSONVALIDATE([Tag.Value])
JSONVALIDATE("{'tag':'" & STR([Tag.Value]) & "'}")