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 begin:

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/

Jump to:

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

  1. Add the action to the workflow and open the action configuration panel. For more information, see Add, rename, and copy actions.

    The Query JSON action should occur after the logic that stores the JSON data in a variable.

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

  3. 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.

Query JSON fields and buttons

Field Description

Variable types
Check Expression

Opens the 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.

Text, Decimal, Integer, Boolean, DateTime
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