Query JSON

Note: We have made improvements to the Query JSON action. Query JSON actions must be replaced with the newer version. Read the FAQ.

Use the Query JSONaction A tool for building the processes, logic, and direction within workflows. to extract information from a JSON text string and store it in a variable A placeholder that stores changes each time the workflow runs, such as the information entered in a form, or the name of a file. You can also create variables to store information you use in the workflow, such as an email address..

Some third-party services send or return information in a JSON data structure. Before you can use the information in a workflow, you need to extract it into variables. You can use the Query JSON action to extract pieces of information into variables using JSON queries (also known as JSONPath expressions).

Before you start 

Variables allow you to store and manipulate data and files in your workflows. They can be created by start events, forms, tasks, workflow actions, or automatically created when a workflow is run. See Variables.

JSON is JavaScript Object Notation, a flexible syntax for storing textual information in a structured way. It is one of the primary standards for transmitting information across the internet.

JSON is built of:

  • Key-value pairs, where a key is matched by a colon to a specific value, like "title" : "Gone with the Wind".
  • Objects, where multiple key-value pairs are grouped together as a unit between a set of curly braces {}.
  • Arrays, where a set of objects or key-value pairs are grouped into a list between a set of square brackets [].

Objects and arrays can be nested inside of each other, creating an extremely flexible data structure:


{ 
  "products": {
    "food": [ 
      { 
        "category": "fruit",
        "name": "apple",
        "type": "Red Delicious",
        "price": 2.95
      },
      { 
        "category": "deli",
        "name": "fish",
        "type": "salmon",
        "price": 7.99
      }
    ],
    "homewares": {
      "item" : "towel",
      "color": "blue",
      "size" : "bath",
      "price": 12.95
    }
  }
}

A JSON query is an expression that retrieves the value of one or more keys in a JSON structure by creating a path through the keys. Starting at the outermost object (written as $), navigate through to the target key:

  • To move to a key or object inside another object, use the dot (.) operator.
  • To move to a key or object inside an array, type the item index inside square brackets [2], starting with 0 for the first item.

You can also use wildcards (*) and recursive operators (..) to create a more powerful query. 


{ 
  "products": {
    "food": [ 
      { 
        "category": "fruit",
        "name": "apple",
        "type": "Red Delicious",
        "price": 2.95
      },
      { 
        "category": "deli",
        "name": "fish",
        "type": "salmon",
        "price": 7.99
      }
    ],
    "homewares": {
      "item" : "towel",
      "color": "blue",
      "size" : "bath",
      "price": 12.95
    }
  }
}

Example

JSON query

Result
Navigate objects to list the homewares object. $.products.homewares 
[  
   {  
      "item":"towel",
      "color":"blue",
      "size":"bath",
      "price":12.95
   }
]
Navigate arrays to list the second item in the food list. $.products.food[1] 
[  
   {  
      "category":"deli",
      "name":"fish",
      "type":"salmon",
      "price":7.99
   }
]
Use a wildcard to list all of the colors in the product list. $.products.*.color 
[  
   "blue"
]
Use a recursive descent to find all of the prices in the data store. $..price 
[  
   2.95,
   7.99,
   12.95
]

Note: Click Check Expression to quickly validate your JSONPath expressions in Nintex Platform API. This reduces the need to execute the workflow to validate expressions. See Test your JSON expression.

For more information on writing JSON queries, see http://goessner.net/articles/JsonPath/

JSON query examples

JSON data: 


{ 
  "products": {
    "food": [ 
      { 
        "category": "fruit",
        "name": "apple",
        "type": "Red Delicious",
        "price": 2.95
      },
      { 
        "category": "deli",
        "name": "fish",
        "type": "salmon",
        "price": 7.99
      }
    ],
    "homewares": {
      "item" : "towel",
      "color": "blue",
      "size" : "bath",
      "price": 12.95
    }
  }
}

JSON query:

Example

JSON query

Result
Navigate objects to list the homewares object. $.products.homewares 
[  
   {  
      "item":"towel",
      "color":"blue",
      "size":"bath",
      "price":12.95
   }
]
Navigate arrays to list the second item in the food list. $.products.food[1] 
[  
   {  
      "category":"deli",
      "name":"fish",
      "type":"salmon",
      "price":7.99
   }
]
Use a wildcard to list all of the colors in the product list. $.products.*.color 
[  
   "blue"
]
Use a recursive descent to find all of the prices in the data store. $..price 
[  
   2.95,
   7.99,
   12.95
]

JSON data: 


{ 
  "userName": "Ford",
  "dateSubmitted": "2020-07-23T01:23:45Z",
    "issues": [ 
      { 
        "subject": "Please help!",  				
        "dateCreated": "2020-07-23T01:23:45Z",
        "severity": "high",
        "id": "01928-22828-10294"
      }
    ]
}

JSON query:

Example

JSON query

Result
Get the ID value from the JSON $.issues[0].id 
[  
   "01928-22828-10294"
]

Test your JSON expression

There is no standard implementation of JSON paths. To avoid having to re-run your workflow multiple times to test your JSON expression, you can use the expression checking tool provided by the Nintex Platform tools.

To check your expression:

  1. Navigate to https://jsontostring.com/.
  2. Paste your sample JSON in the converter and click Convert.
  3. In another tab, navigate to Nintex Platform tools
  4. In the query body template on the right-hand side of the page:
    1. Replace the "string" value next to "JSON" with the JSON string you converted in step 2.
    2. Replace the "string" value next to "JSONpath" with the JSON expression you want to test.
  5. Click Send API Request.
  6. Check the returned response.

Configure the Query JSON action

In the Designer page:

  1. Click the Integration action group in the action toolbox.

    or

    Find actions by browsing action groups or by typing an action name or function in the Search field at the top of the action toolbox.

  2. Drag the Query JSON action into the workflow.
    The Query JSON action should occur after the logic that stores the JSON data in a variable.
  3. Click the Query JSON action.

  4. For JSON source, select the variable containing the JSON data.

  5. For JSONPath expression, type or paste the JSON query.

    Note: Click Check Expression to quickly validate your JSONPath expressions in Nintex Platform API. This reduces the need to execute the workflow to validate expressions.

  6. For Store first result, select a variable to store the first extracted value.

  7. For Store all results, select a collection variable to store a list of all extracted values.

For more information on the fields and buttons, see Query JSON fields.

Field Description

Variable types
Check Expression

Click to go to Nintex Platform API where you can directly check and validate your JSONPath expressions.

The authorization token is automatically generated for you when Nintex API developer platform is launched.

n/a
JSON Source The JSON data source you want to extract elements from.

Text, Decimal, Integer, Boolean, DateTime, Collection
JSONPath expression The JSON query to use.

(n/a)
Store first result The variable in which to store the first piece of data extracted.

Text, Decimal, Integer, Boolean, DateTime
Store all results The collection variable in which to store all extracted pieces of data.

Collection