JSON_GET_VALUE

Use the JSON_GET_VALUE script function to query data from a JSON document using a JSONPath expression.

Syntax

JSON_GET_VALUE (JSON, JSONPath)

Parameters

• JSON

  A string containing a valid JSON document
  

• JSONPath

  A valid JSONPath expression
  

More information: https://goessner.net/articles/JsonPath/

Important!

• Ensure that you test JSONPath expressions within an Automation Engine Script before putting them into production.
• JSONPath expressions developed in other tools might not work due to the lack of a common JSONPath standard.

Limitations

JSON documents larger than 1MB are not supported.

Return value

The return value will always be a string, except if the expression matches:

• A number, then an Automic Script number will be returned
• No value, an empty string ("") will be returned
• An object or array of values, then the string will contain a serialized JSON representation of that object or array.

Runtime errors

JSON_GET_VALUE will fail with a runtime error on the following conditions:

• 45334

  If your JSON document is invalid

• 45342

  Syntax Error in JSONPath Expression
  

Note:

The JSON_GET_VALUE can only return one JSON value (string or number). Use JSON_TO_PROCESS if your JSONPath expression returns multiple values, and you want to process them individually.

Examples:

This example uses the script function to extract the email address from the JSON document, and print it in the job report:

:SET &JSON_DOC# = '{"name": "Mick", "lastName" : "Myers", "contact": {"email": "mick.myers@example.com","phone": "++43 1 999992999"}}'
:SET &JSON_VAL# = JSON_GET_VALUE(&JSON_DOC#,"$.contact.email")
:P &JSON_VAL#

Activation report result:

U00020408 mick.myers@example.com

This example uses the script function to check if the JSON response contains an error key. If this is the case, the script is terminated with an error message.

:SET &JSON_DOC# = '{"code":404, "error":"NOT FOUND"}'
:SET &RESPONSE_ERROR# = JSON_GET_VALUE(&JSON_DOC#,"$.error")
:IF &RESPONSE_ERROR#<> ''
:print 'an error has occurred'
:EXIT 10
:ENDIF

Activation report result:

2019-05-13 10:10:31 - U00020408 an error has occurred
2019-05-13 10:10:31 - U00020411 The command 'EXIT 0000000010' in 'SCRI.NEW.TEST', line '00005' stopped the generation of this task.

See also: