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: